Selecting rows based on 2 conditions

Question

I have 2 tables, records:

CREATE TABLE IF NOT EXISTS `records` (
`recordid` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`platform` varchar(255) NOT NULL,
`track` varchar(255) NOT NULL,
`trackid` int(11) NOT NULL,
`bike` varchar(255) NOT NULL,
`time` decimal(7,3) NOT NULL,
`faults` int(11) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`verified` varchar(3) NOT NULL DEFAULT 'no',
`wr` varchar(3) NOT NULL DEFAULT 'no',
PRIMARY KEY (`recordid`),
KEY `userid` (`userid`)
)

and users:

CREATE TABLE IF NOT EXISTS `users` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(255) NOT NULL,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`country` varchar(50) NOT NULL,
`verified` varchar(3) NOT NULL DEFAULT 'no',
PRIMARY KEY (`userid`)
)

I have some example data stored:

 +----------+--------+----------+----------------+---------+-----------+---------+--------+---------------------+----------+-----+
| recordid | userid | platform |     track      | trackid |   bike    |  time   | faults |        date         | verified | wr  |
+----------+--------+----------+----------------+---------+-----------+---------+--------+---------------------+----------+-----+
|      412 |      1 | Xbox     | Turbine Terror |       1 | Pit Viper | 90.456  |      6 | 2017-02-18 19:54:27 | yes      | yes |
|      413 |      1 | Xbox     | Turbine Terror |       1 | Pit Viper | 75.458  |      4 | 2017-02-18 19:54:39 | yes      | yes |
|      414 |      1 | Xbox     | Turbine Terror |       1 | Pit Viper | 77.885  |      2 | 2017-02-18 19:55:02 | yes      | yes |
|      415 |      1 | Xbox     | Turbine Terror |       1 | Pit Viper | 59.441  |      1 | 2017-02-18 19:55:12 | yes      | yes |
|      416 |      1 | Xbox     | Turbine Terror |       1 | Pit Viper | 52.145  |      0 | 2017-02-18 19:55:21 | yes      | yes |
|      417 |      1 | Xbox     | Turbine Terror |       1 | Pit Viper | 48.444  |      0 | 2017-02-18 19:55:26 | yes      | yes |
|      418 |      1 | Xbox     | Turbine Terror |       1 | Pit Viper | 42.753  |      0 | 2017-02-18 19:55:33 | yes      | yes |
|      419 |      1 | Xbox     | Turbine Terror |       1 | Pit Viper | 39.701  |      0 | 2017-02-18 19:55:42 | yes      | yes |
|      420 |      1 | Xbox     | Inferno IV     |      40 | Pit Viper | 745.159 |    254 | 2017-02-18 20:17:35 | yes      | yes |
|      421 |      1 | Xbox     | Inferno IV     |      40 | Pit Viper | 575.169 |    128 | 2017-02-18 20:17:50 | yes      | yes |
|      422 |      1 | Xbox     | Inferno IV     |      40 | Pit Viper | 465.456 |    101 | 2017-02-18 20:18:12 | yes      | yes |
|      423 |      1 | Xbox     | Inferno IV     |      40 | Pit Viper | 321.247 |     75 | 2017-02-18 20:18:29 | yes      | yes |
|      424 |      1 | Xbox     | Inferno IV     |      40 | Pit Viper | 236.456 |     35 | 2017-02-18 20:18:58 | yes      | yes |
|      425 |      1 | Xbox     | Inferno IV     |      40 | Pit Viper | 165.359 |     25 | 2017-02-18 20:19:17 | yes      | yes |
|      426 |      1 | Xbox     | Waterworks     |       2 | Pit Viper | 45.452  |    457 | 2017-02-18 23:13:12 | yes      | yes |
|      427 |      1 | Xbox     | Turbine Terror |       1 | Pit Viper | 34.123  |      0 | 2017-02-18 23:21:47 | yes      | yes |
|      428 |      1 | Xbox     | Turbine Terror |       1 | Pit Viper | 32.254  |      0 | 2017-02-18 23:24:32 | yes      | yes |
|      429 |      1 | Xbox     | Turbine Terror |       1 | Pit Viper | 31.169  |      0 | 2017-02-18 23:25:33 | yes      | yes |
|      430 |      1 | Xbox     | Waterworks     |       2 | Pit Viper | 50.000  |      0 | 2017-02-20 20:06:23 | yes      | yes |
|      431 |      3 | Xbox     | Turbine Terror |       1 | Pit Viper | 25.123  |      0 | 2017-02-20 20:21:54 | no       | yes |
+----------+--------+----------+----------------+---------+-----------+---------+--------+---------------------+----------+-----+

