Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Unanswered: First & Last records in a group

    Hi there, I have a group of records like this;

    NAME| RUN START| RUN END| LK1| LK2| DESC
    Graeme Brown| 2012-02-23 07:20:00| 2012-02-23 07:50:00| 2213| 2244| AK1/4/PI2
    Graeme Brown| 2012-02-23 08:00:00| 2012-02-23 09:25:00| 2244| 2052| AK1/4/PI2
    Graeme Brown| 2012-02-23 09:30:00| 2012-02-23 11:05:00| 2052| 917| AK1/4/PI2
    Graeme Brown| 2012-02-23 12:15:00| 2012-02-23 13:55:00| 917| 2052| AK1/4/PI2
    Graeme Brown| 2012-02-23 14:05:00| 2012-02-23 15:40:00| 2052| 1111| AK1/4/PI2

    They are grouped on the last column [DESC].

    I want to get;

    NAME| RUN START| RUN END| LK1| LK2| DESC
    Graeme Brown| 2012-02-23 07:20:00| 2012-02-23 15:40:00| 2213| 1111| AK1/4/PI2

    So what it needs to do is combine the ;

    earliest RUN START and corresponding LK1 with
    latest RUN END and corresponding LK2.

    Can anyone please help? I've tried creating temp tables with the mins and maxs - but then I can't combine them with the LK1 and Lk2 fields...
    I'm a bit lost...

    Thank you in advance!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    WITH CTE_MIN AS
    (SELECT NAME, RUN_START, LK1, DESCR, 
    	ROW_NUMBER() OVER (PARTITION BY NAME, DESCR ORDER BY RUN_START ASC) AS RowNum
    FROM #DaTable
    ),
    CTE_MAX AS
    (SELECT NAME, RUN_END, LK2, DESCR, 
    	ROW_NUMBER() OVER (PARTITION BY NAME, DESCR ORDER BY RUN_START DESC) as RowNum
    FROM #DaTable
    )
    SELECT CTE_MIN.NAME, CTE_MIN****N_START, CTE_MAX****N_END, CTE_MIN.LK1, CTE_MAX.LK2, CTE_MIN.DESCR
    FROM CTE_MIN
    	INNER JOIN CTE_MAX ON
    		CTE_MIN.NAME = CTE_MAX.NAME AND
    		CTE_MIN.DESCR = CTE_MAX.DESCR
    WHERE CTE_MIN.RowNum = 1 AND
    	CTE_MAX.RowNum = 1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2012
    Posts
    84
    Hello Tagoso,

    try this query
    Code:
    select x.name,
           x.r_un_start,
           x.r_un_end,
           ( SELECT lk1 FROM mytable m
             WHERE m.`desc` = x.`desc` AND m.r_u_start = x.r_un_start
             LIMIT 1
           ) lk1,
           ( SELECT lk2 FROM mytable m
             WHERE m.`desc` = x.`desc` AND m.r_un_end = x.r_un_end
             LIMIT 1
           ) lk2,
           x.`desc`
    FROM (
      SELECT name, `desc`, min( run_start) run_start, max(run_end) run_end
      FROM mytable
      GROUP BY `desc`
    ) x;
    It is strange - this forum replaces .strings: . r u (dot+r+u) with ****,
    so in the query i used .r_un instead of . r u n

  4. #4
    Join Date
    Feb 2012
    Posts
    3

    First & Last records in a group

    Thank you very much for your help!

    I managed to get Wim's suggestion going (once I figured out what the ***'s were!).

    I haven't tried yours kordirko - as the first solution works. I guess I should try both just for my education.

    But thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •