Trying to find the second largest value in a column (postgres sql)

sql postgresql greatest-n-per-group
diesel picture diesel · Feb 6, 2011 · Viewed 14.7k times · Source

I am trying to find the second largest value in a column and only the second largest value.

select a.name, max(a.word) as word
from apple a
where a.word < (select max(a.word) from apple a)
group by a.name;

For some reason, what I have now returns the second largest value AND all the lower values also but fortunately avoids the largest value.

Is there a way to fix this?

Answer

seb picture seb · Apr 4, 2011

Here's another conceptually simple solution that's been running for me in .1 milliseconds on a table of 21 million rows, according to EXPLAIN ANALYZE. It returns nothing in the case where there's only one value.

SELECT a.name, 
(SELECT word FROM apple ap WHERE ap.name=a.name ORDER BY word ASC OFFSET 1 LIMIT 1) 
FROM apple a

Note that my table already had existing indices on name, word, and (name, word), which allows me to use ORDER BY like that.

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
SQL query to get most recent row for each instance of a given key

I'm trying to get the ip, user, and most recent timestamp from a table which may contain both the current ip for a user and one or more prior ips. I'd like one row for each user containing the most …

Read More
How to select id with max date group by category in PostgreSQL?

For an example, I would like to select id with max date group by category, the result is: 7, 2, 6 id category date 1 a 2013-01-01 2 b 2013-01-03 3 c 2013-01-02 4 a 2013-01-02 5 b 2013-01-02 6 c 2013-01-03 7 a 2013…

Read More