phpmyadmin Question posted in
The official documentation can be found here.

MySQL Display 4th smallest value for each team – Phpmyadmin

I am using phpMyAdmin on MySQL 5.7
The code below selects the lowest values excluding any zero values and gives me a nice table of all the teamids with the lowest times in seconds next to them for that event (zid).

SELECT teamid, MIN(time) AS 'fastest time'
FROM data
WHERE time > 0 AND zid = 217456
GROUP BY teamid

How do I adapt it to get the 4th lowest values?
I have tried countless suggestions found via searching but none work

Table Headings:
id (AI column set as Primary Index)
zid (this is an event identification number)
teamid
name
time (given in seconds)

I could add a position in team column which would make this very easy? Then I just ask MySQL to get me all the positions = to 4 ?

Tags:

3

Answers


  1. 0

    If your version supports window-functions (since 8.0):

    SELECT teamid, time 'fourth_time'
FROM data
WHERE time > 0
    AND zid = 217456
    AND (dense_rank() OVER (PARTITION BY teamid ORDER BY time ASC)) = 4

    EDIT: dense_rank seems to fit better, it will give the fourth-best time now, ignoring multiple appearances of the best to third-best times. The earlier version used row_number, not ignoring multiple apperances. Thanks for mentioning in the comments.

    Login or Signup to reply.
  2. 0

    Since your version does not support window-functions, you can use a subselect with a LIMIT (I assume you have a field id, that is a primary key. If your primary key is another field, just replace this. If there is more than one field in your primary key, you will need to check all of them):

    SELECT d.teamid, MIN(d.time) fourth_time
FROM data d
WHERE d.time > 0
    AND d.zid = 217456
    AND d.time > (SELECT t.time
                  FROM ( SELECT DISTINCT d2.time
                         FROM data d2
                         WHERE d2.time > 0
                             AND d2.zid = 217456
                             AND d2.teamid = d.teamid
                       ) t
                  ORDER BY t.time ASC
                  LIMIT 1
                  OFFSET 2)
GROUP BY d.teamid
    Login or Signup to reply.
  3. 0

    MySQL 8: Use Window functions.

    SELECT 
    teamid, 
    time '4th_Lowest'
FROM data
WHERE time > 0 AND zid = 217456
    AND (dense_rank() OVER (PARTITION BY teamid ORDER BY time ASC)) = 4;

    Mysql 5.7 and Lower: We will use following variables to calculate this on the sorted data(teamid and then time)

    • rank – to set rank for each unique(teamid, time)
    • c_time – whenever there is a change between time of two consecutive rows, we will increase the rank. IF(@c_time = d.time, @rank, @rank:= @rank + 1)
    • c_team_id – we will check whether two consecutive rows have same or different team, if different then reset rank to 1. Check else part IF(@c_team_id = d.teamid, …,@rank:= 1)
    SELECT 
    t.teamid,            
    t.`time`              
FROM(
    SELECT 
        d.teamid,           -- Represent current row team id
        d.`time`,           -- Represent current row time
        IF(@c_team_id = d.teamid, IF(@c_time = d.`time`, @rank, @rank:= @rank + 1), @rank:= 1) as rank,  -- determine rank based on above explanation.
        @c_team_id:= d.teamid,  -- We are setting this variable to current row team id after using it in rank column, so rank column of next row will have this row team id for comparison using @c_team_id variable.
        @c_time:= d.`time`
    FROM `data` AS d,
    (SELECT @c_time:= 0 as tim, @c_team_id:= 0 as tm_id, @rank:= 0 as rnk) AS t
    WHERE d.`time` > 0 AND d.zid = 217456
    ORDER BY d.teamid, d.`time` ASC   -- Use to make sure we have same team records in sequence and with ascending order of time.
) AS t
WHERE t.rank = 4
GROUP BY t.teamid;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search