Select Alternate number of rows

Question

Post reply

Select Alternate number of rows

satishkmr538

SSC Veteran

Points: 258
More actions
January 28, 2015 at 3:29 am

#314826

A simple Query to select alternate rows from a table ?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

satishkmr538 (1/28/2015)
A simple Query to select alternate rows from a table ?

Use ROW_NUMBER() and select either even or odd numbers.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bhushan Kulkarni SSCrazy Points: 2829 More actions · Answer 2

Check this sample query without using % or mod

USE [AdventureWorks2008]

GO

SELECT P.*

FROM

(SELECT *,ROW_NUMBER() OVER(Order by BusinessEntityId) AS RID FROM Person.Person )P

INNER JOIN (SELECT ROW_NUMBER() OVER(Order by BusinessEntityId) * 2 As Rid from Person.Person) P2

ON P.RID = P2.RId

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Bhushan Kulkarni (1/28/2015)
Check this sample query without using % or mod
USE [AdventureWorks2008]
GO
SELECT P.*
FROM
(SELECT *,ROW_NUMBER() OVER(Order by BusinessEntityId) AS RID FROM Person.Person )P
INNER JOIN (SELECT ROW_NUMBER() OVER(Order by BusinessEntityId) * 2 As Rid from Person.Person) P2
ON P.RID = P2.RId

You may wish to consider warning the OP if you are posting a deliberately expensive and convoluted query "just for laughs", it isn't always as obvious as it may seem. It's good, though. I think it would be tricky to make it any more expensive without adding considerably to the code.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bhushan Kulkarni SSCrazy Points: 2829 More actions · Answer 4

Optimized one..

USE [AdventureWorks2008]

GO

SELECT P.*

FROM

Person.Person P

WHERE

P.BusinessEntityId & 1 = 0

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Bhushan Kulkarni (1/28/2015)
Optimized one..
USE [AdventureWorks2008]
GO
SELECT P.*
FROM
Person.Person P
WHERE
P.BusinessEntityId & 1 = 0

Almost...

SELECT n, n%1, n%2

FROM (VALUES (1),(2),(3),(4),(5)) d (n)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Bhushan Kulkarni (1/28/2015)
Optimized one..
USE [AdventureWorks2008]
GO
SELECT P.*
FROM
Person.Person P
WHERE
P.BusinessEntityId & 1 = 0

That doesn't return alternate rows, it returns rows with ~~odd~~ even values of BusinessEntityID. There's a fairly large difference.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

GilaMonster (1/28/2015)
Bhushan Kulkarni (1/28/2015)
Optimized one..
USE [AdventureWorks2008]
GO
SELECT P.*
FROM
Person.Person P
WHERE
P.BusinessEntityId & 1 = 0
That doesn't return alternate rows, it returns rows with odd values of BusinessEntityID. There's a fairly large difference.

It returns every row:

SELECT

n,

[AllZero] = n%1,

[(n-1)%2] = (n-1)%2,

[(n-1)%3] = (n-1)%3,

[(n-1)/2] = (n-1)/2,

[(n-1)/3] = (n-1)/3

FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8)) d (n)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 8

ChrisM@Work (1/28/2015)
GilaMonster (1/28/2015)
Bhushan Kulkarni (1/28/2015)
Optimized one..
USE [AdventureWorks2008]
GO
SELECT P.*
FROM
Person.Person P
WHERE
P.BusinessEntityId & 1 = 0
That doesn't return alternate rows, it returns rows with odd values of BusinessEntityID. There's a fairly large difference.
It returns every row:
SELECT
n,
[AllZero] = n%1,
[(n-1)%2] = (n-1)%2,
[(n-1)%3] = (n-1)%3,
[(n-1)/2] = (n-1)/2,
[(n-1)/3] = (n-1)/3
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8)) d (n)

It's a bitwise-and not modulo

SELECT

n,

[BitwiseAnd] = n&1, --***

[AllZero] = n%1,

[(n-1)%2] = (n-1)%2,

[(n-1)%3] = (n-1)%3,

[(n-1)/2] = (n-1)/2,

[(n-1)/3] = (n-1)/3

FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8)) d (n)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Mark Cowne (1/28/2015)
ChrisM@Work (1/28/2015)
GilaMonster (1/28/2015)
Bhushan Kulkarni (1/28/2015)
Optimized one..
USE [AdventureWorks2008]
GO
SELECT P.*
FROM
Person.Person P
WHERE
P.BusinessEntityId & 1 = 0
That doesn't return alternate rows, it returns rows with odd values of BusinessEntityID. There's a fairly large difference.
It returns every row:
SELECT
n,
[AllZero] = n%1,
[(n-1)%2] = (n-1)%2,
[(n-1)%3] = (n-1)%3,
[(n-1)/2] = (n-1)/2,
[(n-1)/3] = (n-1)/3
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8)) d (n)
It's a bitwise-and not modulo
SELECT
n,
[BitwiseAnd] = n&1, --***
[AllZero] = n%1,
[(n-1)%2] = (n-1)%2,
[(n-1)%3] = (n-1)%3,
[(n-1)/2] = (n-1)/2,
[(n-1)/3] = (n-1)/3
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8)) d (n)

D'oh! Thanks Mark.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

ChrisM@Work (1/28/2015)
It returns every row:
SELECT
n,
[AllZero] = n%1,
[(n-1)%2] = (n-1)%2,
[(n-1)%3] = (n-1)%3,
[(n-1)/2] = (n-1)/2,
[(n-1)/3] = (n-1)/3
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8)) d (n)

No, it doesn't. It's a Bitwise AND, true only when the ID is even (not odd as I previously said)

SELECT

n,

n & 1

FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8)) d (n)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dwain Camps SSC Guru Points: 86948 More actions · Answer 11

Bhushan Kulkarni (1/28/2015)
Optimized one..
USE [AdventureWorks2008]
GO
SELECT P.*
FROM
Person.Person P
WHERE
P.BusinessEntityId & 1 = 0

Interesting but it is not guaranteed to return alternate rows if there are gaps in BusinessEntityID.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St