CODEWITHSUNDEEP
sqlsql-serversql-order-by
Dajito
Dajito

Reputation: 101

SQL - How can you filter entries depending on the order I put them in?

The select I am trying to write orders the data by multiple columns. I want to filter out all entries that are after or before a specific entry in that order.

For example:

ORDER BY DATE, ID

gets me:

ID DATE NAME
1 13.01.2021 Alice
3 15.01.2021 Bob
4 15.01.2021 Charlie
6 15.01.2021 Frank
2 18.01.2021 Dave
5 18.01.2021 Eve

I only want the entries that come after Charlie in this order. (Frank, Dave and Eve)

Is there an easier way to do this than to write a confusing where-clause?

Upvotes: 0

Views: 896

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think the simplest method uses row_number():

select t.*
from (select t.*,
             max(case when name = 'Charlie' then seqnum end) as charlie_seqnum
      from (select t.*, row_number() over (order by date, id) as seqnum
            from t
           ) t
     ) t
where seqnum > charlie_seqnum;

An alternative method uses exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.name = 'Charlie' and
                    (t2.date < t.date or
                     t2.date = t.date and t2.id < t.id
                    )
            );

Both of these check that 'Charlie' comes before the row, when ordered by date and id,

Upvotes: 0

Thom A
Thom A

Reputation: 95574

The WHERE, for this, would be:

...
WHERE [Date] > '20210115'
   OR ([Date] = '20210115' AND [Name] > 'Charlie')

I wouldn't say that's particularly confusing.

Upvotes: 1

Related Questions