CODEWITHSUNDEEP
sqloracle-database
dang
dang

Reputation: 2412

Get first N rows from table based on condition - Oracle SQL

I have a table:

table1

Type            Attribute           Value       Count
Fruit           Apple               Sweet       1772
Fruit           Apple               Sour        1021
Fruit           Apple               Sweetest    930
Fruit           Apple               Sweetest    930
Fruit           Orange              Sweetest    200
Fruit           Orange              Sour        190
Fruit           Orange              Sweetest    160
Fruit           Orange              Sweetest    140

I need the first 3 rows based on type and attribute and count.

So, the output should be:

Type            Attribute           Value       Count
Fruit           Apple               Sweet       1772
Fruit           Apple               Sour        1021
Fruit           Apple               Sweetest    930
Fruit           Orange              Sweetest    200
Fruit           Orange              Sour        190
Fruit           Orange              Sweetest    160

How can I grab the first 3 rows for each type, attribute, count?

Upvotes: 1

Views: 74

Answers (3)

Mark Stewart
Mark Stewart

Reputation: 2098

The other answers by @GordonLinoff and @LukaszSzozda were based on the original post, and not on the clarification added later by the OP. The SQL Fiddle based on original post, using SQL in answer by @Gordon (which is basically identical to answer by @Lukasz as they posted the answers about the same time, before the clarification) returns 4 rows per Apple and 4 rows per Orange:

FOOD_TYPE  ATTRIBUTE  VALUE     CNT   SEQNUM
Fruit      Apple      Sour      1021  1
Fruit      Apple      Sweet     1772  1
Fruit      Apple      Sweetest   930  1
Fruit      Apple      Sweetest   930  2
Fruit      Orange     Sour       190  1
Fruit      Orange     Sweetest   200  1
Fruit      Orange     Sweetest   160  2
Fruit      Orange     Sweetest   140  3

Modified SQL here

select t.*
from (select Food.*,
             row_number() over (partition by food_type, attribute order by cnt desc) as seqnum
      from Food
     ) t
where seqnum <= 3;

returns the desired result:

FOOD_TYPE  ATTRIBUTE  VALUE     CNT    SEQNUM
Fruit      Apple      Sweet     1772   1
Fruit      Apple      Sour      1021   2
Fruit      Apple      Sweetest  930    3
Fruit      Orange     Sweetest  200    1
Fruit      Orange     Sour      190    2
Fruit      Orange     Sweetest  160    3

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

You could use ROW_NUMBER:

WITH cte AS (
  SELECT t.*, ROW_NUMBER(PARTIION BY "Type", Attribute ORDER BY "count" DESC) AS rn
  FROM tab t
)
SELECT *
FROM cte
WHERE rn <= 3;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Use row_number():

select t.*
from (select t.*,
             row_number() over (partition by type, attribute, value order by count desc) as seqnum
      from t
     ) t
where seqnum <= 3;

Upvotes: 0

Related Questions