Home » SQL & PL/SQL » SQL & PL/SQL » how to fetch first record of each group (Oracle 10g)
how to fetch first record of each group [message #312279] Tue, 08 April 2008 07:06 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
The following SQL returns multiple rows for the same vote_id, with the values for other columns same or different. But I need to select the first record for each vote_id, irrespective of other values.

How to write such a query, and will rownum or rowid solve the purpose...?

I tried the following but it returns the same results as returned by above query


[Edit MC: Code removed after OP asked for it due to confidentiality issue]

[Updated on: Tue, 08 April 2008 10:47] by Moderator

Report message to a moderator

Re: how to fetch first record of each group [message #312284 is a reply to message #312279] Tue, 08 April 2008 07:31 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could use the ROW_NUMBER() analytic function, or the KEEP extension to MAX() or MIN()

SELECT *
FROM (
    SELECT a.*, row_number() over (partition by vote_id) AS rn
    FROM (
       ...
    ) a
)
WHERE rn = 1

or
SELECT vote_id
,      MAX(ve_unifcn_status_id) KEEP (DENSE_RANK FIRST ORDER BY ROWNUM) AS ve_unifcn_status_id
,      MAX(....
FROM (
   ....
)
GROUP BY vote_id


Ross Leishman
Re: how to fetch first record of each group [message #312341 is a reply to message #312279] Tue, 08 April 2008 10:48 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
After OP asked for this I removed the code and lock the topic.

Regards
Michel
Previous Topic: create user
Next Topic: MAtrix Query ( Complex )
Goto Forum:
  


Current Time: Wed Dec 07 02:44:10 CST 2016

Total time taken to generate the page: 0.47654 seconds