CODEWITHSUNDEEP
sqloracle-databaseplsql
jovanMeshkov
jovanMeshkov

Reputation: 797

Get rows with max value having 3 columns

I have this PL/SQL query:

SELECT customer_id, table_id, count(table_id) as reserved
FROM { derived table }
GROUP BY customer_id,table_id
ORDER BY reserved DESC

I have this result:

https://i.sstatic.net/ATfUw.png

So now I want to get the first 2 rows (with max value according to reserved column), I tried this query:

SELECT customer_id,table_id,max(reserved) from
(
    SELECT customer_id, table_id, count(table_id) as reserved
    FROM { derived table }
    GROUP BY customer_id,table_id
    ORDER BY reserved DESC
)
GROUP BY customer_id, table_id

I recieved same result as above...

Note: Result was just example, maybe there will be 3, 1 or more rows with max value, next time

Upvotes: 1

Views: 1779

Answers (3)

Hart CO
Hart CO

Reputation: 34784

SELECT customer_id,table_id,reserved
FROM (SELECT customer_id,table_id, COUNT(*)as reserved, RANK() OVER (ORDER BY COUNT(*) DESC) AS ct_rank
      FROM { derived table }
      GROUP BY customer_id,table_id
      )sub
WHERE ct_rank = 1

Edit: Changed to use Rank

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270301

Your query is very close. The outer query should be selecting two rows, rather than re-doing the aggregation:

SELECT customer_id, table_id, reserved from
(
    SELECT customer_id, table_id, count(table_id) as reserved
    GROUP BY customer_id,table_id
    ORDER BY reserved DESC
)
where rownum <= 2;

Upvotes: 0

Omaer
Omaer

Reputation: 827

When you say you want the first 2 rows, I assume you don't mean you always want the first 2 rows - but rather you want the rows which have the maximum value of 'reserved'.

You could try:

    SELECT customer_id, table_id, count(table_id) as reserved
    FROM { derived table }
    GROUP BY customer_id, table_id
    HAVING count(table_id) = (
        SELECT top 1 count(table_id)
        FROM { derived table }
        GROUP BY customer_id, table_id
        ORDER BY reserved DESC
    )

I know that would work in T-SQL and I'm guessing it should in PL/SQL as well.

Upvotes: 0

Related Questions