CODEWITHSUNDEEP
sql-serveroracle-databaset-sql
theateist
theateist

Reputation: 14399

How to write query in ORACLE and SQL_SERVER to get records with the most statuses?

for the table below

tenant_id | status_id
----------------------
   3      |   6
---------------------
   5      |   7
---------------------
   7      |   7
--------------------
   3      |   7
-------------------
   3      |   7
--------------------
   5      |   7

we can see that:

For given STATUS_ID_PARAM I want to get all tenants that has the most records with that STATUS_ID_PARAM. For the example above, for STATUS_ID_PARAM = 7, the query should return 2 records:

   tenant_id | status_id
   --------------------
      3      |   7
   --------------------
      5      |   7

because those tenants has the most records(2 for each one) with status_id=7.

I tried something like this, but I don't know how to continue or may be there is another way:

select tenant_id, count(status_id) s
from candidate
where status_id = STATUS_ID_PARAM 
group by tenant_id, status_id

Upvotes: 2

Views: 500

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

This will work in both Oracle and SQL Server

select tenant_id, s
from
  (       
    select tenant_id,
           count(*) as s,
           rank() over(order by count(*) desc) as rn
    from candidate
    where status_id = 7
    group by tenant_id
  ) T
where rn = 1;

(Tested on SQL Server 2005->2012 and Oracle 11g R2)

Upvotes: 4

Turbot
Turbot

Reputation: 5227

I Edited the answer :)

select tenant_id, count(status_id) s
from candidate can
where status_id = 7
group by tenant_id, status_id
having COUNT(status_id) 
in 
(
select top 1 count(status_id) from candidate 
group by status_id,tenant_id
order by COUNT(status_id) desc
)

Upvotes: 0

Vikram
Vikram

Reputation: 8333

select tenant_id, count(status_id) s
from candidate
where status_id = STATUS_ID_PARAM 
group by tenant_id, status_id
having count(status_id) = (select max(count(status_id)) from candidate)

Upvotes: 0

Pete
Pete

Reputation: 635

Try This

select tenant_id, count(status_id) s
from candidate
where status_id = STATUS_ID_PARAM 
AND count(status_id) = MAX(count(status_id))
group by tenant_id, status_id

Upvotes: 0

Related Questions