QUERYING -- First/Last to get low/high value 1

[GKChesterton]

[tt]I have a table:

Plan Score State
A 5 MI
A 1 GA
A 2 AL
B 4 PA
B 5 TN
C 1 AL
D 3 MA
D 2 HI
D 5 TX

I want:
For each Plan, the lowest Score and the State that had that Score.
A 1 GA
B 4 PA
C 1 AL
D 2 HI

So first I sort by Plan, then Score:
SELECT Plan, Score, ST
FROM ST_Plan ORDER BY Plan, Score;
This obtains:
A 1 GA
A 2 AL
A 5 MI
B 4 PA
B 5 TN
C 1 AL
D 2 HI
D 3 MA
D 5 TX

And then I try this, but I don't get what I want:
SELECT Plan, First(ST) AS FirstOfST
FROM (Query that sorts)
GROUP BY Plan;

My result is:
I get I want
A MI GA
B PA PA
C MI AL
D MA HI

Clearly "First" made an arbitrary selection. Access 2007 Help says: Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary. Well, I have included an ORDER BY, in the underlying query. (I assume Help is referring to the underlying query; I can't make sense of the statement otherwise. I tried nesting the underlying query into the final query so one line of SQL handled everything, but no change.)

(1) What am I doing wrong?
(2) How would I also make the Low Score appear? Seems to me that because two aggregrate domain functions will act independently, I need to apply them one at a time in successive queries ... is that so?
[/tt]

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[PHV]

Create a query named, say, qryMinScore:
SELECT Plan, Min(Score) AS MinScore
FROM ST_Plan
GROUP BY Plan

And now your query:
SELECT S.Plan, S.Score, S.ST
FROM ST_Plan AS S INNER JOIN qryMinScore AS M
ON S.Plan = M.Plan AND S.Score = M.MinScore


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GKChesterton]

That's great. Star. I don't understand it fully ( ... and that isn't even counting that I didn't know you can name table/queries in the SQL, as you can fields).

Okay, one more question. If two states got the same low score for one of the plans:
A 5 MI
A 1 GA
A 2 AL
B 4 PA
B 5 TN
C 1 AL
D 3 MA
D 2 HI
D 5 TX
D 2 TX

The outcome now has five records: Plan D shows both HI and TX. (Remarkable given the geopolitical disparities, but oh well.)

Let's say I want to knock out the repeat, and I don't care which of the two records is retained. Do I need to run it through another aggregate query, or can I handle it in that same query? I tried
SELECT S.Plan, S.Score, First(S.ST)
FROM ST_Plan AS S INNER JOIN qryMinScore AS M
ON S.Plan = M.Plan AND S.Score = M.MinScore
... but, error.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[PHV]

Perhaps this ?
SELECT S.Plan, S.Score, First(S.ST)
FROM ST_Plan AS S INNER JOIN qryMinScore AS M ON S.Plan = M.Plan AND S.Score = M.MinScore
[!]GROUP BY S.Plan, S.Score[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GKChesterton]

Thanks. Surprised I hadn't tried that. Hoping the recommendations from lespaul will help me on really understanding this stuff.

 

[PHV]

Hoping the recommendations from lespaul will help me on really understanding this stuff
I confirm that Leslie gives often really good advises.