retrieve records in an interval

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

retrieve records in an interval

Author

Topic

laailalalaa
Yak Posting Veteran

57 Posts

Posted - 2010-04-26 : 17:37:52

if i have table T(id,number) how can i retrieve records from position 3 to 6 let's say, ordered by number?

thank u

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 18:04:40

Don't your class materials cover this?

SELECT id, number
FROM T
WHERE id >= 3 AND id <= 6
ORDER BY number

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-26 : 18:10:59

Hi Tara,
What is the difference between "BETWEEN" and ">= AND <=" in performance?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-26 : 18:12:18

[code]SELECT ID, Number
FROM ( SELECT TOP 6 ID, Number, ROW_NUMBER() OVER (ORDER BY NUMBER) AS Row
FROM T ) z
WHERE Row >= 3[/code]

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-26 : 18:14:18

quote:
Originally posted by tkizer

This is seriously too easy. Don't your class materials cover this?

SELECT id, number
FROM T
WHERE id >= 3 AND id <= 6
ORDER BY number

Who said that ID contains 1, 2, 3, 4, 5, 6...?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-26 : 18:15:56

quote:
Originally posted by ms65g

Hi Tara,
What is the difference between "BETWEEN" and ">= AND <=" in performance?

I'd be surprised if there were any difference. Under the hood, I'd expect SQL Server to implement the 2 in the same way.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 18:16:08

quote:
Originally posted by DBA in the making

quote:
Originally posted by tkizer

Don't your class materials cover this?

SELECT id, number
FROM T
WHERE id >= 3 AND id <= 6
ORDER BY number

Who said that ID contains 1, 2, 3, 4, 5, 6...?

Well it wasn't said either way, so it's up to interpretation. I interpreted "position" differently than you apparently. I thought OP was meaning id.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 18:16:27

quote:
Originally posted by ms65g

Hi Tara,
What is the difference between "BETWEEN" and ">= AND <=" in performance?

They are equivalent.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

laailalalaa
Yak Posting Veteran

57 Posts

Posted - 2010-04-27 : 04:20:40

that's it, i didn't know about row_number() and was using rank.
thanks :)

quote:
Originally posted by DBA in the making

SELECT ID, Number
FROM ( SELECT TOP 6 ID, Number, ROW_NUMBER() OVER (ORDER BY NUMBER) AS Row
FROM T ) z
WHERE Row >= 3

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 04:25:12

quote:
Originally posted by laailalalaa

that's it, i didn't know about row_number() and was using rank.
thanks :)

quote:
Originally posted by DBA in the making

SELECT ID, Number
FROM ( SELECT TOP 6 ID, Number, ROW_NUMBER() OVER (ORDER BY NUMBER) AS Row
FROM T ) z
WHERE Row >= 3

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

If number contains unique values then rank() will also work

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

laailalalaa
Yak Posting Veteran

57 Posts

Posted - 2010-04-27 : 04:50:07

i know it would work, but number doesn't contain unique values (i simplified the table schema, the table i'm working with is more complicated).
thanks for your help

quote:
Originally posted by visakh16

quote:
Originally posted by laailalalaa

that's it, i didn't know about row_number() and was using rank.
thanks :)

quote:
Originally posted by DBA in the making

SELECT ID, Number
FROM ( SELECT TOP 6 ID, Number, ROW_NUMBER() OVER (ORDER BY NUMBER) AS Row
FROM T ) z
WHERE Row >= 3

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

If number contains unique values then rank() will also work

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources