#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    4
    Rep Power
    0
    Feild 1 . Field 2
    Tom . 99
    **** . 167
    Harry . 123
    Tom . 34
    Harry . 121
    Tom . 156

    --------------------

    I want to ORDER BY field 2 DESC
    but only return the highest Field 2 for identicel Field 1 entries (there are more then 2 fields in table)

    How do I go about doing this?

    I want it to return

    --------------------

    **** . 167
    Tom . 156
    Harry . 123

    --------------------

    I do believe it is done with DISTINCT, but not sure.


    TNT


    [This message has been edited by TNT (edited July 18, 2000).]
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    select field1,max(field2) from table group by field1 order by field2 desc;
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    4
    Rep Power
    0
    Thank you rod k
    It worked perfectly. I was trying to go at this all so wrong and forgot about max().
    I had to do a bit more to it since there were two tables to join.

    SELECT *,max(Game) FROM bowler, scores
    WHERE bowler.BowlerID = scores.BowlerID
    GROUP BY LastName
    ORDER BY game DESC

Similar Threads

  1. Getting database info based on db info
    By Zamees in forum PHP Development
    Replies: 3
    Last Post: February 2nd, 2004, 08:55 PM
  2. Replies: 2
    Last Post: January 14th, 2004, 05:07 PM
  3. Replies: 4
    Last Post: January 14th, 2004, 05:22 AM
  4. Replies: 6
    Last Post: January 13th, 2004, 09:38 AM
  5. Replies: 3
    Last Post: January 10th, 2004, 10:44 PM

IMN logo majestic logo threadwatch logo seochat tools logo