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'