Home » SQL & PL/SQL » SQL & PL/SQL » Get first row with aggregate function?
Get first row with aggregate function? [message #223099] Wed, 07 March 2007 09:39 Go to next message
Messages: 9
Registered: March 2006
Junior Member
There are some cases in which I need to get one record instead of all available records using an aggregate function.

For my case it would be sufficient to just retrieve the first row. Is there such an aggregate function which is probably more efficient than MAX(...) or MIN(...)?

Thanks in advance!
Re: Get first row with aggregate function? [message #223102 is a reply to message #223099] Wed, 07 March 2007 09:43 Go to previous messageGo to next message
Messages: 24958
Registered: January 2009
Location: SoCal
Senior Member

[Updated on: Wed, 07 March 2007 09:45] by Moderator

Report message to a moderator

Re: Get first row with aggregate function? [message #223212 is a reply to message #223102] Thu, 08 March 2007 00:41 Go to previous message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
To return the first row encountered per group, you can use the KEEP (DENSE_RANK FIRST ORDER BY ...) extension to the aggregate functions (note that this is NOT an analytic function - it works with a GROUP BY). This is actually less efficient than a MAX or MIN because it requires a finer degree of sorting. If you just want some random value from each group, stick with MAX() and MIN()

Ross Leishman
Previous Topic: Truncate table issue
Next Topic: Multiple rows into Single row on Oracle 8i
Goto Forum:

Current Time: Sat Oct 22 15:15:00 CDT 2016

Total time taken to generate the page: 0.11128 seconds