Question 176012 in PostgreSQL Question posted in
The official documentation can be found here.

Can I select entries w/ highest values in multiple cols using a subquery in Postgres?

How do I select a database entry who has the highest value in multiple columns? (PostgreSQL)

I have a horse racing database and two tables I joined together in this statement. Then I sorted the values I want by a WHERE statement. Now I want to SELECT only the entries with the highest value in multiple, specified columns:

I want to select only entries that have the highest lastrace_rating and highest going_rating – if no horse is highest ranked in both categories in this race, I want no response / empty response (does not matter)

So there can only be one Selection per race

I tried something like this:

SELECT h.racename, h.racetime, h.racedate, h.odds, h.pos
FROM horse_ratings hr
RIGHT JOIN horses h ON hr.daily_racename = h.racename
  AND hr.daily_racedate = h.racedate
  AND hr.daily_racetime = h.racetime
  AND hr.dailyhorsename = h.horsename
WHERE 
  hr.daily_racedate = '2023-04-13'
  AND hr.daily_racename = 'Staropramen Zero Alcohol Handicap'
  AND hr.daily_racetime = '4:15'
  AND final_horse_rating > 0
  AND h.odds > 0
  AND hr.dailyhorsename IS NOT NULL 
  AND hr.lastrace_rating = (SELECT MAX(lastrace_rating) FROM horse_ratings)
  AND hr.going_rating = (SELECT MAX(going_rating) FROM horse_ratings);

But it does not work – even if I reduce it to one condition (going_rating), I get no response – although the values are there(Just testet with sorting by ORDER BY lastrace_rating DESC). Tried some variations like

  AND (hr.lastrace_rating, hr.going_rating) IN (
    SELECT MAX(lastrace_rating), MAX(going_rating)
    FROM horse_ratings

But I think I am on the wrong path here.

How do I Select entries based on if they have the highest value in multiple columns in PostgreSQL?
I searched, but could not find an answer.

Edit

Basicly, from this table, I want to select the horse ‘Little Tiger’, because he has the highest values in both ‘lastrace_rating’ and ‘going_rating’

This is the layout for the table horse_ratings:

daily_racedate daily_racetime dailytrack dailyracename dailyhorsename lastrace_rating final_horse_rating going_rating
2023-05-23 05:15:00 Wolverhampton At The Races App Handicap Bailar Contigo 76 80 52
2023-05-23 05:15:00 Wolverhampton At The Races App Handicap Petite Sioux 65 67 50
2023-05-23 05:15:00 Wolverhampton At The Races App Handicap Little Tiger 80 75 90

The table "horses" has this layout:

racetime racedate track racename horsename pos odds
2023-05-23 05:15:00 Wolverhampton At The Races App Handicap Bailar Contigo 3 5
2023-05-23 05:15:00 Wolverhampton At The Races App Handicap Petite Sioux 1 3
2023-05-23 05:15:00 Wolverhampton At The Races App Handicap Little Tiger 2 19
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    SELECT t.daily_time, t.daily_date, t.daily_location, t.daily_name, t.value1, t.value2
FROM (
    SELECT daily_time, daily_date, daily_location, daily_name,value1, value2,
           ROW_NUMBER() OVER (PARTITION BY daily_time, daily_date, daily_location
                              ORDER BY value1 DESC, value2 DESC) AS rn,
           LEAD(value2) OVER (PARTITION BY daily_time, daily_date, daily_location
                              ORDER BY value1 DESC, value2 DESC) AS next_rating2
    FROM test
) t
WHERE t.rn = 1 AND t.next_rating2 <= t.value2;

    This has different column names than the example above.


  2. 0

    It’s not clear from the question or comments when final_horse_rating is to be used, so the following query ignores that column; however, it does address selecting only horses from each race that have the highest rating in both lastrace_rating and going_rating.

    WITH horse_ratings(daily_racedate, daily_racetime, dailytrack, daily_racename, daily_horsename, lastrace_rating,
                   final_horse_rating, going_rating) AS
       (VALUES ('2023-05-23'::date, '05:15:00'::time, 'Wolverhampton', 'At The Races App Handicap', 'Bailar Contigo',
                76, 80, 52),
               ('2023-05-23'::date, '05:15:00'::time, 'Wolverhampton', 'At The Races App Handicap', 'Petite Sioux', 65,
                67, 50),
               ('2023-05-23'::date, '05:15:00'::time, 'Wolverhampton', 'At The Races App Handicap', 'Little Tiger', 80,
                75, 90)),
     horses(racedate, racetime, track, racename, horsename, pos, odds) AS
       (VALUES ('2023-05-23'::date, '05:15:00'::time, 'Wolverhampton', 'At The Races App Handicap', 'Bailar Contigo', 3,
                5),
               ('2023-05-23'::date, '05:15:00'::time, 'Wolverhampton', 'At The Races App Handicap', 'Petite Sioux', 1,
                3),
               ('2023-05-23'::date, '05:15:00'::time, 'Wolverhampton', 'At The Races App Handicap', 'Little Tiger', 2,
                19)),
     max_race_ratings AS (SELECT daily_racename,
                                 daily_racedate,
                                 daily_racetime,
                                 MAX(lastrace_rating) AS max_lastrace_rating,
                                 MAX(going_rating)    AS max_going_rating
                            FROM horse_ratings
                            GROUP BY daily_racename, daily_racedate, daily_racetime)
SELECT h.horsename, h.racename, h.racetime, h.racedate, h.odds, h.pos
  FROM horse_ratings hr
    JOIN max_race_ratings mrr
         ON (hr.daily_racename = mrr.daily_racename AND
             hr.daily_racedate = mrr.daily_racedate AND
             hr.daily_racetime = mrr.daily_racetime AND
             hr.lastrace_rating = mrr.max_lastrace_rating AND
             hr.going_rating = mrr.max_going_rating)
    JOIN horses h
         ON (hr.daily_racename = h.racename AND
             hr.daily_racedate = h.racedate AND
             hr.daily_racetime = h.racetime AND
             hr.daily_horsename = h.horsename);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search