how to populate certain range of records?

[seahorse123]

Suppose I have 1000 records in MSSQL DB.
how can I retrieve the records from 300 to 400, in Mysql I can say:
select * from table limit 300, 100

but how to do that in MS SQL server?

Thanks.

 

[amrita418]

There is no such thing as 300th record in the database.

What is your definition of 100 records you need?

One way to get set your criteria is using the order by clause.

select      top 100 * 
from        (select    top 400 * 
             from      tableA 
             order by  colA) b
order by    b.colA desc

Regards,
AA

 

[seahorse123]

Thanks.

But this will sort the result (colA) in desc, not asc.

 

[amrita418]

I was just giving you an example. Post your original requirement and we can help you with it.

You can add an outer select stmt with order by clause colA to order those 100 records in the asc order.

Regards,
AA

 

[r937]

select foo
     , bar
  from yourtable X 
 where ( select count(*) 
           from yourtable  
          where foo > X.foo ) 
       between 300 and 400[/code   

[url=http://r937.com/]r937.com[/url] | [url=http://rudy.ca/]rudy.ca[/url]

 

[seahorse123]

amrita418,

For example I have the statement:

SELECT TOP 100 partno, parttype, partdescription, rev, datelastupdated 
FROM
(SELECT TOP 400 partno, parttype, partdescription, rev, datelastupdated 
FROM IntraParts
order by partno) b
order by b.partno desc

The result shown in desc order, how to add the out stmt?

thanks.

 

[amrita418]

Try this:

select     * 
from       (SELECT TOP 100 partno, 
                           parttype, 
                           partdescription, 
                           rev, 
                           datelastupdated 
            FROM           (SELECT TOP 400 
                                   partno,
                                   parttype,
                                   partdescription,
                                   rev, 
                                   datelastupdated 
                            FROM   IntraParts
                            order by partno) b
            order by b.partno desc) c
order by    c.partno

If the table is huge look into incorporating the code that r937 suggested.
Regards,
AA

 

[seahorse123]

Is there an easy way to populate ranged records? like in Mysql, I can easily display records from 300 to 400 by using:

SELECT partno,parttype,partdescription,rev, datelastupdated 
FROM   IntraParts
ORDER BY partno
LIMIT 300, 100

but why it's so complicated in MSSQL? if I have multiple tables to join, it's even more complicated, is there an alternative to display ranged records in MSSQL?

 

[amrita418]

AFAIK, there is no straight forward way to do this. SQL Server 2005 has row_number function which will make life easy.

Here are 3 solutions to your get the desired output other than the one I already provided:

select   * 
from     (select top 400 (SELECT COUNT(*)
                          FROM   IntraParts b  
                          WHERE  b.partno <= a.partno)  rownum,
                         partno,
                         parttype,
                         partdescription,
                         rev, 
                         datelastupdated
          FROM           IntraParts a
          ORDER BY       partno) a
where     a.rownum between 300 and 400

OR

select partno,
                         parttype,
                         partdescription,
                         rev, 
                         datelastupdated
          FROM           IntraParts a
where partno in (select top 400 partno from Intraparts order by partno)
      and partno not in (select top 300 partno from Intraparts order by partno)

OR
Create a temp table with identity column, load the top 400 rows into the table.
Using the identity column display only from 300 to 400.

Regards,
AA

 

[seahorse123]

thanks, amrita418.