sql, max value

[DCMNBD]

Hi,
I have the following table: My_table:
A B C D
LONDON b1 c1 d1
LONDON b2 c2 d2
LONDON b3 c3 d3
PARIS b4 c4 d4
PARIS b5 c5 d5
PARIS b6 c6 d6
PARIS b7 c7 d7
PRAGUE b8 c8 d8
PRAGUE b9 c9 d9
PRAGUE b10 c10 d11

I m using Microsoft Access.
I would like to get for each destination in column A(LONDON, PARIS, PRAGUE), his maximum in column B and the corresponding values in colomns C and D.
My problem is that with the following code :

select A, max(B) as B_max
from [My_table]
group by A;

I get this:
A B_max
LONDON b2
PARIS b5
PRAGUE b9

Rather I would like to get plus the above result the hole line associated to the max value found, like shown on the following :
A B C D
LONDON b2 c2 d2
PARIS b5 c5 d5
PRAGUE b9 c9 d9

Best Regards

 

[sfreeman]

Not sure why you are getting:
A B_max
LONDON b2
PARIS b5
PRAGUE b9


I get:
A BMAX
LONDON b3
PARIS b7
PRAGUE b9

Anyway...

Assuming that your C and D values will always be in increasing order, you could try:

SELECT
My_Table.A
, Max(My_Table.B) AS BMAX
, Last(My_Table.C) AS LastOfC
, Last(My_Table.D) AS LastOfD

FROM
My_Table

GROUP
BY My_Table.A;

Which gives you:

A BMAX LastOfC LastOfD
LONDON b3 c3 d3
PARIS b7 c7 d7
PRAGUE b9 c10 d10

HTH

Sam_F
"90% of the problem is asking the right question.

 

[dhookom]

I avoid Last() in every query since I have never found it reliable or functional.

Try:
SELECT *
FROM My_Table
WHERE B = (SELECT TOP 1 B FROM My_Table m WHERE m.A = My_Table.A ORDER BY B DESC);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DCMNBD]

Sorry for the confusion. .......
Actually the table My_Table is let say:

A B C D
LONDON 0.1 5 3
LONDON 5 7 2
LONDON 1.5 8 5
PARIS 2.3 78 42
PARIS 2.5 74 14
PARIS 4.2 88 11
PARIS 0.1 45 22
PRAGUE 1.6 456 4
PRAGUE 2.5 55 21
PRAGUE 2.65 22 10

select A, max(B) as B_max
from [My_table]
group by A;

I get this:
A B_max
LONDON 5
PARIS 4.2
PRAGUE 2.65

and the ideal result should be:

A B C D
LONDON 5 7 2
PARIS 4.2 88 11
PRAGUE 2.65 22 10

Best Regards

 

[lespaul]

THis may work:

SELECT M1.A, MAX(M1.B), M2.C, M2.D
FROM My_Table M1
INNER JOIN My_Table M2 on M1.A = M2.A AND M1.B = M2.B
GROUP BY M1.A, M2.C, M2.D

Leslie

 

[PHV]

One way:
SELECT * FROM My_Table T
WHERE M.B=(SELECT Max(B) FROM My_Table M WHERE M.A=T.A);

Another way:
SELECT T.*
FROM My_Table T INNER JOIN (
SELECT A, Max(B) AS MaxB FROM My_Table GROUP BY A
) M ON T.A=M.A AND T.B=M.MaxB;

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