ms access selecting the date from a row containing MIN function?

Question

Score:
id gametype point gdate
1 1000 4 7/30/2009
1 1000 5 8/30/2009

I have a sql
SELECT id, gametype, min(point) from Score
GROUP BY id, gametype;

The above sql gives me the minimum score which is what I need.
However, if I try to include the date also
ie
SELECT id, gametype, min(point), gdate from Score
GROUP BY id, gametype, gdate;

then my sql returns me both the rows.
My goal is to retrieve the row having minimum point for the same id and gametype.
Could anyone help me to see what I am missing here.
Thank you very much

Answer 1

GROUP BY gives you a new row for every combination of the values you selected.

Both rows have id(1) and gametype in common so your first statement comes up with

one row 1 1000 4.

By adding gdate there's no grouping anymore as both rows have different dates or to be more specific, you're actually getting back 2 groups one for every combination of id, gametype and date.

Answer 2

In your second example, you are grouping by id, type and date, which will give you every unique collection of the three. If you want date(s) when the minimum score occurred for each id/type (there could be more than one, you know), do this:



SELECT s.id, s.gametype, gdate, point FROM Score s,

(SELECT id, gametype, min(point) AS lowestscore

FROM Score GROUP BY id, gametype) m

WHERE s.id = m.id AND s.gametype = m.gametype AND point = lowestscore



Edit: I don't know why Access would be thinking those are parameters, but might be some quirk to Access...try qualifying all the column references and see if that helps.

Answer 3

you are going to could use some form of command, no remember if or no longer it is quite hassle-free sq. or a nightmarish piece of VBA. additionally, the "nicely suited 3" is quite an imaginary thought - you are able to form a table by using a diverse container and directly regulate what "nicely suited 3" certainly skill. i might say which you would be able to desire to define to your self extra needless to say what you propose by using "nicely suited 3", and write an sq. assertion to in basic terms pick those 3. Rawlyn.