#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0

    Query to get rows of latest date from each group


    Hi

    I want to get rows by latest date for each group pf rows.

    say my data is :

    TYPE1 TYPE2 quantity DATE-ORDERED
    sweets chocolate 10 05-FEB-2012
    sweets chocolate 10 04-DEC-2012
    sweets chocolate 10 08-FEB-2012
    pastries chocolate 20 08-AUG-2012
    pastries chocolate 20 07-JUN-2012
    pastries chocolate 20 08-JUL-2012
    pastries chocolate 20 01-AUG-2012
    sweets vanilla 10 06-SEP-2012
    sweets vanilla 10 07-MAR-2012
    sweets vanilla 10 08-AUG-2012
    sweets vanilla 10 05-JAN-2012
    sweets vanilla 10 05-DEC-2012
    pastries vanilla 20 05-MAY-2012
    pastries vanilla 20 05-SEP-2012
    pastries vanilla 20 05-NOV-2012
    pastries vanilla 20 05-FEB-2012


    I want to get results by latest date,
    sweets chocolate 10 04-DEC-2012
    pastries chocolate 20 08-AUG-2012
    sweets vanilla 10 05-DEC-2012
    pastries vanilla 20 05-NOV-2012


    I have tried Queries with Max(DATE-ORDERED) and grouping it..its not showing me results because I don't have indexes in my data.
    Any Help would be appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    348
    Code:
    select type1, 
           type2, 
           quantity, 
           date_ordered,
    from (
       select type1, 
              type2, 
              quantity, 
              date_ordered,
              row_number() over (partition by type1, type2 order by date_ordered desc) as rn
       from the_unknown_table
    ) t
    where rn = 1
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0
    Awesome, Thanks !!
    It worked.

IMN logo majestic logo threadwatch logo seochat tools logo