I have a query in development:

SELECT users.username, 
       records.platform, 
       records.track, 
       records.bike,  
       records.time, 
       records.faults, 
       records.date 
FROM records 
INNER JOIN users 
   ON records.userid = users.userid 
INNER JOIN (SELECT track, time, MIN(faults) AS faults 
            FROM records GROUP BY track) AS tracksWithMinFaults 
   ON records.track = tracksWithMinFaults.track 
  AND records.faults = tracksWithMinFaults.faults 
WHERE users.userid = 1  
ORDER BY records.trackid ASC

The results from the query on the data set above:

+----------------+----------+----------------+-----------+---------+--------+---------------------+
|    username    | platform |     track      |   bike    |  time   | faults |        date         |
+----------------+----------+----------------+-----------+---------+--------+---------------------+
| TheRealTeeHill | Xbox     | Turbine Terror | Pit Viper | 52.145  |      0 | 2017-02-18 19:55:21 |
| TheRealTeeHill | Xbox     | Turbine Terror | Pit Viper | 48.444  |      0 | 2017-02-18 19:55:26 |
| TheRealTeeHill | Xbox     | Turbine Terror | Pit Viper | 42.753  |      0 | 2017-02-18 19:55:33 |
| TheRealTeeHill | Xbox     | Turbine Terror | Pit Viper | 39.701  |      0 | 2017-02-18 19:55:42 |
| TheRealTeeHill | Xbox     | Inferno IV     | Pit Viper | 165.359 |     25 | 2017-02-18 20:19:17 |
| TheRealTeeHill | Xbox     | Turbine Terror | Pit Viper | 34.123  |      0 | 2017-02-18 23:21:47 |
| TheRealTeeHill | Xbox     | Turbine Terror | Pit Viper | 32.254  |      0 | 2017-02-18 23:24:32 |
| TheRealTeeHill | Xbox     | Turbine Terror | Pit Viper | 31.169  |      0 | 2017-02-18 23:25:33 |
| TheRealTeeHill | Xbox     | Waterworks     | Pit Viper | 50.000  |      0 | 2017-02-20 20:06:23 |
+----------------+----------+----------------+-----------+---------+--------+---------------------+

A little background: I am trying to pull the "best" record for each track for a given user ID. The best record can be defined as the record with the lowest faults and lowest time. The query I have developed so far is pulling all the records for a user that have the lowest faults. The query does not consider time yet!

My question: How do I find the lowest time for each track with the lowest faults?

This is my first post on StackOverflow, if I've missed anything or done something wrong please let me know. Thanks in advance for your time :)

Edit: I can't see a solution in the suggested duplicate post as I am looking for 2 minimum values, first I need to pull all of the lowest fault records, which my query is doing, and then pull the lowest time records from that set? If the solution is in that other post I cannot see it :(

This is the result I'm trying to achieve:

+----------------+----------+----------------+-----------+---------+--------+---------------------+
|    username    | platform |     track      |   bike    |  time   | faults |        date         |
+----------------+----------+----------------+-----------+---------+--------+---------------------+
| TheRealTeeHill | Xbox     | Inferno IV     | Pit Viper | 165.359 |     25 | 2017-02-18 20:19:17 |
| TheRealTeeHill | Xbox     | Turbine Terror | Pit Viper | 31.169  |      0 | 2017-02-18 23:25:33 |
| TheRealTeeHill | Xbox     | Waterworks     | Pit Viper | 50.000  |      0 | 2017-02-20 20:06:23 |
+----------------+----------+----------------+-----------+---------+--------+---------------------+

