Home » SQL & PL/SQL » SQL & PL/SQL » Top records based on a column (Oracle 10g)
Top records based on a column [message #391465] Thu, 12 March 2009 05:55 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to find top 2 rows from a table under each ingredients(ing).

I am pasting the test table creation script and insert statement.

create table toptest(
ing varchar2(50),
qty number(5),
transdate date
)
insert into toptest values('i1',100,sysdate)
insert into toptest values('i1',200,sysdate-1)
insert into toptest values('i1',300,sysdate-2)
insert into toptest values('i1',400,sysdate-3)

insert into toptest values('i2',100,sysdate)
insert into toptest values('i2',200,sysdate-1)
insert into toptest values('i2',300,sysdate-2)
insert into toptest values('i2',400,sysdate-3)

insert into toptest values('i3',100,sysdate);
insert into toptest values('i3',200,sysdate-1);
insert into toptest values('i3',300,sysdate-2);
insert into toptest values('i3',400,sysdate-3);

how to select top 2 rows for each of Ingredient code present in the table order by date asc?

The result should come as:-

Ing Qty
i1 100
i1 200
i2 100
i2 200
i3 100
i3 200

Please help me on this as I am only able to get the top 2 row and not based on ing.

Thanks for looking into this,
Mahi

[Updated on: Thu, 12 March 2009 06:01]

Report message to a moderator

Re: Top records based on a column [message #391467 is a reply to message #391465] Thu, 12 March 2009 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: Top records based on a column [message #391468 is a reply to message #391465] Thu, 12 March 2009 06:03 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@virmahi,

Use ROW_NUMBER or RANK or DENSE_RANK. A Few good illustrations have been given at Analytical Functions By Example

Hope this helps.

Regards,
Jo
Re: Top records based on a column [message #391475 is a reply to message #391468] Thu, 12 March 2009 06:21 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks for your suggestions.
I tried using the below script :-
select ing, qty, transdate from(
select ing, qty, transdate,
ROW_NUMBER( ) OVER (PARTITION BY
ing order by transdate ASC
NULLS LAST) SRLNO from toptest
order by transdate, SRLNO) where srlno <= 2


Please advice if its correct as I am getting the correct result.

Regards,
Mahi
Re: Top records based on a column [message #391483 is a reply to message #391475] Thu, 12 March 2009 06:31 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@virmahi,

You are kindda funny...
virmahi wrote on Thu, 12 March 2009 16:51

./fa/5767/0/ Please advice if its correct as I am getting the correct result.



Regards,
Jo
Re: Top records based on a column [message #391496 is a reply to message #391475] Thu, 12 March 2009 06:45 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your query to make it more readable, for instance use SQL Formatter.

Regards
Michel
Previous Topic: Replacing a unique index with primary key (merged 3)
Next Topic: dbms_job issue
Goto Forum:
  


Current Time: Sun Dec 04 16:31:32 CST 2016

Total time taken to generate the page: 0.06628 seconds