I have the following table using PostgreSQL

And so on. There are many more columns and rows of course.

Now I want to select the second lowest value in weight for each race. So I want only one result for each unique race in the DB - How do I do that?

I tried MIN() and LEAST() in variations like

SELECT DISTINCT ON (races.id) races.id, MIN( weight ), horses.horsename, races.racename, races.racedate, races.track
FROM horses
RIGHT JOIN races ON races.racedate = horses.racedate AND races.racetime = horses.racetime AND races.racename = horses.racename AND races.track = horses.track

WHERE weight > ( SELECT MIN( weight )
                FROM horses )
GROUP BY races.id, weight, horses.horsename, races.racename, races.racedate, races.track
ORDER BY races.id, weight

But this gives me the lowest value - not the second lowest.

racedate racetime track racename are unique and together the primary key, I added the ID afterwards.

Thank you all for your help!

CodePudding user response：

Use DENSE_RANK here:

WITH cte AS (
    SELECT r.id, h.weight, h.horsename, r.racename, r.racedate, r.track,
           DENSE_RANK() OVER (PARTITION BY r.racename ORDER BY h.weight) drnk
    FROM races r
    LEFT JOIN horses h
        ON r.racedate = h.racedate AND
           r.racetime = h.racetime AND
           r.racename = h.racename AND
           r.track = h.track
)

SELECT id, weight, horsename, racename, racedate, track
FROM cte
WHERE drnk = 2;

Note that RANK() or ROW_NUMBER() might also make sense here, depending on your requirements.