Mysql Query to Group and Limit 1 Desc

Question

Table name : users

I am trying to query this table such that it gives me a result showing - names for users - where - the last approved status - group by name - has manager_2 as Stan

Expected result image below

Something like

SELECT id,name,manager_1,manager_2,department_status_fruits 
FROM users 
WHERE status = --- the last approved entry for (group by Name), 
if manager_2 = stan 
ORDER BY id DESC 
LIMIT 1


SELECT id,name,manager_1,manager_2,department_status_fruits 
FROM users 
WHERE manager_2 = 'stan' AND status = 'approved' 
group by name 
ORDER BY id DESC 
LIMIT 1

None of the queries I tried are giving the expected results, infact I am finding it hard to even form a query to explain the request.

I guess, sub queries or joins is what i will have to go for, please suggest, joins is what I would prefer though.

@Tim Biegeleisen , please refresh - now the details are clear — Manuraj Sebastian, Nov 15 '18 at 00:20

score 0 · Accepted Answer · answered Nov 15 '18 at 00:47

You need to use a query that returns the last approved row for each name. See SQL select only rows with max value on a column for various ways to do this.

Then you can further filter this to those where manager_2 = 'stan'.

SELECT u1.*
FROM users AS u1
JOIN (SELECT name, MAX(id) AS maxid
      FROM users
      WHERE status = 'approved'
      GROUP BY name) AS u2 ON u1.id = u2.id
WHERE manager_2 = 'stan'
ORDER BY id DESC
LIMIT 1

Mysql Query to Group and Limit 1 Desc

1 Answers1