PostgreSQL: Selecting second lowest value for each unique race?

Question

I have the following table using PostgreSQL

ID	racedate	racetime	racename	track	horsename	pos	weight
1	2022-1-1	01:00	race1	test	MrEd	1	56
2	2022-1-1	01:00	race1	test	SeaBiscuit	2	54
3	2022-1-1	01:00	race1	test	Frankel	3	51
4	2022-1-1	02:00	race2	test	SeaRose	1	57
2	2022-1-1	02:00	race2	test	WarAdmiral	2	65

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!

Answer 1

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.