Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Unanswered: Need help with grouping and displaying last row in the first group

    Hi

    I have a table wherein I have to report the the present status and the date from which this status is applicable.
    Example:

    Status date
    1 26-July
    1 24-July
    1 22-July
    2 21-July
    2 19-July
    1 16-July
    0 14-July

    Given this, i want to display the current status as 1 and date as 22 July. I am not sure how to go about this. Any help will be greatly appreciated.

    Thanks,
    Sindhu

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Given this, i want to display the current status as 1 and date as 22 July
    why return just this row & none of the others?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2012
    Posts
    6
    That is the requirement. I have to display the present status which is 1 and the date from which it is in effect(which is 22 July)

    Thanks,
    Sindhu

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT * FROM TABLE1 WHERE DATE = '22-July';
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2012
    Posts
    6
    No.. that was just an example. Basically, I have to report the current status and the date from which it is applicable.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Need help with grouping and displaying last row in the first group

    so what exactly is being "grouped" & why?
    what determines "first"?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    for status 1 you have an earlier date if 16-July. Why wouldn't that be when the status was started?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34
    Select status, min(date)
    from table1
    group by status;
    Last edited by jignesh_foryou; 07-28-12 at 06:41.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If nesting of analytical functions was possible.
    Code:
    SELECT status
         , max_date AS date
     FROM (SELECT status , date
                , ROW_NUMBER()
                     OVER( ORDER BY date DESC ) AS row_num
                , MAX(
                     CASE
                     WHEN status
                       <> LAG(status)
                             OVER( ORDER BY date ASC ) THEN
                          date
                     END
                  ) OVER( ORDER BY date ASC ) AS max_date
            FROM  sample_data
          )
     WHERE row_num = 1
    ;

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If only status and date were interesting,
    this also might work...
    Code:
    SELECT status
         , date
     FROM (SELECT status , date
                , MAX(
                     CASE
                     WHEN status
                       <> LAG(status)
                             OVER( ORDER BY date ASC ) THEN
                          date
                     END
                  ) OVER( ORDER BY date ASC
                          ROWS BETWEEN CURRENT ROW
                                   AND UNBOUNDED FOLLOWING
                        ) AS max_date
            FROM  sample_data
          )
     WHERE date = max_date
    ;

Tags for this Thread

Posting Permissions

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