I find your solution confusing.
Your first query
SELECT TOP(10) * FROM TABLE ORDER BY NEWID(); Just randomises the output and has nothing to do with selecting alternate records. Incidentally, you should avoid using reserved words (e.g. TABLE) as tablenames, but if you insist, then get into the habit of surrounding the reserved word with square brackets (i.e. [TABLE])
In your section 2 you have put Identity column in bold suggesting that it is someway relevant to identifying alternate records - it is not, as I stated earlier.
You then go on to mention "ROW_NUMBER() Rank function" - which one do you mean?
Your final comment I think, is saying that the Rank function has an option to order columns - all Window functions have the potential for ORDER BY and/or PARTITION BY, that's why they are sometimes referred to as OVER functions. You can use Window functions on any table, not just derived tables or CTEs.
Here is an example of why Identity Column is not appropriate: Consider this sample data
create table test (d varchar(10))
insert into test (d) values
('Test 1'), ('Test 2'), ('Test 3'), ('Test 4'),
('Test 5'), ('Test 6'), ('Test 7'), ('Test 8')
DELETE from test WHERE Id = 3The contents of the table are
Id d
1 Test 1
2 Test 2
4 Test 4
5 Test 5
6 Test 6
7 Test 7
8 Test 8 Note the missing Id 3.
So I would expect to return rows where Id = 1, 4, 6 and 8. But if I just use the Identity Column
SELECT * FROM test where id % 2 = 1 I only get rows where id = 1, 5 and 7. Incorrect.
An example where RANK is inappropriate. Consider the following test data
create table test2 (Id int, d varchar(10))
insert into test2 (id,d) values
(1,'Test 1'), (1,'Test 2'), (1,'Test 3'), (2,'Test 4'),
(2,'Test 5'), (2,'Test 6'), (3,'Test 7'), (3,'Test 8') The table contains the data
Id d
1 Test 1
1 Test 2
1 Test 3
2 Test 4
2 Test 5
2 Test 6
3 Test 7
3 Test 8 So I would expect to return the rows where d is Test... 1, 3, 5, 7.
If I try to use Rank like this
;with CTE AS
(
select *, ROW_NUMBER() OVER (ORDER BY d) as rn, RANK() OVER (ORDER BY d) as r
FROM Test2
)
SELECT * FROM CTE WHERE r % 2 = 1 I get the correct answer. But I could just have easily used
SELECT * FROM CTE WHERE rn % 2 = 1 as both RANK and ROW_NUMBER return the same value in this instance. I contend that using ROW_NUMBER is clearer and less prone to risk - what if someone changes it to use a PARTITION … RANK() OVER (PARTITION BY id ORDER BY d) as r … you're going to get the rows where Test is … 1, 3, 4, 6, 7. Incorrect again.
Even if partition is not used, RANK can fail depending on the data being returned. Try adding some more data to test2 e.g.
insert into test2 (id,d) values
(1,'Test 1'), (1,'Test 2'), (1,'Test 3'), (2,'Test 4') Note that test2 now contains duplicate rows so the query that forms the CTE
select *, ROW_NUMBER() OVER (ORDER BY d) as rn, RANK() OVER (ORDER BY d) as r
FROM Test2 returns the following values
Id d rn r
1 Test 1 1 1
1 Test 1 2 1
1 Test 2 3 3
1 Test 2 4 3
1 Test 3 5 5
1 Test 3 6 5
2 Test 4 7 7
2 Test 4 8 7
2 Test 5 9 9
2 Test 6 10 10
3 Test 7 11 11
3 Test 8 12 12 Expected results would be Test 1, Test 2, Test 3, Test 4, Test 5, Test 7 but if I re-run the CTE I actually get Test 1, 1, 2, 2, 3, 3, 4, 4, 5, and 7. Incorrect again.
However using ROW_NUMBER will always work, regardless of the data contents
;with CTE AS
(
select *, ROW_NUMBER() OVER (ORDER BY d) as rn
FROM Test2
)
SELECT * FROM CTE WHERE rn % 2 = 1
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
