Select Data from same table multiple times

[Cathy]

I have a table something like this

ID, OFFID
1, 2
2, 2
3, 3
4, 2
5, 3
6, 2
7, 3
8, 4

I need to run a select query that returns
1. All unique fields in OFFID
2, the matching ID as ID1
3, the ID before ID1 matching OFFID, as ID2
4, the ID before ID2 matching OFFID, as ID3

The result should look something like this

OFFID, ID1, ID2, ID3
2, 6, 4, 2
3, 7, 5, 3
4, 8, ,

Where no value is returned, value should be blank

Any assistance in formulating this query will be appreciated

Thanks in advance

C

[Erland Sommarskog]

Post by Cathy
I have a table something like this
ID, OFFID
1, 2
2, 2
3, 3
4, 2
5, 3
6, 2
7, 3
8, 4
I need to run a select query that returns
1. All unique fields in OFFID
2, the matching ID as ID1
3, the ID before ID1 matching OFFID, as ID2
4, the ID before ID2 matching OFFID, as ID3
The result should look something like this
OFFID, ID1, ID2, ID3
2, 6, 4, 2
3, 7, 5, 3
4, 8, ,

My interpretation is that for each OFFID, you want the three highest
ids in descening order. This can be solved by first numbering the rows
on descening order by ID and then pivot:

WITH numbering AS (
SELECT OFFID, ID,
row_number() OVER (PARTITION BY OFFID ORDER BY ID DESC) AS rowno
FROM tbl
)
SELECT OFFID,
ID1 = MIN(CASE WHEN rowno = 1 THEN ID END),
ID2 = MIN(CASE WHEN rowno = 2 THEN ID END),
ID3 = MIN(CASE WHEN rowno = 3 THEN ID END)
FROM numbering
GROUP BY OFFID