Alright, I'm having a problem with my SQL syntax. I'm trying to use TOP 3 to limit the results to the top 3. I'm using MySQL 4.0.21 and I can't get top to work at all. ALL, DISTINCT, and DISTINCTROW throw no errors, but I can't get TOP to work. So here's what I have thus far:
SELECT TOP 3 * FROM `table_3` ORDER BY `id` DESC
And it returns this error:
MySQL Error #1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '3 * FROM `table_3`' at line 1
I thought I had the syntax right, but now I'm not sure. What am I doing wrong?
Thankyou. I had gotten it from a SQL tutorial. I guess the author was using MSSQL. I would have thought that they'd be the same. No matter, it works fine, thank you.
Glad to help. I'm not sure that SELECT TOP is ANSI SQL-92 compliant, but I do know that DB2 documentation says it is supported. That said, I've never been able to make it work with DB2, so I just had to write long workarounds.
MSSQL and MSACCESS support it (as you know).
Not sure if Oracle supports it.
As an aside, SELECT TOP does burden the database server, because it still has to select all of the records to figure out which ones are the top n records. In a client/server setup at least only the top n records are sent over the wire to the client, but the point is worth noting when examining performance. Sometimes, I will arbitrarily set a variable in my query that I know will return enough records to get the top n, but not have to grab the entire table. Example:
SELECT top 10 player, hr, avg
FROM Players
WHERE avg > .275
ORDER BY avg
Now I get the top 10 batting averages, as long as at least 10 players hit over .275 and I din't make the server have to sort all the players who didn't hit at least .275. Anyway, 'nuff o that
I grabbed the manual as well. It is, however, a bit difficult of a read. Makes the W3C guidelines look like a beginners tutorial. I know the basics, and I could most easily simply take only the last three rows. So I'm wondering, is it more efficient to use what I have:
SELECT * FROM `table_3` ORDER BY `id` DESC LIMIT 0,3
Or, should I just do:
SELECT * FROM `table_3` ORDER BY `id` DESC
and grab the last three rows through PHP via mysql_result? Or does it even make a difference? So basically, have the database server sort and return last three rows, or have the PHP find the last three rows? Or am I thinking about this too much?
My gut instinct says that letting MySQL do the limit will be faster than having PHP do it, though I have no empirical evidence to support that.
There will be less data to send from MySQL to PHP
There will be fewer lines of PHP code to parse and execute
MySQL is optimized for that sort of thing and generally performs quite well
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
NogDog
There will be less data to send from MySQL to PHP
There will be fewer lines of PHP code to parse and execute
MySQL is optimized for that sort of thing and generally performs quite well
MstrBob
Yeah, that's what I thought, so I think I'm going to stick with my SQL
This is absolutely the right choice. Only return the records you want to process/present. This becomes especially important in a high throughput, transactional system.
We've got a guy at work that's returning 10K (large) rows from a DB2 query and just to compound the idiocy he's putting them all into an HTML table on one page. <sigh>
Ray, you need to teach that guy something. I sure hope this is an intranet and not an extranet or the www -- not that it's ok on an intranet, but you don't want to chase off your users. At the very least have him look into recordset paging. Just curious as I have spent a few years with mssql AND db2 - what platform are you running db2 on?
Oh, it's an EXTRANET app. Luckily there are very few users who hammer/get hammered by the system this way but it certainly ties up the connection pools WAY longer than any of the other queries do.
DB2 on MVS, app running in WebSphere on NT.
I'm not much of a SQL guy but I think DB2 has some other way(s) of limiting the result set size. Unfortunately I don't think ANY DBMS has the really useful paging ability to "return me x number of rows starting a row y", just "return the first x rows of this".
the limit clause in rdbms that support it (mysql for one) do just that. ADO does it, regardless of the backend database.
I worked a few years with db2 on an as/400 -- alongside an nt/win2k network with mssql servers. Unfortunately, some of the db2 documentation i have (and on ibm's web site) seem to contradict my direct experience, but db2 is one heck of a dbms.
I haven't studied it much but what I find looks like FETCH FIRST n ROWS is DB2 syntax for that and it only has the one parameter. ADO probably does it in memory after retrieving all the rows from the query. Yea, DB2 is a really solid DBM but the developers are very conservative so it's usually 5-10 years behind in non-standard features. Still I'd rather use something reliable with few proprietary whiz-bang features than something with the features but that screws up the basics.
Reply With Quote
Bookmarks