aggregate functions

[Not Me]

Hi,

I have 3 columns, I wish to group by one of them, choose one with max(), and
wish for the 3rd column to be in the same row as the one in the 2nd column.

Eg.

1 a i
1 b ii
2 a v
2 c iv
2 b i
3 d iii
3 c vi

if I group by the first column, choose max() on the second column (picking
(1,b),(2,c),(3,d)) I want to choose column 3 from the same row

output should be

1 b ii
2 c iv
3 d iii

Hope that's clear enough! I don't want to group by the 3rd column also,
because that duplicates column 1.

Cheers,
Chris

[Allen Browne]

If you really must use Roman numerals here, you will have to teach Access
how to order these.

Create another table with 2 fields:
- RomanNumber (text)
- SortAs (Number)

You can then join this table in a query to your other one, and choose the
max related value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Not Me" <su**@sumwhere.fake> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...

I have 3 columns, I wish to group by one of them, choose one with max(), and wish for the 3rd column to be in the same row as the one in the 2nd column.
Eg.

1 a i
1 b ii
2 a v
2 c iv
2 b i
3 d iii
3 c vi

if I group by the first column, choose max() on the second column (picking
(1,b),(2,c),(3,d)) I want to choose column 3 from the same row

output should be

1 b ii
2 c iv
3 d iii

Hope that's clear enough! I don't want to group by the 3rd column also,
because that duplicates column 1.

[Not Me]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

"Not Me" <su**@sumwhere.fake> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...

I have 3 columns, I wish to group by one of them, choose one with max(), and

wish for the 3rd column to be in the same row as the one in the 2nd

column.

Eg.

1 a i
1 b ii
2 a v
2 c iv
2 b i
3 d iii
3 c vi

if I group by the first column, choose max() on the second column (picking (1,b),(2,c),(3,d)) I want to choose column 3 from the same row

output should be

1 b ii
2 c iv
3 d iii

Hope that's clear enough! I don't want to group by the 3rd column also,
because that duplicates column 1.

If you really must use Roman numerals here, you will have to teach Access
how to order these.

Ah, sorry that was just an example, the information isn't really numbers,
letters and numerals!
Anyway, I don't want to do anything with the 3rd column, other than display
it (max is on 2nd column)

[Roger]

this worked for me, using your data sample
paste this is the sql-view of a query

SELECT a.id, b.maxF1 AS f1, a.f2
FROM table1 AS a, [SELECT id, Max(f1) AS MaxF1
FROM Table1
GROUP BY id]. AS b
WHERE (((a.id)=[b].[id]) AND ((a.f1)=[b].[maxF1]));
I just don't know why the design view can display it properly ?

the [SELECT ... ]. AS b creates a sub table (b) with the first 2
fields you want, and then I join it to the first table to get the
matching f2

"Not Me" <su**@sumwhere.fake> wrote in message news:<bq**********@ucsnew1.ncl.ac.uk>...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...

"Not Me" <su**@sumwhere.fake> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...

I have 3 columns, I wish to group by one of them, choose one with max(), and wish for the 3rd column to be in the same row as the one in the 2nd column.
Eg.

1 a i
1 b ii
2 a v
2 c iv
2 b i
3 d iii
3 c vi

if I group by the first column, choose max() on the second column (picking (1,b),(2,c),(3,d)) I want to choose column 3 from the same row

output should be

1 b ii
2 c iv
3 d iii

Hope that's clear enough! I don't want to group by the 3rd column also,
because that duplicates column 1.

If you really must use Roman numerals here, you will have to teach Access
how to order these.

Ah, sorry that was just an example, the information isn't really numbers,
letters and numerals!
Anyway, I don't want to do anything with the 3rd column, other than display
it (max is on 2nd column)