SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    England
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Quesion on ordering in a GROUPED query

    Hi,

    Got a question about grouping and ordering. What I am trying to do is:

    a) Group by a criteria by pick which result I am getting as part of that group
    b) Order the list of groups.

    Lets say I have a list of emails in the form:

    | id | timestamp | author | subject

    Now, I am trying to generate a list of the latest email subjects per author, ordered by timestamp.

    So I want to do:

    SELECT * FROM table GROUP BY author ORDER BY timestamp

    The problem with that, is it dosen't allow me to choose which result I am getting as part of the group, so how do I do that?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    are you looking for the latest row (based on timestamp) per author?

    what version of mysql are you on?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    England
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I am looking for the latest row, but also need to know the counts on the group, but I guess I could do that as a 2nd query.

    Looking for a solution that works in v3 as well as 4

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select t1.id
         , t1.timestamp
         , t1.author
         , t1.subject
         , count(*) as rows
      from yourtable as t1
    inner
      join yourtable as t2
        on t1.author
         = t2.author
    group
        by t1.id
         , t1.timestamp
         , t1.author
         , t1.subject    
    having t1.timestamp
         = max(t2.timestamp)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •