Finding lowest values in dataset - postgres-CodePudding

I am currently working on a query where I pull a racer and three different race times associated with the racer.

select
   r.id,
   r.race1,
   r.race2,
   r.race3
from racer r
where r.race_id = 1

There are about 50 racers and each racer will have three different race time values race1, race2 and race3. I am looking to add three more boolean values that indicate if they are the fastest time in each race.

select
   r.id,
   r.race1,
   -- race1_fastest,
   r.race2,
   --- race2_fastest,
   r.race3,
   --- race3_fastest,
from racer r
where r.race_id = 1

I have tried creating case statements using min() and a whole other table using with but am failing to get this working. Is there a standard method of comparing all the racers here? Or will I need to create a separate query?

CodePudding user response：

Find the fastest times for each race, then join to it:

with fastest_times as (
    select
        race_id,
        min(race1) as race1_fastest,
        min(race2) as race2_fastest,
        min(race3) as race3_fastest
    from racer
    group by race_id
)
select
    r.id,
    race1,
    race1 = race1_fastest AS race1_fastest,
    race2,
    race2 = race2_fastest AS race2_fastest,
    race3,
    race3 = race3_fastest AS race3_fastest
from racer r
join fastest_times ft on ft.race_id = r.race_id
where r.race_id = 1
order by r.id

Not sure about which columns should be involved as you haven't shared your schema.

Also, the table name racer seems to be poorly named and should perhaps be racer_times or similar, and probably have foreign keys to racer and race.

CodePudding user response：

Postgres provides the scalar LEAST function which we can help here:

WITH cte AS (
    SELECT *, LEAST(race1, race2, race3) AS fastest
    FROM racer
)

SELECT
    id,
    race1,
    race1 = fastest AS race1_fastest,
    race2,
    race2 = fastest AS race2_fastest,
    race3,
    race3 = fastest AS race3_fastest
FROM cte
ORDER BY id;