SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    what's wrong with this query ?

    SELECT *, COUNT(productid) AS total FROM products LIMIT total, 3

  2. #2
    SitePoint Zealot Gar onn's Avatar
    Join Date
    Feb 2011
    Location
    Belgium
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think MySQL doesnt allow varibles in the LIMIT part

    my google-fu gave me this:
    MySQL Bugs: #8094: Variables in the LIMIT Clause

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    even if you did manage to obtain a COUNT from the products table, putting that number as the first paramter in the LIMIT clause indicates how many rows to skip, and there just ain't gonna be any 3 rows after the last one

    besides, LIMIT usually doesn't make sense without ORDER BY

    but your main problem is the dreaded, evil "select star"

    what do you really want -- a COUNT, or all rows? ya can't have both

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist speda1's Avatar
    Join Date
    Jan 2002
    Posts
    550
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are counting, you want a GROUP BY.

  5. #5
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i want to select 3 last row and i have to count total row to do it... is there any way to do it ?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by niurexx View Post
    i want to select 3 last row
    last 3 based on what?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lets say i want to select product with active status and i want display last 3 row of result

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by niurexx View Post
    ...and i want display last 3 row of result
    there is no order in a database table

    so "last 3" makes no sense unless you do it with reference to the values in a column

    3 latest --> select ... order by date descending limit 3

    3 smallest --> select ... order by size ascending limit 3

    get the idea?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    there is no order in a database table
    Well that's not quite true, is it. The database infers a FIFO order by default if an index isnt defined.

  10. #10
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i want select last 3 row of the result but display it as ascending.. for example facebook comment.. it will display 4 last row but in ascending order..

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by niurexx View Post
    i want select last 3 row of the result but display it as ascending.. for example facebook comment.. it will display 4 last row but in ascending order..
    that would be done by date, descending. You then take the result and reverse the order when you get it into PHP or whatever language you're retrieving the data with.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by StarLion View Post
    The database infers a FIFO order by default if an index isnt defined.
    in a word... no

    if you actually believe that, then i've got a bridge in brooklyn and some nice dry property in florida you might be innerested in

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Run SELECT * on a database table without an index a few times.

    "Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows."

    (MySQL Manual 5.6, 7.3.1)

    So yes, there is an implied index - the order of data in the table file, which would be FIFO.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    like i said, if you believe that...



    mysql places the rows onto the disk wherever there happens to be space available

    without getting into a lot of detail, this is ~not~ always going to be "fifo"

    furthermore, if you do not include an ORDER BY clause in your SELECT statement, mysql can return the rows in whatever sequence it finds convenient

    can you be sure that rows that happen to be in a buffer will be re-retrieved in fifo sequence? or could you not imagine that mysql will return the rows in the buffer first, and then go and retrieve the others off the disk

    DO NOT rely on anything other than ORDER BY to retrieve rows in a given sequence

    if you want fifo, use a datetime column
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    mysql places the rows onto the disk wherever there happens to be space available

    without getting into a lot of detail, this is ~not~ always going to be "fifo"

    furthermore, if you do not include an ORDER BY clause in your SELECT statement, mysql can return the rows in whatever sequence it finds convenient

    can you be sure that rows that happen to be in a buffer will be re-retrieved in fifo sequence? or could you not imagine that mysql will return the rows in the buffer first, and then go and retrieve the others off the disk

    DO NOT rely on anything other than ORDER BY to retrieve rows in a given sequence

    if you want fifo, use a datetime column
    First off - You should always be using an order by for sequencing. *Agrees with r937, and never disagreed with him on that point.*

    Now i think i'm wandering a bit from the original post here, but I am now curious.

    How does mySQL retrieve those rows its randomly scattered around the disk? Cause, in my head, i say "The table file lists the memory addresses for the data, wherever it is. And it stores them FIFO... so it would read them FIFO."

    Buffer (which tbh i hadnt thought of) doesnt actually store the data, but the addressing... which would, again, in my head, first pull a table based on the table file (FIFO), and then if called again, would return the same list (still FIFO)...


Bookmarks

Posting Permissions

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