SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2002
    Location
    Fort Lauderdale
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL, group by and order by

    Say I have a table with 3 columns:
    id(primary key, int), user_id(int), dt(datetime)

    I need to select 1 row of data from user 1. However, I need that 1 row to be the most recently entered, in other words order by dt desc. This does not work, when I do a group by for some reason. Is there a way I can nest queries to get what I want? Here is a simplified version of what I'm working on:
    Code:
    select *
    from table
    where user_id = 1
    group by user_id
    order by dt desc
    It is always returning 1 row, but with the oldest dt value.

    www.unpossiblemedia.com - A Fort Lauderdale based multimedia company.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select id, user_id, dt
      from yourtable X
     where user_id = 1
       and dt = 
           ( select max(dt)
               from yourtable
              where user_id = X.user_id
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2002
    Location
    Fort Lauderdale
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, thats what I want but I'm getting an error with the nested select.
    Code:
    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 'select max(dt) from t where user_id = x.user_id)' at line 1
    Is this b/c it is selecting twice from the same table? Using MySQL 3.23.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, it's not because you're selecting twice from the same table, it's because mysql doesn't do standard sql

    either you could have posted in the mysql forum, or you should have mentioned that you're using mysql, as this woulda saved both you and me some time and effort

    as far as sql is concerned, in many areas mysql is weak, incompetent, and non-standard -- and this is one of them

    you have to use two queries:

    3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field
    http://www.mysql.com/doc/en/example-...group-row.html

    rudy

  5. #5
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    as far as sql is concerned, in many areas mysql is weak, incompetent, and non-standard -- and this is one of them
    In other words, MySQL doesn't do subqueries
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  6. #6
    SitePoint Zealot
    Join Date
    Jul 2002
    Location
    Fort Lauderdale
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry about wasting your time on the first query. I thought it used standard SQL. It looks like 4.1 supports nesting. I guess I have to come up with some obscure way of doing this.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, hillsy, i should learn to be more diplomatic -- or, more correctly, to remember to remain diplomatic, which, i think, i usually am, with rare exceptions

    and mysql does do subqueries in 4.1


  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    obscure? two queries in succession isn't obscure

    one query gets the latest date for the user, then the second query gets the row for that date

    same as the query with the subquery, with you doing what mysql can't

    ooops, there i go again


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
  •