Getting records from 10 to 100

Hi NGs,
I am newbie in SQL, I have a question.
I'd like to query a table, fetch the records met conditions from 10th to
100th, which sql statement can I use?
select *** distinct 10 top 100 orderby ...;
or
SELECT WHERE ... BETWEEN 10 AND 100 ORDER BY ...John
1)
select col1 from
(select top 10 col1
from
(select top 100 col1 from #w order by col1 ASc)AS F
order by col1 DESC) as t1
order by col1 asc
2)
select * from #w where (select count(*) from #w t where t.col1<#w.col1)
between 10 and 100
"John" <spam@.spam.com> wrote in message
news:eDlDQDW6DHA.2168@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Hi NGs,
>
> I am newbie in SQL, I have a question.
>
> I'd like to query a table, fetch the records met conditions from 10th to
> 100th, which sql statement can I use?
>
> select *** distinct 10 top 100 orderby ...;
>
> or
>
> SELECT WHERE ... BETWEEN 10 AND 100 ORDER BY ...
>
>
>

---

|||Hi, I really appreciated your answer, however, it is little complex to me, I
am sorry, could someone explain it in a little more details? Thank you very
much
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:es9yQYW6DHA.2432@.TK2MSFTNGP10.phx.gbl...

quote:

---

> John
> 1)
> select col1 from
> (select top 10 col1
> from
> (select top 100 col1 from #w order by col1 ASc)AS F
> order by col1 DESC) as t1
> order by col1 asc
> 2)
> select * from #w where (select count(*) from #w t where t.col1<#w.col1)
> between 10 and 100
>
>
> "John" <spam@.spam.com> wrote in message
> news:eDlDQDW6DHA.2168@.TK2MSFTNGP12.phx.gbl...
>

---

|||Hello John,
Thanks for your post. If I understand correctly that you want to fetch the
records from 10th to 100th. I have the following information for
supplement. The following sql statements work fine on sample database
Northwind.
1. The first method should be:
select * from
(select top 91 * from
(select top 100 * from ORDERS order by ORDERID ASC)
AS F order by ORDERID DESC) as t1
order by ORDERID ASC
These sql statements perform the following steps to fetch the required data.
a. Fetch the top 100 records by ascending.
b. Order the 100 records by descending and fetch the top 91 records.
c. Order the 91 records by ascending.
2. The Second method should be:
Select * from ORDERS where (select count(*) from ORDERS t where t.ORDERID
<ORDERS.ORDERID)
between 9 and 99
This sql statements (Select count(*) from ORDERS t where t.ORDERID
<ORDERS.ORDERID) got the number of the records which are smaller than the
current record.
I hope the explanation is clear. Please post in the group if you need
further assistance on this issue.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||This makes sense for small recordsets...in your orders example there are
only 830 rows.
My question would be, for larger recordsets, let's say in the range of
20,000 to 30,000, where you want to return only 100 rows at a time from
within that matching set...what would be the proper technique?
The method outlined here would surely be much too expensive.
Thanks,
Mike
""Yuan Shao"" <v-yshao@.online.microsoft.com> wrote in message
news:aQkvX%23Y6DHA.568@.cpmsftngxa07.phx.gbl...

quote:

---

> Hello John,
> Thanks for your post. If I understand correctly that you want to fetch the
> records from 10th to 100th. I have the following information for
> supplement. The following sql statements work fine on sample database
> Northwind.
> 1. The first method should be:
> select * from
> (select top 91 * from
> (select top 100 * from ORDERS order by ORDERID ASC)
> AS F order by ORDERID DESC) as t1
> order by ORDERID ASC
> These sql statements perform the following steps to fetch the required

---

data.

quote:

---

> a. Fetch the top 100 records by ascending.
> b. Order the 100 records by descending and fetch the top 91 records.
> c. Order the 91 records by ascending.
> 2. The Second method should be:
> Select * from ORDERS where (select count(*) from ORDERS t where t.ORDERID
> <ORDERS.ORDERID)
> between 9 and 99
> This sql statements (Select count(*) from ORDERS t where t.ORDERID
> <ORDERS.ORDERID) got the number of the records which are smaller than the
> current record.
> I hope the explanation is clear. Please post in the group if you need
> further assistance on this issue.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>

---

|||Hello Mike,
Based on my test, the first method works fine in the large record sets (at
least for 30,000 to 40,000) on my side. This method is more beneficial to
be used in large record sets than the second one. The time it takes is
acceptable.
Select * from
(Select top 91 * from
(Select top 100 * from <Table Name> order by <Column
Name> ASC) AS F order by <Column Name> DESC) as t1
order by <Column Name> ASC
Thanks for posting in community.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.