CODEWITHSUNDEEP
sqlsql-server
Whitewolf
Whitewolf

Reputation: 186

Even Odd rows without using an outer query

SELECT
    a,
    b,
    c,
    ROW_NUMBER() OVER (ORDER BY a) ROWNUM,
    ROW_NUMBER() OVER (ORDER BY a) % 2 BOOL
FROM love
--WHERE BOOL = 1

I am trying to fetch the odd/even rows without using an outer query.

Using a generated column in the same query will not work, but is there a way to make it?

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Martin's answer looks like this:

SELECT TOP (1) WITH TIES a, b, c,
       ROW_NUMBER() OVER (ORDER BY a) as ROWNUM,
       ROW_NUMBER() OVER (ORDER BY a) % 2 as BOOL
FROM love
ORDER BY BOOL DESC;

This is very clever and I give full attribution to Martin.

I will note that for guaranteed correctness, this assumes that a is unique. Sort in SQL (and SQL Server) are not stable. So, if there are duplicate values, then the ordering could actuall be different for rownum and bool.

Upvotes: 2

Related Questions