How can you get the alternate records from the table in the SQL?
-
How can you get the alternate records from the table in the SQL?
-
How can you get the alternate records from the table in the SQL?
-
How can you get the alternate records from the table in the SQL?
You need to be able to determine how to identify "alternate" records - if you have a numeric identity column it is NOT sufficient to say that you just want to extract the even number records. I would probably assign each row a number using ROW_NUMBER[^] with an appropriate ORDER BY[^] clause. You could put those results into a Temporary table or a sub-query (references Subqueries (SQL Server) - SQL Server | Microsoft Docs[^] , SQL Server CTE vs Temp Table vs Table Variable Performance Test[^] ) but I personally prefer to use a Common Table Expression (CTE) (Introduction to Common Table Expressions (CTE's) - Essential SQL[^]) You then select alternate records from those results using Modulus[^] i.e. modulus 2 of row_number = 0 If you are still stuck after reading this documentation and attempting this approach, then share the code you are having problems with and we can try to help further
-
How can you get the alternate records from the table in the SQL?
1.If your looking random records from the table every time then use "order by NEWID()" with top clause. EX:- SELECT TOP(10) * FROM TABLE ORDER BY NEWID(); 2.Alternate records is identified with Identity column AND ROW_NUMBER() Rank function in advance Rank function has choice to sorting based on columns under "CTE" and derived table.
-
1.If your looking random records from the table every time then use "order by NEWID()" with top clause. EX:- SELECT TOP(10) * FROM TABLE ORDER BY NEWID(); 2.Alternate records is identified with Identity column AND ROW_NUMBER() Rank function in advance Rank function has choice to sorting based on columns under "CTE" and derived table.
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 8Note 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 8So 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 = 1I 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
-
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 8Note 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 8So 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 = 1I 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
CHill60 wrote:
You then go on to mention "ROW_NUMBER() Rank function" - which one do you mean?
ROW_NUMBERis a ranking function; I suspect that's what Santosh meant. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
CHill60 wrote:
You then go on to mention "ROW_NUMBER() Rank function" - which one do you mean?
ROW_NUMBERis a ranking function; I suspect that's what Santosh meant. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I was trying to make him aware that his post was as clear as mud. I'd just had a session with a user group from whom I'm trying to get some requirements. They (the group and the requirements!) are woollier than a woolly mammoth. I wasn't in the best of moods. :sigh:
-
I was trying to make him aware that his post was as clear as mud. I'd just had a session with a user group from whom I'm trying to get some requirements. They (the group and the requirements!) are woollier than a woolly mammoth. I wasn't in the best of moods. :sigh:
You mean there can be requirements that aren't that woolly? :omg:
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
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 8Note 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 8So 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 = 1I 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
We cannot share same solution..this site for sharing our knowledge and experience to find best and alternate solution to reach OP's expectations.I always try to understand others i hope u too :-)
