CODEWITHSUNDEEP
mysql
Cyclone
Cyclone

Reputation: 15269

MySQL calculations with values

I'm calculating the MIN, MAX and the AVG of player level:

SELECT
     MIN(`p`.`level`) AS 'min',
     MAX(`p`.`level`) AS 'max',
     AVG(`p`.`level`) AS 'avg'
FROM `dfh`.`players` `p`

How can how can I get the p.name field value which refers to the MIN and MAX?

Fe. I got this values in my p table:

Lp. | Name | Level
1   | Test | 12
2   | Tesx | 15
3   | Anot | 150

And the query will withdraw the following:

MIN: 12
MAX: 150

But I want it to withdraw also the Name corresponding to the MIN and MAX value:

MIN: 12 Test
MAX: 150 Anot

How can I do that?

Upvotes: 0

Views: 151

Answers (2)

Devart
Devart

Reputation: 121912

Try this query -

SELECT 'MIN' property, GROUP_CONCAT(p1.name) name, p1.level FROM players p1
  JOIN (SELECT MIN(level) level FROM players) p2
    ON p1.level = p2.level
UNION
SELECT 'MAX' property, GROUP_CONCAT(p1.name) name, p1.level FROM players p1
  JOIN (SELECT MAX(level) level FROM players) p2
    ON p1.level = p2.level

Query with the highest 'experience' value and any 'id' -

SELECT * FROM

  (SELECT 'MIN' property, p1.id, p1.level, p1.name FROM players p1
    JOIN (
      SELECT
        level, MAX(experience) experience
      FROM
        players,
        (SELECT @level:=MIN(level) FROM players) t
      WHERE level = @level
    ) p2
    ON p1.experience = p2.experience AND p1.level = p2.level
  ORDER BY id LIMIT 1) t_min

UNION

SELECT * FROM 

  (SELECT 'MAX' property, p1.id, p1.level, p1.name FROM players p1
    JOIN (
      SELECT
        level, MAX(experience) experience
      FROM
        players,
        (SELECT @level:=MAX(level) FROM players) t
      WHERE level = @level
    ) p2
    ON p1.experience = p2.experience AND p1.level = p2.level
  ORDER BY id LIMIT 1) t_max

Use this dataset as a subquery to join with another table -

SELECT * FROM 
(
  SELECT * FROM

    (SELECT 'MIN' property, p1.id, p1.level, p1.name FROM players p1
      JOIN (
        SELECT
          level, MAX(experience) experience
        FROM
          players,
          (SELECT @level:=MIN(level) FROM players) t
        WHERE level = @level
      ) p2
      ON p1.experience = p2.experience AND p1.level = p2.level
    ORDER BY id LIMIT 1) t_min

  UNION

  SELECT * FROM 

    (SELECT 'MAX' property, p1.id, p1.level, p1.name FROM players p1
      JOIN (
        SELECT
          level, MAX(experience) experience
        FROM
          players,
          (SELECT @level:=MAX(level) FROM players) t
        WHERE level = @level
      ) p2
      ON p1.experience = p2.experience AND p1.level = p2.level
    ORDER BY id LIMIT 1) t_max
) p
LEFT JOIN guild_member gm
  ON gm.id = p.id

Upvotes: 2

Bohemian
Bohemian

Reputation: 424993

This will return the low/high names efficiently:

select 'MIN', level, name from players order by level limit 1
union all
select 'MAX', level, name from players order by level desc limit 1

Notice the lack of subqueries...

Upvotes: 1

Related Questions