Coalescence sur des lignes dans MSSQL 2008,

J'essaie de déterminer la meilleure approche ici dans MSSQL 2008.

Voici mes exemples de données

TransDate Id Active ------------------------- 1/18 1pm 5 1 1/18 2pm 5 0 1/18 3pm 5 Null 1/18 4pm 5 1 1/18 5pm 5 0 1/18 6pm 5 Null 

Si groupé par Id et ordonné par le TransDate, je veux la dernière valeur non nulle pour la colonne active, et le MAX de TransDate

 SELECT MAX(TransDate) AS TransDate, Id, --LASTNonNull(Active) AS Active 

Voici les résultats:

 TransDate Id Active --------------------- 1/18 6pm 5 0 

Ce serait comme un Coalesce mais sur les lignes, au lieu de deux valeurs / colonnes.

Il y aurait beaucoup d'autres colonnes qui auraient également cette méthode similaire appliquée, donc je ne veux vraiment pas faire une jointure séparée pour chacune des colonnes.

Des idées?

J'utiliserais probablement une sous-requête corrélée.

 SELECT MAX(TransDate) AS TransDate, Id, (SELECT TOP (1) Active FROM T t2 WHERE t2.Id = t1.Id AND Active IS NOT NULL ORDER BY TransDate DESC) AS Active FROM T t1 GROUP BY Id 

Un moyen sans

 SELECT Id, MAX(TransDate) AS TransDate, CAST(RIGHT(MAX(CONVERT(CHAR(23),TransDate,121) + CAST(Active AS CHAR(1))),1) AS BIT) AS Active, /*You can probably figure out a more efficient thing to compare than the above depending on your data. eg*/ CAST(MAX(DATEDIFF(SECOND,'19500101',TransDate) * CAST(10 AS BIGINT) + Active)%10 AS BIT) AS Active2 FROM T GROUP BY Id 

Ou en suivant les commentaires serait plus cross apply travailler mieux pour vous?

 WITH T (TransDate, Id, Active, SomeOtherColumn) AS ( select GETDATE(), 5, 1, 'A' UNION ALL select 1+GETDATE(), 5, 0, 'B' UNION ALL select 2+GETDATE(), 5, null, 'C' UNION ALL select 3+GETDATE(), 5, 1, 'D' UNION ALL select 4+GETDATE(), 5, 0, 'E' UNION ALL select 5+GETDATE(), 5, null,'F' ), T1 AS ( SELECT MAX(TransDate) AS TransDate, Id FROM T GROUP BY Id ) SELECT T1.TransDate, Id, CA.Active AS Active, CA.SomeOtherColumn AS SomeOtherColumn FROM T1 CROSS APPLY (SELECT TOP (1) Active, SomeOtherColumn FROM T t2 WHERE t2.Id = T1.Id AND Active IS NOT NULL ORDER BY TransDate DESC) CA 

Cet exemple devrait aider, en utilisant les fonctions analytiques Max () OVER et Row_Number () OVER

 create table tww( transdate datetime, id int, active bit) insert tww select GETDATE(), 5, 1 insert tww select 1+GETDATE(), 5, 0 insert tww select 2+GETDATE(), 5, null insert tww select 3+GETDATE(), 5, 1 insert tww select 4+GETDATE(), 5, 0 insert tww select 5+GETDATE(), 5, null select maxDate as Transdate, id, Active from ( select *, max(transdate) over (partition by id) maxDate, ROW_NUMBER() over (partition by id order by case when active is not null then 0 else 1 end, transdate desc) rn from tww ) x where rn=1 

Une autre option, assez coûteuse, serait de le faire via XML. À des fins éducatives seulement

 select ID = ncvalue('@id', 'int'), trandate = ncvalue('(data/transdate)[1]', 'datetime'), active = ncvalue('(data/active)[1]', 'bit') from (select xml=convert(xml, (select id [@id], ( select * from tww t where t.id=tww.id order by transdate desc for xml path('data'), type) from tww group by id for xml path('node'), root('root'), elements) )) x cross apply xml.nodes('root/node') n(c) 

Cela fonctionne sur le principe que le XML généré a chaque logging comme un nœud enfant de l'ID. Les colonnes nulles ont été omises, donc la première colonne trouvée en utilisant xpath (nom enfant / colonne) est la première valeur non nulle similaire à COALESCE.

Vous pouvez utiliser une sous-requête:

 SELECT MAX(TransDate) AS TransDate , Id , ( SELECT TOP 1 t2.Active FROM YourTable t2 WHERE t1.id = t2.id and t2.Active is not null ORDER BY t2.TransDate desc ) FROM YourTable t1 

J'ai créé une table temporaire nommée #temp pour tester ma solution, et voici ce que j'ai trouvé:

 transdate id active 1/1/2011 12:00:00 AM 5 1 1/2/2011 12:00:00 AM 5 0 1/3/2011 12:00:00 AM 5 null 1/4/2011 12:00:00 AM 5 1 1/5/2011 12:00:00 AM 5 0 1/6/2011 12:00:00 AM 5 null 1/1/2011 12:00:00 AM 6 2 1/2/2011 12:00:00 AM 6 3 1/3/2011 12:00:00 AM 6 null 1/4/2011 12:00:00 AM 6 2 1/5/2011 12:00:00 AM 6 null 

Cette requête …

 select max(a.transdate) as transdate, a.id, ( select top (1) b.active from #temp b where b.active is not null and b.id = a.id order by b.transdate desc ) as active from #temp a group by a.id 

Renvoie ces résultats

 transdate id active 1/6/2011 12:00:00 AM 5 0 1/5/2011 12:00:00 AM 6 2 

En supposant une table nommée "test1", pourquoi ne pas utiliser ROW_NUMBER, OVER et PARTITION BY?

 SELECT transdate, id, active FROM (SELECT transdate, ROW_NUMBER() OVER(PARTITION BY id ORDER BY transdate desc) AS rownumber, id, active FROM test1 WHERE active is not null) a WHERE a.rownumber = 1