Home » SQL & PL/SQL » SQL & PL/SQL » Limit number of rows (not what you think) (Oracle 10i)
icon5.gif  Limit number of rows (not what you think) [message #442821] Wed, 10 February 2010 17:15 Go to next message
huesland
Messages: 3
Registered: July 2009
Junior Member
Here is my problem. I have a query that currently returns all of our products by product family, SKU's and revenue. What I would like to do is return the top 3 sku's for each product family.

This is what I currently get:
PROD_FAM    SKU    REV
Prod 1      123    100
Prod 1      124    150
Prod 1      125    200
Prod 1      126    50
Prod 1      127    105
Prod 2      234    100
Prod 2      235    150
Prod 2      213    130
Prod 2      240    200
Prod 3 ...


This is what I would like to return:

PROD_FAM    SKU    REV
Prod 1      125    200
Prod 1      124    150
Prod 1      127    105
Prod 2      240    200
Prod 2      235    150
Prod 2      213    130
Prod 3 ...


If anyone knows of how I can accomplish this that would be great.

Thanks,
Chris
Re: Limit number of rows (not what you think) [message #442822 is a reply to message #442821] Wed, 10 February 2010 17:22 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
I am pretty sure Tom Kyte of asktomhome has the exact article you are looking for. Unfortunately I cannot find the link.

Maybe someone else here knows it?

Kevin
Re: Limit number of rows (not what you think) [message #442824 is a reply to message #442822] Wed, 10 February 2010 17:41 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
select *
 from ( select deptno, sal,
        dense_rank() over ( partition by deptno order by sal desc ) dense_rank,
        rank() over ( partition by deptno order by sal desc ) rank
          from emp )
where rank <= 3
order by deptno, sal desc



    DEPTNO        SAL DENSE_RANK       RANK
---------- ---------- ---------- ----------
        10       5000          1          1
        10       2450          2          2
        10       1300          3          3
        20       3000          1          1 <-- tie for high val
        20       3000          1          1 <-- tie for high val
        20       2975          2          3 <-- skip 2nd due to duplicate 1st's
        30       2850          1          1
        30       1600          2          2
        30       1500          3          3

9 rows selected.

Re: Limit number of rows (not what you think) [message #442852 is a reply to message #442821] Thu, 11 February 2010 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is EXACTLY what we thought.

Regards
Michel
Re: Limit number of rows (not what you think) [message #442931 is a reply to message #442852] Thu, 11 February 2010 07:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Thu, 11 February 2010 07:19
This is EXACTLY what we thought.

Lol, exactly what I was going to post Smile
Re: Limit number of rows (not what you think) [message #442935 is a reply to message #442821] Thu, 11 February 2010 08:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
OK I found it. It took some looking. It was an older Oracle Magazine article by Tom which I remember reading on one of my many trips to the men's room after a fine lunch of Tikka Masala, Vindaloo, and Pomppadoms.

Enjoy it... I did.

Kevin
Re: Limit number of rows (not what you think) [message #442963 is a reply to message #442821] Thu, 11 February 2010 12:40 Go to previous message
huesland
Messages: 3
Registered: July 2009
Junior Member
Thanks for the quick response it worked like a charm. I think I found a new oracle forum. Smile

[Updated on: Thu, 11 February 2010 12:48] by Moderator

Report message to a moderator

Previous Topic: Replace
Next Topic: DBMS_SCHEDULER.RUN_JOB -> ORA-02800: Requests timed out
Goto Forum:
  


Current Time: Sun Sep 25 04:24:21 CDT 2016

Total time taken to generate the page: 0.10173 seconds