Finding the rank of a value in a most common list

Question

I got a database table containing some names and some numbers looks like following:

first last    number
max   muster  1
max   juster  2
max   huster  3
jen   muster  4
jen   jenker  5
ian   hoster  6
...

I query the most common first name by:

SELECT first, COUNT(*) AS value FROM table
GROUP BY first
ORDER BY COUNT(*) DESC
LIMIT 3

I would like to know the ranking of first name 'ian' of this database table. In this case it is the 3rd common first name and above query gives me following:

    first value
1   max   3
2   jen   2
3   ian   1

What I would like is a code for following:

    first value
3   ian   1

or something similar so that I can reach the number "3" with giving the first='ian' because it is the 3rd common name in my table. How should I query it?

EXAMPLE:

SELECT first, COUNT(*) AS value FROM table
GROUP BY first
ORDER BY COUNT(*) DESC

# So far we ordered the list from most common to least common

FILTER WHERE first='ian'

# We filtered the other names so that only first='ian' stays in the query,
# and we did not lose the index value (in this case 3) of the 'ian'

ofc this won't work, however I think you understand what I am searching for.

This look like a duplicate of : https://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table — Malo, Nov 24 '20 at 23:14
I don't select/search the row like the question of https://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table I search for a name and want to learn the row of the name — oakca, Nov 25 '20 at 00:28

GMB · Accepted Answer · 2020-11-25T00:22:07.357

1

You can use window functions:

select *
from (
    select first_name, count(*) as cnt, rank() over(order by count(*) desc) as rn
    from mytable
    group by first_name
) t
where first_name = 'ian'

This gives you the the rank of "Ian".

edited Nov 25 '20 at 00:22

answered Nov 24 '20 at 16:03

GMB

216,147
25
84
135

above code with where first='ian' generates a table with only ian and ian rank would be 1 because it is the only entry in this table – oakca Nov 24 '20 at 16:07
@oakca: the query does what you ask for. The subquery ranks all records, *then* the outer query filters on the target `name`. Please try the query. – GMB Nov 24 '20 at 23:37
You are right. I am sorry, I did not copy pasted the code you wrote, and therefore somehow I had a wrong output. I got some questions though please answer them in your answer (so you edit and I will be able to upvote). 1) what is this 't'? what does it do in your code? 2) the rank appears to be having a column name of 'm'. Why is that? where do you define it? @GMB – oakca Nov 25 '20 at 00:15
1

@oakca: `t` is an alias for the subquery. There is no `m` in the query, though. – GMB Nov 25 '20 at 00:22
>There is no `m` in the query, though. >> But somehow my ranking column's name appears to be `m`. And I don't know why pgAdmin4 decides to give this name. @GMB – oakca Nov 25 '20 at 01:14
@oakca: that’s `rn`, which is the alias given to the rank in the subquery. You can change that to whatever you prefer – GMB Nov 25 '20 at 01:45

Malo · Answer 2 · 2020-11-25T22:02:14.750

You can do it like this: (works with PostgreSQL)

SELECT * from
(
  select ROW_NUMBER() over (ORDER BY value desc) AS rownumber, firstname, value
  from
  (
    SELECT firstname, COUNT(*) AS value
    FROM public.people as pp
    GROUP BY firstname
  ) as goo
) AS foo
WHERE foo.firstname like 'ian%';

This gives the expected result:

3   ian                     1

or if you wants to get the 3rd result:

SELECT * from
(
  select ROW_NUMBER() over (ORDER BY value desc) AS rownumber, firstname, value
  from
  (
    SELECT firstname, COUNT(*) AS value
    FROM public.people as pp
    GROUP BY firstname
  ) as goo
) AS foo
WHERE rownumber = 3

or if you want to LIMIT the intermediate result like this:

SELECT * from
(
  select ROW_NUMBER() over (ORDER BY value desc) AS rownumber, firstname, value
  from
  (
    SELECT firstname, COUNT(*) AS value
    FROM public.people as pp
    GROUP BY firstname
    ORDER BY COUNT(*) desc
    limit 3
  ) as goo
) AS foo
WHERE rownumber = 3

This gives the expected result:

3   ian                     1

Finding the rank of a value in a most common list

2 Answers2