CODEWITHSUNDEEP
sqlsql-server-2008
Loofer
Loofer

Reputation: 6973

Returning the top values for each column of an SQL query

I have a query that returns some data

Name Hour1 hour2 hour3 etc
A    1     2      3    ...  
B    50    2      25   ... 
C    25    2      3    ... 
D    1     30     50   ... 
E    1     40     3    ...

I would like to just have the values for the top two results in each column (values are made up)

Name Hour1 hour2 hour3 etc
A    null  null  null  ...  
B    50    null  25    ... 
C    25    null  null  ... 
D    null  30    50   ... 
E    null  40    null ...

There can be lots of names, and 24 hour columns. Is this possible?

The existing query is in the form of

SELECT Name,
    ISNULL(Count(CASE WHEN DATEPART(HOUR,OpenedDate)=1 THEN 1 ELSE null END), 0) AS Hour1,
etc
etc
From DataTable

Upvotes: 0

Views: 48

Answers (2)

jpw
jpw

Reputation: 44911

One option might be to use a common table expression together with a pivot.

with cte as (
    select 
       name
       , h = datepart(hour,openeddate) 
       , c = count(*) 
       , r = rank() over (partition by datepart(hour,openeddate) order by count(*) desc) 
    from datatable 
    group by name, datepart(hour,openeddate)
) 

select 
    name
    , hour1 = max(case when r <= 2 then [1] else null end)
    , hour2 = max(case when r <= 2 then [2] else null end)
    , hour3 = max(case when r <= 2 then [3] else null end)
from cte
pivot ( sum(c) for h in ([1],[2],[3]) )p
group by name
order by name

Note that the rank function will return more than two rows if there are ties.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I would write your query as:

SELECT Name,
       SUM(CASE WHEN DATEPART(HOUR, OpenedDate) = 1 THEN 1 ELSE 0 END) AS Hour1,
       . . .
FROM DataTable
GROUP BY Name;

To get the top 2 values, you can enumerate DataTable:

select name,
       max(case when hr = 1 and seqnum <= 2 then cnt end) as hr_1,
       . . .
from (select t.*, DATEPART(HOUR, OpenedDate) as hr, count(*) as cnt,
             row_number() over (partition by datepart(hour, OpenedDate)
                                order by count(*) desc) as seqnum
      from datatable t
      group by name, DATEPART(HOUR, OpenedDate)
     ) t
group by name;

The subquery summarizes the data by name and hour. It then enumerates the values and uses that for conditional aggregation.

You might want dense_rank() rather than row_number() depending on how you want to handle ties.

Upvotes: 1

Related Questions