CODEWITHSUNDEEP
mysql
Muhammad Ashikuzzaman
Muhammad Ashikuzzaman

Reputation: 3143

Selecting data where a column is maximum

I have a MySQL table like below.

  ID       X     Y    Z
  1        1     1    1
  2        7     4    2
  2        9     4    3
  3        2     3    1
  4        2     2    1
  4        2     2    2
  5        3     3    1

I want to select X where ( ID=2 and Y=4 and Z Maximum). I have do this code but cant understand where to add the MAX function.

$check_x=mysql_query("SELECT X from Hamdun_Soft where ID='2' AND Y='4'");

Please help me with MySQL.

Upvotes: 0

Views: 50

Answers (2)

mochalygin
mochalygin

Reputation: 742

There already was right variant if you need only one X with maximum Z

SELECT 
  X
FROM
  Hamdun_Soft 
WHERE ID = '2' 
  AND Y= '4' 
ORDER BY Z DESC 
LIMIT 1

And variant with subquery if there may be not only one X with maximum Z.

SELECT 
  X
FROM
  Hamdun_Soft 
WHERE ID = '2' 
  AND Y = '4'
  AND Z = (SELECT MAX(Z) FROM Hamdun_Soft WHERE ID = '2' AND Y = '4')

P. S.

And don't forget what mysql_* family of function is deprecated now. You can use mysqli_* or PDO.

Upvotes: 2

Sahin Yanlık
Sahin Yanlık

Reputation: 1201

SELECT 
  X
FROM
  Hamdun_Soft 
WHERE ID = '2' 
  AND Y= '4' 
ORDER BY Z DESC 
LIMIT 1

Upvotes: 2

Related Questions