Newbie SQL range question.... - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Newbie SQL range question....

Author

Topic

bershi95
Starting Member

5 Posts

Posted - 2003-10-27 : 17:05:17

Using the following select statement, a list of the top 100 records is returned:
SELECT TOP 1000 *
FROM EMP

But suppose I want a list of those records that fall between 300 and 700...how do I write that query?

I'm fairly new, so please be patient.
Thanks!
Nardo

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-10-27 : 17:13:59

Give this a read. If this doesn't help, reply to this post.

http://www.sqlteam.com/item.asp?ItemID=566

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

bershi95
Starting Member

5 Posts

Posted - 2003-10-27 : 17:27:30

Hi Michael,
Thanks for the article, but it really doesn't address the problem, though the question raised in the article is exactly the same as mine.
I need to know how to return a range of records, perhaps within the top 1000.
Thanks again!
Nardo

quote:
Originally posted by MichaelP

Give this a read. If this doesn't help, reply to this post.

http://www.sqlteam.com/item.asp?ItemID=566

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-10-27 : 18:17:03

Nardo,

First, your query will need to have an ORDER BY clause attached to it, or else there is no guarantee that when you run it the next time, the next week, or the next month that it would return the same set of records. There is no meaningful physical ordering of the data unless a clustered index has been declared.

Second, using the example from the article, wouldn't something like
SELECT TOP 400 *
FROM EMP
WHERE YourPrimaryKey in (SELECT TOP 700 YourPrimaryKey
FROM EMP
ORDER BY YourSortField DESCENDING)
ORDER BY YourSortField ASCENDING

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-28 : 12:13:59

quote:
Originally posted by bershi95

suppose I want a list of those records that fall between 300 and 700...how do I write that query?

It's the middle of the pack thing....

You can't.

Unless there is some meta data stored with the data (an add timestamp or something), it's an arbitrary thing. You need to be able to order by something.

And TOP can get you the BOTTOM when your order by descending.

Do you have something like that?

I imagine you could do something like:



USE Northwind
GO

  SELECT * FROM Orders o1
   WHERE OrderId NOT IN (SELECT TOP 100 OrderId FROM Orders ORDER BY OrderId)
     AND  OrderId NOT IN (SELECT TOP 100 OrderId FROM Orders ORDER BY OrderId DESC)
ORDER BY OrderId
GO

And go nuts with dynamic...but for what purpose?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources