CODEWITHSUNDEEP
sql
Enrique Ortiz Casillas
Enrique Ortiz Casillas

Reputation: 577

Selecting only the max value by a filter in SQL

Suppose I have a table with only 3 columns: name, total, and year. total means the total number of people who had that name in a given year. It looks like this, but with many more entries:

name     total    year 

Mary     100       1955
Jenny     20       1955
Susy      50       1956
Sarah     33       1956

I want a table that has 3 columns too (name, total, and year) that only shows the most popular name per year for a list of years (for example, 1885, 1915, 1945, 1975 and 2005).

I tried this: 

CREATE OR REPLACE temporary view HistoricNames as 
SELECT firstName, year, max(total) as total
FROM SSANames
WHERE year = 1885 or year = 1915 OR year = 1945 or year = 1975 or year = 2005
group by firstName, year
ORDER by total

But I'm getting a lot of entries for each year, not the most popular one.

Upvotes: 0

Views: 915

Answers (3)

Menno
Menno

Reputation: 12651

Credits to the users that already answered the question. You could rewrite those queries to make your subquery obsolete:

SELECT TOP 1 WITH TIES firstName, year, total
FROM SSANames
WHERE year IN (1885, 1915, 1945, 1975, 2005)
ORDER BY ROW_NUMBER() OVER (PARTITION BY year ORDER BY total DESC)

The trick here is that the ROW_NUMBER() works just the same as in the other answers. By using ORDER BY you are effectively placing all those rows marked with a one (one row per year) first. Followed by TOP 1 you only retreive the first value you have ordered by, again the one. WITH TIES finally retreives not only the first one, but all ones.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48865

You don't mention which database you are using, but I'll assume it's a modern one with window functions. You can do:

select * 
from (
  select *,
    row_number() over(partition by year order by total desc) as rn
  from ssanames
  where year in (1885, 1915, 1945, 1975,2005)
) x
where rn = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can use row_number():

SELECT firstName, year, total
FROM (SELECT firstName, year, total,
             ROW_NUMBER() OVER (PARTITION BY year ORDER BY total DESC) as seqnum
      FROM SSANames
      WHERE year IN (1885, 1915, 1945, 1975, 2005)
     ) s
WHERE seqnum = 1
ORDER by total ;

Upvotes: 1

Related Questions