Search code examples
sqlsqlitehaving

How to find results based on item ordering

Say you have a table of Items with UniqueNames, Ids to denote the order, and a RunNumber for each Run

Id Name RunNumber
1 Open 1
2 Close 1
1 Open 2
2 Close 2
1 Close 3
2 Open 3
1 Open 4
2 Close 4

Is there a way to get the RunNumbers where the item order has changed, eg where "Id of 'Close' Task is less than Id of 'Open' Task" (like RunNumber 3) ?

Hoping to learn of a few methods, but one will suffice!

Thanks!

Solution

  • For this sample data you can use aggregation and set the condition in the HAVING clause:

    SELECT RunNumber
FROM Items
GROUP BY RunNumber
HAVING MAX(CASE WHEN Name = 'Close' THEN Id END) < MAX(CASE WHEN Name = 'Open' THEN Id END);

    Or, with a self join:

    SELECT i1.RunNumber
FROM Items i1 INNER JOIN Items i2
ON i1.RunNumber = i2.RunNumber AND i1.id < i2.id
WHERE i1.Name = 'Close' AND i2.Name = 'Open';

    See the demo.