selecting n rows at a time

selecting n rows at a time

Post by raghur » Wed, 29 Aug 2001 03:21:25



Hi,
I want to select n rows at a time eg. first 10, next 10 etc.
In MySQL there is a limit clause . Does anybody know what is the
equivalent in sql server.
thanks
raghu
 
 
 

selecting n rows at a time

Post by Johnny Emai » Fri, 31 Aug 2001 22:38:31


To limit the number of rows returned in MS SQL use the 'TOP n' syntax in
your query, where n=number of rows you want returned.

Example:

SELECT TOP 10 *
FROM    stores
ORDER  BY profit

- This example would give you the top 10 most profitiable stores.

Enjoy.

eJohnny


Quote:> Hi,
> I want to select n rows at a time eg. first 10, next 10 etc.
> In MySQL there is a limit clause . Does anybody know what is the
> equivalent in sql server.
> thanks
> raghu


 
 
 

1. Select first row over a time span

Hello,

I am attempting to select the first row over a period of time.  It is hard
to explain this in words so please use the tables below:

1    01/01/2003 6:00 AM
2    01/01/2003 6:10 AM
3    01/01/2003 6:35 AM
4    01/01/2003 7:00 AM
5    01/01/2003 7:10 AM
6    01/01/2003 7:15 AM

The rows that I need to select are the first in time span similar to a
session that has a 20 minute timeout.  So row 1 is the first row in a
session that times out 20 minutes after 6:10AM (the second row is in the
same session because it hadn't timed out).

So the rows that i need from the above list are 1, 3, 4.

I have built a query, but it is returning the last row in each session and
not the first - and it also can be quite slow over a very large table (which
is what the query will be working on)

Thanks in advance,
Chris Aitchison

PS. I have included a script that will create a temporary table and what i
have so far for a query:

create table #entries
(
 entryid int primary key,
 employeeid int,
 entrytimestamp datetime
)

insert into #entries (entryid, employeeid, entrytimestamp) values ( 1, 1,
'2003-01-01 06:00:00') -- this one
insert into #entries (entryid, employeeid, entrytimestamp) values ( 2, 1,
'2003-01-01 06:01:00')
insert into #entries (entryid, employeeid, entrytimestamp) values ( 3, 1,
'2003-01-01 06:02:00')
insert into #entries (entryid, employeeid, entrytimestamp) values ( 4, 1,
'2003-01-01 06:20:00')
insert into #entries (entryid, employeeid, entrytimestamp) values ( 5, 1,
'2003-01-01 06:41:00') -- plus this one
insert into #entries (entryid, employeeid, entrytimestamp) values ( 6, 1,
'2003-01-01 06:43:00')
insert into #entries (entryid, employeeid, entrytimestamp) values ( 7, 1,
'2003-01-01 07:30:00') -- and this one too
insert into #entries (entryid, employeeid, entrytimestamp) values ( 8, 1,
'2003-01-01 07:50:00')
insert into #entries (entryid, employeeid, entrytimestamp) values ( 9, 1,
'2003-01-01 07:51:00')
insert into #entries (entryid, employeeid, entrytimestamp) values (10, 1,
'2003-01-01 07:55:00')

-- I want to return rows 1, 5 & 7

select *
from #entries as e1
where not exists (
 select *
 from #entries as e2
 where e1.employeeid = e2.employeeid
 and datediff(minute, e1.entrytimestamp, e2.entrytimestamp) <= 20
 and e2.entrytimestamp > e1.entrytimestamp
)

drop table #entries

2. Copying databases from 2K to desktop engine

3. selecting x rows at a time

4. notifications

5. question regarding select (multiple rows select into one result row)

6. FoxProV2.6 QA?

7. how to check how many times row selected in sql server

8. Wview problems--help wanted

9. Writing a transformed row several times for each input row

10. Deleting rows from one table and adding those rows to another table at the same time

11. qeurying for difference in time field row to row

12. Selecting a row by it's row number

13. Q: sql select where criteria for each row is not wholly contained in the row