user1934212 - 2 months ago 15

SQL Question

I have a result table

`id | name | wins`

----+-------------------

57 | Paul | 10

64 | Sven | 9

62 | Peter | 9

59 | Marina | 8

58 | Carlos | 4

60 | Pamela | 3

61 | Marcus | 2

63 | Hank | 1

Where I want to pair every nth entry with every (n+1)th entry, such that the resulting table looks like that:

`id | name | id | name`

----+-------------------

57 | Paul | 64 | Sven

62 | Peter | 59 | Marina

58 | Carlos | 60 | Pamela

61 | Marcus | 63 | Hank

Which SQL statement would achieve that?

Answer

```
;WITH cte AS (
SELECT *,ROW_NUMBER() OVER (ORDER BY Wins DESC) as RowNum
FROM
@Table
)
SELECT *
FROM
cte c1
LEFT JOIN cte c2
ON c1.RowNum + 1 = c2.RowNum
WHERE
c1.RowNum % 2 <> 0
```

Generate a ROW_NUMBER to use, seeing you have a third Column replace (SELECT NULL) in the Order by statement with that third column.

Then select all rows that are Odd Row numbers `(remainder of RowNum divided by 2 <> 0 )`

and self join back to itself with `RowNum + 1`

. If you have an odd number of Rows you might consider using `LEFT JOIN`

so you don't drop off the 1 row that won't have a match.