Query to get rows of latest date from each group

Dev Shed Forums Sponsor:

August 9th, 2012, 11:28 AM

tweety03

Registered User

Join Date: Aug 2012

Posts: 2

Time spent in forums: 19 m 32 sec
Reputation 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.

August 9th, 2012, 11:48 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,684

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 42 m 28 sec
Reputation Power: 284

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

August 9th, 2012, 12:15 PM

tweety03

Registered User

Join Date: Aug 2012

Posts: 2

Time spent in forums: 19 m 32 sec
Reputation Power: 0

Awesome, Thanks !!
It worked.

Viewing: Dev Shed Forums > Databases > Oracle Development > Query to get rows of latest date from each group

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump