CODEWITHSUNDEEP
sqlsql-servert-sql
Grambot
Grambot

Reputation: 4524

Selecting Top Occurrence of Record by Date

Table Structure:

ScheduledDate Date
Commodity varchar

Example Data

ScheduledDate | Commodity
--------------+------------
 2017-11-27   | KIDNEY BEANS
 2017-11-27   | KIDNEY BEANS
 2017-11-27   | RED BEANS
 2017-11-28   | LARGE GREEN LENTIL
 2017-11-28   | SMALL GREEN LENTIL
 2017-11-29   | LARGE GREEN LENTIL

What I need: One record per day of the top occurring commodity in the table, sorted by date. So the desired output would be:

ScheduledDate | Commodity
--------------+------------
 2017-11-27   | KIDNEY BEANS
 2017-11-28   | LARGE GREEN LENTIL
 2017-11-29   | LARGE GREEN LENTIL

...if multiple commodities appear with the same number of occurances on a date then I only need one returned. No real preference on which.

I figure I'm really close but just need the last piece of the puzzle to solve this...

SELECT
     ScheduledDate
    ,Commodity
    ,OCCURANCE_COUNT = MAX(OCCURANCES)
FROM (
    SELECT 
        ScheduledDate
        ,Commodity
        ,OCCURANCES = COUNT(Commodity)
    FROM 
        vwScheduledLoads 
    GROUP BY
        ScheduledDate,
        Commodity
) qc
GROUP BY
    ScheduledDate
    ,Commodity
ORDER BY
    ScheduledDate

...The inner SQL does what I want it to, the part where I want to group the results is what I'm stumped on. That query spits out the following:

ScheduledDate | Commodity          | OCCURANCE_COUNT
--------------+--------------------+-----------------
 2017-11-27   | KIDNEY BEANS       | 2
 2017-11-27   | RED BEANS          | 1
 2017-11-28   | LARGE GREEN LENTIL | 1
 2017-11-28   | SMALL GREEN LENTIL | 1
 2017-11-29   | LARGE GREEN LENTIL | 1

Upvotes: 2

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You want an aggregation query with row_number():

select ScheduledDate, Commodity
from (select ScheduledDate, Commodity, count(*) as cnt,
             row_number() over (partition by ScheduledDate order by count(*) desc) as seqnum
      from t
      group by ScheduledDate, Commodity
     ) sc
where seqnum = 1;

Upvotes: 5

Related Questions