User not logged in - login - register
Home Calendar Books School Tool Photo Gallery Message Boards Users Statistics Advertise Site Info
go to bottom | |
 Message Boards » » SQL: How to Return All BUT the Top(X) Page [1]  
synapse
play so hard
60940 Posts
user info
edit post

so i have a table of events, and I want to return all events except the most 6 recent.

how would i write this query?

2/20/2006 11:26:02 AM

qntmfred
retired
40818 Posts
user info
edit post

Quote :
"The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;"


http://dev.mysql.com/doc/refman/5.0/en/select.html

2/20/2006 11:33:17 AM

CrazyJ
The Boss
2453 Posts
user info
edit post

SELECT * FROM [yourtable] a WHERE (SELECT COUNT(*) FROM [yourtable] WHERE [timestamp_column] >= a.[timestamp_column]) > 6

or you could use a db specific clause that is probably more efficient

2/20/2006 5:25:53 PM

qntmfred
retired
40818 Posts
user info
edit post

is that supposed to be more efficient than a LIMIT clause or just an alternative?

2/20/2006 7:45:36 PM

synapse
play so hard
60940 Posts
user info
edit post

i need to do this with the least possible load on the server: this particular query will be ran at least a thousand times a day on a server with a bunch of other databases/websites on it.

2/20/2006 8:48:25 PM

Maugan
All American
18178 Posts
user info
edit post

a nested select is probably not going to be as efficient as the previously posted method.

however, you do earn nerd points for using that shit

2/20/2006 9:16:52 PM

NCSUhobbit
All American
869 Posts
user info
edit post

Just SQL or PL/SQL? If you're on an Oralce platform, the rank() over function is a thing of beauty

2/20/2006 10:15:02 PM

Lowjack
All American
10491 Posts
user info
edit post

why not just run the normal query then throw out the first items whenever you manipulate the data in your program?

(because LIMIT is not portable sql)


http://troels.arvin.dk/db/rdbms/#select-top-n

[Edited on February 20, 2006 at 10:52 PM. Reason : 46546]

2/20/2006 10:40:09 PM

synapse
play so hard
60940 Posts
user info
edit post

oh this is a MS SQL system

2/20/2006 10:59:28 PM

CrazyJ
The Boss
2453 Posts
user info
edit post

what I posted would work across most dbmses. the limit clause is mysql specific.

for sql server:

SELECT * FROM [table] a WHERE NOT EXISTS (SELECT TOP 6 * FROM [table] WHERE [key1] = a.[key1] AND [key2] = b.[key2] AND [key3] = b.[key3])

2/21/2006 11:21:09 AM

Maugan
All American
18178 Posts
user info
edit post

I think the "simple" query I wrote last week for some stupid report at work was like 523 lines of PL/SQL.

Fun shit...

2/21/2006 11:39:19 AM

Breezer95
All American
6304 Posts
user info
edit post

^^ what he just said is almost the same as what I sent you - just use EXISTS in sql server.. I do believe it is a bit more intensive friendly than some other types of subqueries for execution (but don't quote me on that )

2/21/2006 12:33:30 PM

philihp
All American
8349 Posts
user info
edit post

in Oracle, there is a reserved word called rownum and rowid. rowid tells stuff about where on the disk the record exists. rownum is the obsnum.

2/21/2006 2:33:38 PM

Maugan
All American
18178 Posts
user info
edit post

dude

don't say obsnum

it'll bring back evil nightmares of SAS

2/21/2006 3:21:31 PM

 Message Boards » Tech Talk » SQL: How to Return All BUT the Top(X) Page [1]  
go to top | |
Admin Options : move topic | lock topic

© 2024 by The Wolf Web - All Rights Reserved.
The material located at this site is not endorsed, sponsored or provided by or on behalf of North Carolina State University.
Powered by CrazyWeb v2.39 - our disclaimer.