Geek asks and answers

Select max id from current mysql id

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 Id managers prior to the current name of non-managers. So, for example, the result for bob would be Tony or 3.

I can figure out how to do this with two queries

SELECT MAX( item_order) AS parent
FROM tablename WHERE item_order < 5 && Manager =  'Y'

As a result, I will make another item_order element. Is there a way to do this in just one choice?

+3
source share
2 answers
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
0
source 
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 a list of all non-managers along with the names of those managers that immediately precede them (based on item_order).

If you want to return only one row for a specific non-manager, add one more condition:

  AND n.Name = 'bob'
0
source

More articles:


All Articles