Looks ok, but we prefer data sample as text [why-may-i-not-upload-images-of-code](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) — Juan Carlos Oropeza, Feb 21 '17 at 16:04
Possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) — Juan Carlos Oropeza, Feb 21 '17 at 16:06
Show me some sample data and expected result as text table [why-may-i-not-upload-images-of-code](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) — Juan Carlos Oropeza, Feb 21 '17 at 17:12
How do these numbers (faults and time) relate? E.g. what if you have two records: 3 faults and 54 time, and 2 faults and 56 time. One has fewer faults, one has lower time. Which one is "best?" — miken32, Feb 21 '17 at 19:09
@miken32 In the last edit OP specify it. `first I need to pull all of the lowest fault records, and then pull the lowest time records from that set` — Juan Carlos Oropeza, Feb 21 '17 at 19:17
@miken32 Faults come before time, so a record with a time of 60 and 0 faults is better than a time of 30 with 1 fault :) — Tee Hill, Feb 21 '17 at 19:35

Juan Carlos Oropeza · Answer 1 · 2017-02-21T19:23:09.790

2

Use variables to create a row_number for each track, but here I see you may have an issue with ties. Not sure how you want handle those.

DEMO

SELECT *
FROM (
        SELECT records.userid, 
               records.platform, 
               records.track, 
               records.bike,  
               records.time, 
               records.faults, 
               records.date,
               @row := IF( @track = records.track, 
                           @row + 1,
                           IF(@track := records.track, 1, 1)
                         ) as rn
        FROM records 
        CROSS JOIN (SELECT @row := 0, @track := '') as par
        WHERE records.userid = 1
        ORDER BY records.trackid ASC,
                 records.faults ASC,
                 records.time ASC
     ) as T
WHERE T.rn = 1;

OUTPUT

edited Feb 21 '17 at 19:23

answered Feb 21 '17 at 19:08

Juan Carlos Oropeza

47,252
12
78
118

this works perfectly after I added "WHERE records.userid = 1" Could you explain what you mean by "you may have an issue with ties. Not sure how you want handle those." Thanks. – Tee Hill Feb 21 '17 at 19:12
Yes, I just fix it with the user. Saw the error on the image when appear `userid=3`. The ties, on cases when a player achive same faults and same time on the same track in two differents dates. Do you want show both rows, the earliest, the latest? – Juan Carlos Oropeza Feb 21 '17 at 19:14
Users can only ever input improved records, so a record with the same time and faults as an existing record would not be considered an improvement and could not be entered :) thanks for pointing that out though. I can see how that could be an issue. – Tee Hill Feb 21 '17 at 19:19
Ok. Then this should be your query ;) – Juan Carlos Oropeza Feb 21 '17 at 19:20
Just so I am clear, did the suggested duplicate "How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?" answer my question? – Tee Hill Feb 21 '17 at 19:42
well isnt a direct answer, but point you in the right direction. As you can see in my comment to @denny I use that solution to nest two subquerys and also work. – Juan Carlos Oropeza Feb 21 '17 at 19:44

denny · Answer 2 · 2017-02-21T19:29:54.777

0

try this

   SELECT 
         users.name,records.platform,records.track,records.min_time,records.bike,records.time,records.min_faults,records.date  
   FROM users 
   INNER JOIN 
   (SELECT MIN(time) as min_time,time, MIN(faults) AS min_faults,faults,platform,track,bike,date   FROM records GROUP BY trackid) records 
   ON records.time = records.min_time  
   AND records.faults = records.min_faults  
   AND records.recordid = users.userid  
   WHERE users.userid = 1    
   ORDER BY records.trackid ASC;

edited Feb 21 '17 at 19:29

answered Feb 21 '17 at 19:06

denny

2,084
2
15
19

1

This doesnt work, because you need the `min(time)` but only between the `min(faults)` – Juan Carlos Oropeza Feb 21 '17 at 19:15
1

The `group by date` isnt necesary. You need do a subquery to find the `MIN(fault)` then another subquery to find the `MIN(time)` with `fault = MIN(fault)`. you can try your query in my demo rextester and see the difference. – Juan Carlos Oropeza Feb 21 '17 at 19:21
without group by how i find min fault or min time thats will return only single row – denny Feb 21 '17 at 19:22
1

I see, you need `group by track_id` not `date` – Juan Carlos Oropeza Feb 21 '17 at 19:24
1

I update my [**demo**](http://rextester.com/PMDKD27794) to include this different aproach in the last query. As you can see is a litle more complicated as you tought. – Juan Carlos Oropeza Feb 21 '17 at 19:37

Selecting rows based on 2 conditions

2 Answers2