String concatenation in MSSQL 2008,

Question

I am trying to determine the best approach here in MSSQL 2008.

Here is my sample data

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 

If I group by Id and sort by TransDate, I want the last Non Null value for the active column, and MAX TransDate

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

Here are the results:

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

This will be similar to Coalesce, but on top of rows instead of two values ​​/ columns.

There would be many other columns that would also use this similar method, so I really don't want to make a separate join for each of the columns.

Any ideas?

Answer 1

I would probably use a correlated subquery.

 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 

Way without

 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 

Or after comments will cross apply work better for you?

 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 

Answer 2

This example should help using the analytic functions Max () OVER and 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 

Another option, quite expensive, would do this through XML. For educational purposes only

 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) 

It works on the principle that the generated XML has each record as a child node identifier. Zero columns are omitted, so the first column found using xpath (child / columnname) is the first non-zero value, similar to COALESCE.

Answer 3

You can use a subquery:

 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 

Answer 4

I created a temporary table called #temp to test my solution, and here is what I came up with:

 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 

This request ...

 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 

Returns these results.

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

Answer 5

Assuming a table named "test1", how about using ROW_NUMBER, OVER, and 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