First, Last row and other data from those rows

  • 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)

  • 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
  • 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

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

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