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: 25534
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: 3727
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: Mon Jul 24 18:15:59 CDT 2017

Total time taken to generate the page: 0.17730 seconds