Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: SQL Syntax TOP

  1. #1
    Join Date
    Jul 2003
    New York City

    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
    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
    New York City
    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
    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
    I have the entire manual in a folder if you want it man. It's handy to have the manual on yer own machine.
    I'm thuper, thanks for asking.

    It lives! http://www.stephenphilbin.com/ (Well it kinda' does anyway).
    My portable colour selection tool

  6. #6
    Join Date
    Jul 2003
    New York City
    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
    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
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  8. #8
    Join Date
    Jul 2003
    New York City
    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
    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
    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
    Jerryville, Tejas
    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
    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
    Ray you use DB2 -- SELECT TOP? Only in a cursor or what?

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



X vBulletin 4.2.2 Debug Information

  • Page Generation 0.16481 seconds
  • Memory Usage 3,002KB
  • Queries Executed 13 (?)
More Information
Template Usage (36):
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (4)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (15)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (15)postbit
  • (15)postbit_onlinestatus
  • (15)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (72):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates