SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot websalacarta's Avatar
    Join Date
    May 2003
    Location
    Spain
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    problems with group by

    Hi everybody, here I am the last day of the year with one problem...

    this is my table:
    Code:
    id useridfrom useridto message 
    1    5          15        A
    2    5          15        B
    3   10           8        C
    4    2          15        D
    5   10          15        E
    6    2           6        F
    7   10          15        G
    what I want is:
    Code:
    id useridfrom message
    2    5           B
    4    2           D
    7   10           G
    for one useridto (for example, useridto=15) I want the max(id) for each useridfrom

    I thought I could use this query:
    Code:
    SELECT max( id ) , useridfrom, message
    FROM messages
    WHERE useridto =15
    GROUP BY useridfrom
    but the result doesnīt seem to be correct

    any idea about how can I do it? if itīs not possible with group, how could I do it?

    Iīm using mysql 4.0.22

    thanks and happy new year

  2. #2
    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)
    Code:
    select t1.id
         , t1.useridfrom
         , t1.message
      from messages as t1
    inner
      join messages as t2
        on t1.useridto   = t2.useridto
       and t1.useridfrom = t2.useridfrom     
     where t1.useridto = 15
    group 
        by t1.id
         , t1.useridfrom
         , t1.message   
    having t1.id
         = max(t2.id)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot websalacarta's Avatar
    Join Date
    May 2003
    Location
    Spain
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much,

    thatīs exactly what I need

    thanks 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
  •