CODEWITHSUNDEEP
mysql
Astrid
Astrid

Reputation: 1312

MYSQL Select all where value is highest

In a MYSQL query, how can i select all of the rows where a value is highest for example:

SELECT      UNIX_TIMESTAMP(creation_date) AS `date`,
            release_version
FROM        content
WHERE       id = '1' 
ORDER BY    `date` DESC

This will output for example:

output

Note: the amount of rows with the same/different release_version is variable and therefore i can't use LIMIT.

i only want to select those where the release_version is highest (3 in this case)

How can i do this? Thanks.

Upvotes: 1

Views: 145

Answers (2)

John Woo
John Woo

Reputation: 263883

This could be simply as

SELECT * 
FROM   content
WHERE  release_version = (SELECT MAX(release_version) FROM content)

Upvotes: 4

Martin Perry
Martin Perry

Reputation: 9527

Use HAVING clausule.

SELECT      UNIX_TIMESTAMP(creation_date) AS `date`,
        MAX(`release_version`) as max,
FROM        content
WHERE       id = '1' 
HAVING max > 3
ORDER BY    `date` DESC

EDIT

OR you can use

SELECT      UNIX_TIMESTAMP(creation_date) AS `date`,
       `release_version` as max,
FROM        content
WHERE       id = '1' AND release_version = (SELECT MAX(`release_version`) FROM content WHERE id = '1')
ORDER BY    `date` DESC

Upvotes: 1

Related Questions