First, Last row and other data from those rows

Question

Post reply

First, Last row and other data from those rows

phoenix_

SSChasing Mays

Points: 614
More actions
July 23, 2013 at 1:39 am

#296663

Hi,
I have the following data in tables
T1:
ID Dept Cat
1 WER 2
2 TTR 7
T2:
ID Date Type
1 2013-07-01 GA
2 2013-07-04 FS
2 2013-07-08 TR
2 2013-07-01 TT
1 2013-04-05 RT
1 2013-05-13 GG
2 2013-04-18 TT
I would like to query first table and then join to the second to have first and last transaction and some other data from the first and last transaction row. I would like to have the output similar to the following:
ID Cat FirstDate FirstType LastDate LastType
1 2 2013-04-05 RT 2013-07-01 GA
2 7 2013-04-18 TT 2013-07-08 TR
I was trying to use MIN MAX to query the First and Last Date (T2.Date) but then I can't to extract the related First and Last Type (T2.Type)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

DECLARE @T1 TABLE(ID INT, Dept CHAR(3), Cat INT)

INSERT INTO @T1(ID,Dept,Cat)

VALUES

(1, 'WER', 2),

(2, 'TTR', 7);

DECLARE @T2 TABLE(ID INT, [Date] DATE, Type CHAR(2))

INSERT INTO @T2(ID, [Date], Type)

VALUES

(1, '2013-07-01', 'GA'),

(2, '2013-07-04', 'FS'),

(2, '2013-07-08', 'TR'),

(2, '2013-07-01', 'TT'),

(1, '2013-04-05', 'RT'),

(1, '2013-05-13', 'GG'),

(2, '2013-04-18', 'TT');

WITH CTE AS (

SELECT t1.ID,

t1.Cat,

t2.[Date],

t2.Type,

ROW_NUMBER() OVER(PARTITION BY t1.ID ORDER BY t2.[Date]) AS rn,

ROW_NUMBER() OVER(PARTITION BY t1.ID ORDER BY t2.[Date] DESC) AS rnRev

FROM @T1 t1

INNER JOIN @T2 t2 ON t2.ID = t1.ID)

SELECT ID,

Cat,

MIN([Date]) AS FirstDate,

MAX(CASE WHEN rn=1 THEN Type END) AS FirstType,

MAX([Date]) AS LastDate,

MAX(CASE WHEN rnRev=1 THEN Type END) AS LastType

FROM CTE

GROUP BY ID, Cat;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Dwain Camps SSC Guru Points: 86948 More actions · Answer 2

Just for a little variety, I think this is another way:

SELECT a.ID, Cat -- , [Date], [Type]

,[FirstDate]=MIN([Date])

,[FirstType]=MAX(CASE WHEN a=[Date] THEN [Type] END)

,[LastDate]=MAX([Date])

,[LastType]=MAX(CASE WHEN b=[Date] THEN [Type] END)

FROM (

SELECT ID, [Date], [Type]

,a=MIN([Date]) OVER (PARTITION BY ID)

,b=MAX([Date]) OVER (PARTITION BY ID)

FROM @T2) a

INNER JOIN @t1 b ON a.ID = b.ID

WHERE a=[Date] OR b=[Date]

GROUP BY a.ID, b.Cat;

Thanks to Mark for the DDL and sample data!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St