Select multiple distinct values

Databases
1

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

the question doesn’t make sense yet :slight_smile:

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?

3

Sorry for the vague details, I was able to resolve by issue by using “GROUP BY”

4

so you’re okay with getting a random id value, then

5

Yeah, the ID wasn’t really a problem, I just needed to make sure ‘field’ was unique

6 
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
7

Holy COW!! That worked great! I wouldn’t have figured that out in a million years! Thank You!

8

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!

9

how do you determine which one is last?

10

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