CODEWITHSUNDEEP
mysql
red90
red90

Reputation: 1

Find the lowest value of every row of selected columns

As the result of the query, I want to get all rows (Drivers), order by the drivers who got most series wins. If a driver has won 4 tacks at least one or more times but failed to win the remaining track at least once, his series count is 0.

Driver Table 

ID|Name| .........

1   A        
2   B        
3   C        
4   D

Tracks Table

TID |FK|Track1_Wins|Track2_Wins| Track3_Wins|Track4_Wins|Track5_Wins|

1     1   5           6            3           2           4

2     2   2           4            0           5           3

3     3   6           3            9           4           7  

4     4   5           8            2           4           1

My code sample

SELECT `Drivers`.`Name`, LEAST(`Track1_Wins`, `Track2_Wins`, `Track3_Wins`, `Track4_Wins`, `TRACK5_Wins`) AS Series
FROM `Drivers`, `Tracks`
ORDER BY Series DESC;

Accidently I got part expected output when I use WHERE with Driver ID 

SELECT `Drivers`.`Name`, LEAST(`Track1_Wins`, `Track2_Wins`, `Track3_Wins`, `Track4_Wins`, `TRACK5_Wins`) AS Series FROM `Drivers`, `Tracks` WHERE `Drivers`.`ID` = 2 ORDER BY Series DESC;

It will give the expected result but with Same Driver Name as expected

B 3

B 2

B 1

B 0

My expected output is

Name | Series

C           3

A           2

D           1

B           0

Upvotes: 0

Views: 30

Answers (1)

Abishek Aditya
Abishek Aditya

Reputation: 812

Run this,

SELECT d.`Name`, 
  LEAST(`Track1_Wins`, `Track2_Wins`, `Track3_Wins`, `Track4_Wins`, `TRACK5_Wins`) AS Series 
FROM `Drivers` d  INNER JOIN `Tracks` t  
ON t.`FK` = d.`ID` 
ORDER BY Series DESC;

This returns the user name associated with the FK. Also, try to use kebab_case and lower case for all your column and table name. Makes it much easier to run the code

Upvotes: 2

Related Questions