SitePoint Sponsor

User Tag List

Results 1 to 25 of 25

Thread: MySQL Select..

  1. #1
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Select..

    Ok Here is a sample set of rows:

    id|gid|date|file|
    1|3|2004-04-17|asdasd
    2|4|2004-04-18|kjlasd
    3|3|2004-04-18|asiosadsd

    ID = auto_increment
    GID = specifies which owner this row belongs to
    DATE = datetime field
    FILE = text data

    How can I do a select that will return just the latest row for each gid?:
    SK

  2. #2
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ooop, just remembered GROUP BY... Sorry for this post. Please delete if necassary. For others who are wondering about the query:

    SELECT * FROM table GROUP BY gid ORDER BY date_added DESC

    SK

  3. #3
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A slight problem now, that query DOES select one row for each gid but NOT the latest row, just the first ... why?
    SK

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SharifTK
    A slight problem now, that query DOES select one row for each gid but NOT the latest row, just the first ... why?
    why? because your GROUP BY is wrong

    the GROUP BY clause must include every non-aggregate column in the SELECT list

    i just basically tell people never to use "select star" with GROUP BY

    by the way, any other database besides mysql, you woulda got a syntax error on your incorrect GROUP BY

    mysql, bless it's heart, goes right ahead and runs it

    i guess they (mysql developers) thought it was better to give unpredictable and confusing results instead of a syntax error

    here's your solution:
    Code:
    select t1.id
         , t1.gid
         , t1.date
         , t1.file
      from foo t1
    inner
      join foo t2
        on t1.gid = t2.gid
    group
        by t1.id
         , t1.gid
         , t1.date
         , t1.file
    having t1.date
         = max(t2.date)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I sort of understand what you said, GROUP BY must have all the selected fields?
    SK

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    all the ones that aren't involved in an aggregate function

    examples:

    select a,min(b) from t group by a

    select count(*) from t

    select a, min(b), c, max(d) from t group by a, c

    select x, y, z from t group by x, y, z
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah I see, ok well this is my REAL query:

    Code:
    SELECT
    gid, DATE_FORMAT(date_added, '%m/%d/%Y at %l:%i:%s %p') AS date_added 
    FROM 
    videos 
    WHERE 
    status = '1' 
    GROUP BY 
    gid 
    ORDER BY 
    date_added 
    DESC LIMIT 0, 25
    This does not return the latest row either.
    SK

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you want the latest row in the table, you do not need a group by

    if you want the latest row per group, you will need a query like mine in post #4

    ordinarily, you specify row selection within a group by using a correlated subquery, which i would show you if i thought you were on mysql 4.1

    if you're not, you have to use the self-join i gave you with a GROUP BY

    by the way, for your query, date_added is not involved in an aggregate expression, so it must be in the GROUP BY too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    According to my Cpanel, the Mysql version is 4.0.18-standard.

    Anyways, I am trying to do something similar to the stuff at http://xboxmovies.teamxbox.com. You can see that it displays the latest records first, but only 1 per game, the latest for that game that is. Is that what your query will do?
    Last edited by SharifTK; May 12, 2004 at 16:13.
    SK

  10. #10
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also, if I do GROUP BY gid, date_added I get all rows for that gid instead of just one.
    SK

  11. #11
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for the multiple posting, freaking keep coming up with questions: does a WHERE come before or after a GROUP BY?
    SK

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SharifTK
    Is that what your query will do?
    i have an idea -- why don't you try it



    Also, if I do GROUP BY gid, date_added I get all rows for that gid instead of just one
    actually, you don't get all the rows, you get all the distinct combinations of gid, date_added

    that's what grouping does

    does a WHERE come before or after a GROUP BY?
    allow me to introduce you to the fine manual --

    http://dev.mysql.com/doc/mysql/en/SELECT.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And how fine it is . Anyways, I am confused by your query becuase it looks like it does a join with another table (t2). I am working with one table only, or is t2 an alias?

    EDIT: what do you mean by distinct combination?
    SK

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SharifTK
    I am working with one table only, or is t2 an alias?
    yes, and yes

    it's a self-join


    what do you mean by distinct combination?
    the combinations are distinct

    gid dateadded
    100 2004-05-11
    100 2004-05-12
    100 2004-05-13
    100 2004-05-14
    101 2004-05-14
    102 2004-05-14
    103 2004-05-14
    104 2004-05-14

    see? no dupes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You ARE a guru! Why the join though? I thought that was only necassary when using more than one table.
    SK

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks

    well, that's just how a self-join works

    it's easiest to think of it as two identical "copies" of the table being joined

    there really only is one table, but the join works as though there were two

    the tricky part is understanding how, when you self-join x rows to x rows, you get x^2 results, the only rows you wnt from the results are the rows of t1 which match the max of t2

    it really deserves a good article, which i will write one day

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

  17. #17
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, honestly trying the query:

    Code:
    SELECT 
    t1.gid, DATE_FORMAT(t1.date_added, '%m/%d/%Y at %l:%i:%s %p') AS date_added 
    FROM 
    videos t1 
    INNER JOIN 
    videos t2 
    ON 
    t1.gid = t2.gid 
    WHERE
    t1.status = '1'
    GROUP BY 
    t1.gid, t1.date_added 
    HAVING 
    t1.date_added = max(t2.date_added) 
    ORDER BY 
    t1.date_added 
    DESC 
    LIMIT 
    0, 25
    And it returns the error:
    Unknown column 't1.date_added' in 'having clause'

    Tried adding "t1." to the DATE_FORMAT() and AS but still same problem.
    SK

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i think it's gettin gmixed up because you're using the same alias name as the column name

    try DATE_FORMAT(date_added ...) as foo (not date_added)

    and then GROUP BY foo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok maybe I'm missing something here, I should supply more info. Here is the table structure:
    Code:
    CREATE TABLE videos (
      id int(11) NOT NULL auto_increment,
      status smallint(1) NOT NULL default '0',
      gid int(11) NOT NULL default '0',
      date_added datetime NOT NULL default '0000-00-00 00:00:00',
      title text NOT NULL,
      description text NOT NULL,
      file text NOT NULL,
      PRIMARY KEY  (id)
    ) TYPE=MyISAM;
    And the current sql query:
    Code:
    SELECT
    t1.gid, DATE_FORMAT(t1.date_added, '%m/%d/%Y at %l:%i:%s %p') AS foo
    FROM 
    videos t1 
    INNER JOIN 
    videos t2 
    ON 
    t1.gid = t2.gid 
    WHERE 
    t1.status = '1' 
    GROUP BY 
    t1.gid, t1.foo 
    HAVING 
    t1.date_added = max(t2.date_added) 
    ORDER BY 
    t1.date_added 
    DESC 
    LIMIT 
    0, 25
    The error I'm getting now:
    Unknown column 't1.foo' in 'group statement'

    Hopefully since I posted the table structure, we'll all be on the same page. Again, what I am trying to do is get the latest row for each gid.
    SK

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    <sigh />

    add t1.date_added to the SELECT (this fixes a potential problem with the ORDER BY as well)

    then you can say:

    GROUP BY t1.gid, t1.date_added
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    <sigh />

    You are like ... so smart. It worked now!

    Anyways, check out this query (which also works), and see if there are any flaws please. This involves two tables this time.

    Code:
    SELECT 
    t1.gid, DATE_FORMAT(t1.date_added, '%m/%d/%Y at %l:%i:%s %p') AS date, t1.date_added, t3.id, t3.title 
    FROM 
    videos t1, games t3 
    INNER JOIN 
    videos t2 
    ON 
    t1.gid = t2.gid 
    WHERE 
    t1.status = '1' AND t3.id = t1.gid AND LEFT(t3.title, 1) BETWEEN '0' AND '9' 
    GROUP BY 
    t1.gid, t1.date_added, t3.id, t3.title 
    HAVING 
    t1.date_added = max(t2.date_added) 
    ORDER BY 
    t1.date_added 
    DESC
    Again thanks. Maybe anything to make the query more efficient would be great. Thank you.
    SK

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    do not mix "table list" syntax with JOIN syntax


    SELECT ...
    FROM videos t1
    INNER JOIN games t3
    ON t1.gid = t3.id
    INNER JOIN videos t2
    ON t1.gid = t2.gid
    WHERE ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I kind of understand, what was I doing wrong? I mean my current query works as I want it to (or at least it looks that way), maybe it's another one of those things mysql lets you get away with?
    SK

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it's more of a "best practices" thing

    several databases, not just mysql, allow you to mix syntax forms

    it's hell to debug, that's all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah I see. Well I'll remember to follow that. Thanks for all the great help... now go be a guru..
    SK


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
  •