knopix — 2007-10-20T00:50:08-04:00 — #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
r937 — 2007-10-20T07:15:52-04:00 — #2
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?
knopix — 2007-10-21T00:21:58-04:00 — #3
Sorry for the vague details, I was able to resolve by issue by using "GROUP BY"
r937 — 2007-10-21T06:10:42-04:00 — #4
so you're okay with getting a random id value, then
knopix — 2007-10-21T13:02:31-04:00 — #5
Yeah, the ID wasn't really a problem, I just needed to make sure 'field' was unique
r937 — 2010-03-05T15:30:53-05:00 — #6
From ( SELECT AssessID
, MAX(ID) AS last_id
BY AssessID ) AS m
JOIN daTable AS t
ON t.AssessID = m.AssessID
AND t.ID = m.last_id
cataclysm — 2010-03-05T15:37:27-05:00 — #7
Holy COW!! That worked great! I wouldn't have figured that out in a million years! Thank You!
cataclysm — 2010-03-05T14:57:22-05:00 — #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:
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!
r937 — 2010-03-05T15:02:47-05:00 — #9
how do you determine which one is last?
cataclysm — 2010-03-05T15:07:32-05:00 — #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