Trying to get the 11th - 20th record

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

SQL Server Development (2000)

Trying to get the 11th - 20th record

Author

Topic

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-10 : 08:26:51

Hello,

I've got a table of countries (called cia). I've written a query that will return the 11th through the 20th rows, and I'm wondering if there is a better/easier/more correct way to do it.

--=================================================================
-- Extract the top 20 rows
-- Extract the bottom 10 of those (records 11 - 20)
-- Join to the main table again and reverse the sort order.
-- The outermost join returns the 10 rows in ascending order
--=================================================================


SELECT d.name
FROM cia d join 
  (SELECT top 10 a.name
   FROM cia a join 
	(SELECT top 20 name
	 FROM cia
	 ORDER BY name) b  on (a.name = b.name)
   ORDER BY a.name desc) c  on (d.name = c.name)
ORDER BY d.name asc

Seems like a convoluted way to do it, even though it works.

Thanks,
Kevin

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-10 : 09:06:12

Most of the Stored procedure solutions use a temp table to number the rows so a set of specific rows can be returned. See [url]http://www.aspfaqs.com/webtech/062899-1.shtml[/url] for an example

Sam

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-10 : 09:09:22

I could have used a temp table, but that seemed like cheating....

I'm trying to exercise my SQL joining skills !

Amused

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-10 : 09:10:59

Try this:


USE pubs

SELECT TOP 10 au_lname from authors 
WHERE au_lname > 
ALL(SELECT TOP 10 au_lname FROM authors 
ORDER BY au_lname)
ORDER BY au_lname

I think this will get slower as the number of results in the inner query increases.

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-10 : 09:16:39

That's pretty slick.

Thanks,
Kevin

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-10 : 09:40:59

Why do you feel temp tables are cheating?

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-10 : 09:44:33

The article generalizes into a SELECT rows N to N+M. Unfortunately, none of the solutions in this thread can be parameterized into a general solution.

Here's another fixed solution

SELECT TOP 10 au_lname
FROM authors
WHERE au_lname NOT IN (SELECT TOP 10 au_lname FROM authors ORDER BY au_lname)
order by au_lname

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-10 : 09:54:43

Jeff's got a solution here too:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25496

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-10 : 14:22:12

Rob,

I didn't mean that literally

I just wanted to see if I could solve it with joins.

Thanks guys,
Kevin

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-10 : 15:06:42

This post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27297

might help explain my method a little clearer .... give it a shot!

- Jeff

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-10 : 16:29:28

Thanks Jeff,

All the paging I've done in the past required the web app to receive the complete record set, and then use it's own internal row numbering to consistently page through the results.

Downside of course was having to move all that data, and store it in memory (potentially multiplied by the number of sessions).

I'm gonna bet that many many web app designers don't seriously take into consideration the network congestion created when they decide to handle all the paging processes within their own application.

I'll take the time to really digest your method, it sounds like the best use of resources.

Thanks,
Kevin

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-08-11 : 01:44:17

LIfe would be a lot easier if Microsoft including the rownum pseudo-column in the resultset (like our Oracle friends do).

That way you could do something like this:


SELECT au_lname
FROM   (SELECT rownum as rownum2, au_lname
        FROM authors
        ORDER BY au_lname)
WHERE  rownum2 > 10
       AND rownum2 =< 20

-ec

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-11 : 01:56:07

Encapsualting the underlying physical layout was one of the most important breakthroughs that the relational model gave us.

What's a pseudo-column? What type is it? How is it generated? Is it sequential? Is it tied to the base tables? Is it generated for every query? What's its position in the column set? Can it have duplicates?

Personally, I think all they need to do is extend the IDENTITY function to be able to be used outside the INTO syntax.

DavidM

"SQL-3 is an abomination.."

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-08-11 : 02:23:29

quote:

What's a pseudo-column? What type is it? How is it generated? Is it sequential? Is it tied to the base tables? Is it generated for every query? What's its position in the column set? Can it have duplicates?

Oracle calls rownum a pseudocolumn. You do not define it, but it is there in every resultset.

It is an integer datatype

It is generated automatically, every select statement has a column called rownum (although it is not displayed unless explicitly selected)

It is sequential, there are no gaps in the sequence

It is generated for every query, it is not tied to the underlying tables or data.

Not sure what position it is in the column set, but that doesn't really matter does it?

It cannot have duplicates, it is unique.

rownum is just like having an identity column returned with every resultset. Well sort of, I actually made a mistake in my code above (I corrected it). By the nature of how oracle assigns the rownum value, it can only be used with the 'less than' or 'less than or equal' operator unless you alias the pseudocolumn in the inner select. This limits rownums usefulness to a degree - especially if you are not using a subquery.

I'd like to see an improved implementation of rownum (working with all comparison operators) included in the SQL Server yukon release, but they have probably frozen the feature set by now.

-ec

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-11 : 02:44:33

Is it part of the result set schema? ie If I issue the SET FMTONLY flag will it show up?
Column order should not be important, but this is SQL and has serious implications for the result set schema in dynamic systems.

I can actually see why they would call it a "psuedocolumn".. It is only there when you look at.. On second thought, they should have called it the "anti-Schr¨odinger Cat column" :-)

I really didn't expect anyone to answer those questions, it was simply to show that rownum like constructs really don't give much value except for the paging problem, yet raise all sorts of questions and issues (Well answered though!)..

If MS go down the path of extending the IDENTITY function then at least you would have more control... You would be able to set the DataType, interval, start number etc....

DavidM

"SQL-3 is an abomination.."

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-11 : 02:57:25

quote:
David:
Personally, I think all they need to do is extend the IDENTITY function to be able to be used outside the INTO syntax

I SECOND THAT!! Whether we like it or not, paging has become a way of life now, and its about time it got a little easier. (now I'm gonna get blasted for making it easier for morons to do this

). How wonderful it would be to say

SELECT IDENTITY(smallint, 1, 1) AS RowNumber, Blah, Blah
FROM foo JOIN bar
ON blah = blah

sqlwish@microsoft.com is that it?

Owais

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-08-11 : 06:44:29

quote:

If MS go down the path of extending the IDENTITY function then at least you would have more control... You would be able to set the DataType, interval, start number etc....

So, what you really want is a SQL Server implementation of an Oracle sequence ;)

-ec

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-11 : 07:47:28

As far as solving the paging problem goes, yes, I would like that functionality.
But definately not the implemented the way Oracle does.

DavidM

"SQL-3 is an abomination.."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-11 : 07:58:54

I can say that Yukon does have some features that will make this easier. And no, the feature set is not entirely frozen at this time.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-08-11 : 08:53:45

mySQL has a nice LIMIT clause which seems to made for paging...

SELECT * FROM mytable LIMIT 5,10

Gives rows 6-15. 5 is the offset and 10 is the length. Quite nice I think.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources