CODEWITHSUNDEEP
mysqlaggregate-functions
Son of the Wai-Pan
Son of the Wai-Pan

Reputation: 13191

How do I reference the MIN SQL function in my WHERE clause using a subtable?

I have a table of lastNames and userNames. I want the 'smallest' (lexically speaking) value for a lastName and the corresponding userName. This is what I have (and this works):

SELECT lastName, userName FROM user  WHERE lastName IN (SELECT MIN(lastName) FROM user);

Now I want to do this but do it on a subset of the table (for example, only for the lastNames starting with 'P'):

SELECT subT.lastName, subT.userName
FROM (SELECT lastName, userName FROM user WHERE lastName LIKE 'p%') AS subT
WHERE subT.lastName=MIN(subT.lastName)

This doesn't work. I tried to put the MIN function as a select_item but I also want the userName that corresponds with the MIN function. How do I do this?

Upvotes: 0

Views: 10729

Answers (1)

zerkms
zerkms

Reputation: 254916

  SELECT lastName,
         userName
    FROM user
   WHERE lastName LIKE 'p%'
ORDER BY lastName DESC
   LIMIT 1

If you expect to get several users with the same lastname, then use subquery:

  SELECT lastName,
         userName
    FROM user
   WHERE lastName = (SELECT MIN(lastName)
                       FROM users
                      WHERE lastName LIKE 'p%')

Upvotes: 3

Related Questions