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

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

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

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

2/20/2006 7:45:36 PM

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

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

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

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

oh this is a MS SQL system

2/20/2006 10:59:28 PM

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

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

^^ 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

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

dude

don't say obsnum

it'll bring back evil nightmares of SAS

2/21/2006 3:21:31 PM