Re: SELECT statement ranges
You do it like this:
Code:
SELECT TOP 20 ... FROM <table>
WHERE <keyColumn> NOT IN (SELECT TOP <rowsToSkip> <keyColumn> FROM
<table> ORDER BY <SortOrder>)
ORDER BY <SortOrder>
This will work for SQL Server at least.
Re: SELECT statement ranges
Re: SELECT statement ranges
Darwen, what database are you referring to? If that is Oracle then probably rowid and rownum would help you achieve that easily. Here is a sample from - here:
Code:
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum < =&rangeend
minus
select rowid from emp where rownum<&rangebegin);
But MS SQL Server or Sybase probably don't have this feature. If you are using ADO.Net then you might need to get all the records into a Dataset and then extract bunches from there. Let me know your comments and what you think. Regards.
Re: SELECT statement ranges
klintan's way will work but another way which is generally faster is:
Code:
SELECT ... FROM (
SELECT TOP <pageSize> ...
FROM (
SELECT TOP <rowsToSkip + pageSize> ...
FROM <tableName>
ORDER BY <sortField> <sortDirection>
) a
ORDER BY <sortField> <oppositeSortDirection>
) b
ORDER BY <sortField> <sortDirection>
It first pulls the top rowsToSkip + pageSize rows, then sorts them in reverse order and takes the top pageSize rows, thus yielding all the records for the desired page. The outermost SELECT just resorts the page into the desired order.
Re: SELECT statement ranges
Thanks all for those. As you can guess, I'm pretty new to SQL databasing. Not new to everything else of course, just to databasing.
Glad to know that the forums are here to help. MSDN is pitifully poor on databasing from what I can see.
What are the 'a' and 'b's for CMiscow ? Excuse my ignorance but I've never seen this in SQL.
Darwen.
Re: SELECT statement ranges
Quote:
Originally Posted by darwen
What are the 'a' and 'b's for CMiscow ? Excuse my ignorance but I've never seen this in SQL.
They are probably aliases. I have used them with columns and tables but they could be used with resultsets of a Select sub-query is the first time I have seen (may be I never faced a need for them). Regards.
1. Aliases
2. Alias for sub-query resultset
Re: SELECT statement ranges
Yeah, they are aliases for the sub-queries. I'm pretty sure you need to give subqueries aliases when selecting from them but not when they are part of the where clause. Sorry, I should have given them meaningful names.
Re: SELECT statement ranges
Hello CMiskow,
I think the query that you have suggested here will not work.Because the inner query is arranging in the asc order,whereas the outer query to that is arranging in the reverse order.It will nor work.Sorry to hurt you by means of saying this .
Re: SELECT statement ranges
Actually it does work. Imagine you have a table t_Letters:
letter
a
b
c
d
e
Imagine we want to sort them in ascending order and skip two rows and get the next two rows. We expect to return c, d.
Here is the innermost SELECT:
Code:
SELECT letter FROM
( SELECT TOP 2 letter FROM
( SELECT TOP 4 letter
FROM t_Letters
ORDER BY letter ASC
) a
ORDER BY letter DESC
) b
ORDER BY letter ASC
The innermost SELECT gives us:
a
b
c
d
Then the next SELECT operates on that set and gives:
d
c
Then the outermost re-sorts them:
c
d
Re: SELECT statement ranges
I'm new to this, so bear with me but it strikes me we can do this with only 2 select statements e.g.
Letter
a
b
c
d
e
f
We need d,e
We can select top 5, in reverse order which gives us
e
d
c
b
a
Then we can select top 2, in ascending order which gives us
d
e
i.e.
Code:
SELECT TOP 2 letter FROM
(
SELECT TOP 5 FROM t_Letters
ORDER BY letter DESC
) a
ORDER BY Letter ASC
However, if we're talking about 'letter' being the primary key for the table I suppose we need an additional 'SELECT' around the outside to draw out the necessary fields.
Am I right ?
And does this work with Jet databases ? I've got no reason to believe it won't, but it's good to check.
Thanks,
Darwen.
P.S. I'm glad this has turned into such a good thread. Thanks for all the input. It's been really great help.
Re: SELECT statement ranges
Not exactly...
say you have
a
b
c
d
e
f
The first part of the query that's run:
Code:
SELECT top 5 letter from t_letters ORDER BY letter DESC
Gives you
f
e
d
c
b
So running that through
Code:
SELECT top 2 letter from (SELECT top 5 letter from t_letters ORDER BY letter DESC) a ORDER BY letter ASC
gives you
b
c
In regard to JET - most standard SQL should work, but any of the cool T-SQL functions (inline case, string manipulation, etc) won't work.
Re: SELECT statement ranges
Thanks, I understand now.
Darwen.