Click here to Skip to main content
14,642,933 members
Home » Discussions » Database
   

Database

 
QuestionWhat is an alias? Pin
Shraddha_Patel7-May-19 18:20
MemberShraddha_Patel7-May-19 18:20 
AnswerRe: What is an alias? Pin
Richard MacCutchan7-May-19 21:23
mveRichard MacCutchan7-May-19 21:23 
AnswerRe: What is an alias? Pin
#realJSOP28-May-19 1:52
mva#realJSOP28-May-19 1:52 
QuestionHow can you get the alternate records from the table in the SQL? Pin
Shraddha_Patel30-Apr-19 22:53
MemberShraddha_Patel30-Apr-19 22:53 
AnswerRe: How can you get the alternate records from the table in the SQL? Pin
Richard MacCutchan30-Apr-19 23:07
mveRichard MacCutchan30-Apr-19 23:07 
AnswerRe: How can you get the alternate records from the table in the SQL? Pin
CHill601-May-19 5:05
mveCHill601-May-19 5:05 
AnswerRe: How can you get the alternate records from the table in the SQL? Pin
Santosh kumar Pithani4-May-19 2:31
professionalSantosh kumar Pithani4-May-19 2:31 
GeneralRe: How can you get the alternate records from the table in the SQL? Pin
CHill6014-May-19 2:30
mveCHill6014-May-19 2:30 
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 = 3
The 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

SuggestionRe: How can you get the alternate records from the table in the SQL? Pin
Richard Deeming14-May-19 2:38
mveRichard Deeming14-May-19 2:38 
GeneralRe: How can you get the alternate records from the table in the SQL? Pin
CHill6015-May-19 2:36
mveCHill6015-May-19 2:36 
JokeRe: How can you get the alternate records from the table in the SQL? Pin
Richard Deeming15-May-19 6:51
mveRichard Deeming15-May-19 6:51 
GeneralRe: How can you get the alternate records from the table in the SQL? Pin
Santosh kumar Pithani15-May-19 20:43
professionalSantosh kumar Pithani15-May-19 20:43 
QuestionA strange issue!The count of queries does not match data. Pin
Member 1409322316-Apr-19 23:17
MemberMember 1409322316-Apr-19 23:17 
AnswerRe: A strange issue!The count of queries does not match data. Pin
Maciej Los16-Apr-19 23:34
mveMaciej Los16-Apr-19 23:34 
SuggestionRe: A strange issue!The count of queries does not match data. Pin
Richard Deeming17-Apr-19 1:16
mveRichard Deeming17-Apr-19 1:16 
GeneralRe: A strange issue!The count of queries does not match data. Pin
Member 1409322317-Apr-19 15:27
MemberMember 1409322317-Apr-19 15:27 
GeneralFavour sought from a MySql expert Pin
Ger Hayden16-Apr-19 21:50
MemberGer Hayden16-Apr-19 21:50 
GeneralRe: Favour sought from a MySql expert PinPopular
Richard MacCutchan16-Apr-19 22:21
mveRichard MacCutchan16-Apr-19 22:21 
GeneralRe: Favour sought from a MySql expert Pin
Ger Hayden16-Apr-19 22:34
MemberGer Hayden16-Apr-19 22:34 
GeneralRe: Favour sought from a MySql expert Pin
Richard MacCutchan16-Apr-19 22:48
mveRichard MacCutchan16-Apr-19 22:48 
QuestionSQL to retrieve record count for each column of a table with blank, not null, null and distinct count in oracle sql Pin
Member 1422994312-Apr-19 22:35
MemberMember 1422994312-Apr-19 22:35 
AnswerRe: SQL to retrieve record count for each column of a table with blank, not null, null and distinct count in oracle sql Pin
Richard MacCutchan12-Apr-19 23:26
mveRichard MacCutchan12-Apr-19 23:26 
GeneralRe: SQL to retrieve record count for each column of a table with blank, not null, null and distinct count in oracle sql Pin
Member 1422994313-Apr-19 0:40
MemberMember 1422994313-Apr-19 0:40 
GeneralRe: SQL to retrieve record count for each column of a table with blank, not null, null and distinct count in oracle sql Pin
Richard MacCutchan13-Apr-19 1:07
mveRichard MacCutchan13-Apr-19 1:07 
QuestionLinkedServer version Pin
Darrylw9912-Apr-19 4:59
MemberDarrylw9912-Apr-19 4:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.