SQL - get min values from a column based on values from two other columns-CodePudding

I am trying to collect values of a column that are less than another value of the column that has a specific value in a second column for each instance of a unique value in a third column.

Here's an example.

user_name	time	succeeds
bill	0300	no
bill	0400	yes
bill	0500	no
annie	1200	yes
annie	1400	yes
jonny	0900	no
jonny	1000	no
jonny	1400	yes
jonny	1900	yes

So for each user, I want to find the earliest time they succeed (a 'yes') and then collect all the times below these times.

For bill, this would be 0300 For annie there is no time For jonny this would be 1000 and 0900

CodePudding user response：

SELECT t.*
FROM mytable t
JOIN (
  SELECT user_name, MIN(time) AS time
  FROM mytable
  WHERE succeeds = 'yes'
  GROUP BY user_name
) m ON m.user_name = t.user_name 
   AND m.time > t.time 
   AND t.succeeds = 'no'

CodePudding user response：

Use a correlated subquery in the WHERE clause that returns the 1st row with succeeds = 'yes' for each user_name:

SELECT t1.*
FROM tablename t1
WHERE t1.time < (
  SELECT MIN(CASE WHEN t2.succeeds = 'yes' THEN t2.time END)
  FROM tablename t2
  WHERE t2.user_name = t1.user_name
);

Or:

SELECT t1.*
FROM tablename t1
WHERE t1.time < (
  SELECT t2.time
  FROM tablename t2
  WHERE t2.user_name = t1.user_name AND t2.succeeds = 'yes'
  ORDER BY t2.time LIMIT 1
);

Or, with NOT EXISTS:

SELECT t1.*
FROM tablename t1
WHERE NOT EXISTS (
  SELECT 1
  FROM tablename t2
  WHERE t2.user_name = t1.user_name 
    AND t2.succeeds = 'yes'
    AND t2.time <= t1.time
);

See the demo.