www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: SQL Syntax TOP

  1. #1
    Join Date
    Jul 2003
    Location
    New York City
    Posts
    2,771

    SQL Syntax TOP

    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?

  2. #2
    Join Date
    Feb 2003
    Posts
    2,745
    TOP is a MS SQL Server clause.

    MySQL uses the LIMIT clause.

    Here is the MySQL documentation for query syntax.

  3. #3
    Join Date
    Jul 2003
    Location
    New York City
    Posts
    2,771
    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.

  4. #4
    Join Date
    Feb 2003
    Posts
    2,745
    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

  5. #5
    Join Date
    Mar 2004
    Posts
    3,056
    I have the entire manual in a folder if you want it man. It's handy to have the manual on yer own machine.

  6. #6
    Join Date
    Jul 2003
    Location
    New York City
    Posts
    2,771
    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?

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,923
    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

    eBookworm.us

  8. #8
    Join Date
    Jul 2003
    Location
    New York City
    Posts
    2,771
    Yeah, that's what I thought, so I think I'm going to stick with my SQL, it's faster. Thanks again.

  9. #9
    Join Date
    Feb 2003
    Posts
    2,745
    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.

  10. #10
    Join Date
    Nov 2003
    Location
    Jerryville, Tejas
    Posts
    11,715
    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>

  11. #11
    Join Date
    Feb 2003
    Posts
    2,745
    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?

  12. #12
    Join Date
    Feb 2003
    Posts
    2,745
    Ray you use DB2 -- SELECT TOP? Only in a cursor or what?

  13. #13
    Join Date
    Nov 2003
    Location
    Jerryville, Tejas
    Posts
    11,715
    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".

  14. #14
    Join Date
    Feb 2003
    Posts
    2,745
    "return me x number of rows starting a row y"
    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.

  15. #15
    Join Date
    Nov 2003
    Location
    Jerryville, Tejas
    Posts
    11,715
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles