SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Jul 2004
    Location
    Brooklyn, NY
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select multiple distinct values

    How do I select a distinct value, and the id for that row?
    Basically what is the correct syntax for this: SELECT DISTINCT field, id FROM table

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the question doesn't make sense yet

    if "id" means the primary key, then obvioulsy there's going to be more than one id value for each distinct value of field, yes

    so for each distinct value of field, which id value would you like to see? the lowest one? the highest one?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2004
    Location
    Brooklyn, NY
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for the vague details, I was able to resolve by issue by using "GROUP BY"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    so you're okay with getting a random id value, then
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jul 2004
    Location
    Brooklyn, NY
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, the ID wasn't really a problem, I just needed to make sure 'field' was unique

  6. #6
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know this post is a few yeyars old, but I need help on this same topic.
    I have a mysql table with student test scores in it.
    Some of the fields are as follows:
    ID|StudentID|AssessID|Total

    I need to pull all the totals, BUT only for DISTINCT AssessID's.
    In other words, if more than one scores exists for a student for the same test, only pull the LAST score entered.

    SELECT DISTINCT AssessID From Table WHERE blabla='blabla' ORDER BY ID DESC ~ won't let me pull the Total.

    And SELECT * FROM table WHERE blabla='blabla' GROUP BY AssessID ~ only selects the first record instead of the latest

    Any help would be appreciated. Thanks!

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    how do you determine which one is last?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The field ID is auto-incremented. That's how I determine the last entry

    And thanks for the fast reply, you guys don't mess around lol

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT t.ID
         , t.StudentID
         , t.AssessID 
         , t.Total
      From ( SELECT AssessID
                  , MAX(ID) AS last_id
               FROM daTable 
              WHERE blabla='blabla' 
             GROUP
                 BY AssessID ) AS m
    INNER
      JOIN daTable AS t
        ON t.AssessID = m.AssessID
       AND t.ID = m.last_id
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Holy COW!! That worked great! I wouldn't have figured that out in a million years! Thank You!


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
  •