Question: Use MySQL IN condition in FIND_IN_SET

Question

Use MySQL IN condition in FIND_IN_SET

Answers 1
Added at 2017-01-01 14:01
Tags
Question

I have a query where I use a pretty intensive WHERE IN clause to get a list of ID's related to players.

SELECT p.name,
  0a.stat_value
FROM leaderheadsplayers p
LEFT JOIN leaderheadsplayersdata_alltime 0a
ON 0a.player_id    = p.player_id
AND 0a.stat_type   = 'kills'
WHERE p.player_id IN
  (SELECT player_id
  FROM
    (SELECT 0a.player_id
    FROM leaderheadsplayersdata_alltime 0a
    WHERE 0a.stat_type = 'kills'
    ORDER BY 0a.stat_value DESC LIMIT 0,
      20
    ) 1a
  )

The problems is that I want to keep the order of these ID's from the IN clause in my final result by using a FIND_IN_SET without doing the intensive query from the IN clause again. Something like this:

SELECT p.name,
  0a.stat_value
FROM leaderheadsplayers p
LEFT JOIN leaderheadsplayersdata_alltime 0a
ON 0a.player_id    = p.player_id
AND 0a.stat_type   = 'kills'
WHERE p.player_id IN
  (SELECT player_id
  FROM
    (SELECT 0a.player_id
    FROM leaderheadsplayersdata_alltime 0a
    WHERE 0a.stat_type = 'kills'
    ORDER BY 0a.stat_value DESC LIMIT 0,
      20
    ) 1a
  )
ORDER BY FIND_IN_SET(p.player_id, result_from_in_clause)

This is my current output:

player_id | stat_value
3 | 304
5 | 507
4 | 208

This is what I want to get:

player_id | stat_value
5 | 507
3 | 304
4 | 208
Answers to

Use MySQL IN condition in FIND_IN_SET

nr: #1 dodano: 2017-01-01 14:01

Simple order by should be able able to do it:

SELECT p.name,
  0a.stat_value
FROM leaderheadsplayers p
LEFT JOIN leaderheadsplayersdata_alltime 0a
ON 0a.player_id    = p.player_id
AND 0a.stat_type   = 'kills'
WHERE p.player_id IN
  (SELECT player_id
  FROM
    (SELECT 0a.player_id
    FROM leaderheadsplayersdata_alltime 0a
    WHERE 0a.stat_type = 'kills'
    ORDER BY 0a.stat_value DESC LIMIT 0,
      20
    ) 1a
  )
order by 
  0a.stat_value,  p.player_id;
Source Show
◀ Wstecz