select max id from current id mysql
Given the database.....
ID Name item_order Manager
1 ted 2 N
2 bob 5 N
3 tony 1 Y
4 fred 3 N
5 william 4 N
6 george 6 Y
7 cade 8 N
8 matt 7 N
I would like to be able to select managers Id prior to the current non managers name. So for example the result for bob would be tony or 3.
I can figure out how to do this with two requests
SELECT MAX( item_order) AS parent
FROM tablename WHERE item_order < 5 && Manager = 'Y'
The result from that I would make another select by the item_开发者_StackOverfloworder. Is there a way to do this all in one select?
SELECT * FROM tablename
WHERE Manager = 'Y'
AND item_order = (SELECT MAX(item_order) AS parent
FROM tablename
WHERE item_order < 5
AND Manager = 'Y') AS t
SELECT
n.*,
ManagerName = m.Name
FROM tablename n
LEFT JOIN tablename m ON m.Manager = 'Y' AND n.item_order > m.item_order
LEFT JOIN tablename m2 ON m2.Manager = 'Y'
AND m2.item_order < n.item_order AND m2.item_order > m.Item_order
WHERE n.Manager = 'N'
AND m2.ID IS NULL
This will give you the list of all non-managers together with the names of those managers that directly precede them (based on item_order
).
If you only want to return one row for a specific non-manager, add one more condition like this:
AND n.Name = 'bob'
精彩评论