by raghur » Wed, 29 Aug 2001 03:21:25
by Johnny Emai » Fri, 31 Aug 2001 22:38:31
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
5. question regarding select (multiple rows select into one result row)
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