Group by-function or Selection-query??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The data set looks lite this:
A B C
1 c 49
1 b 85
1 c 24
2 d 18
2 f 98
2 e 62
2 e 97
3 f 74
3 a 99

I want to group/compress/choose rows from the data so that I get one row per
number in the A column. I want to choose the rows by a: the "highest" letter
in column B, and b: out of the remaining rows choose the highest number in
column C. The result would then look like:
A B C
1 c 49
2 f 98
3 f 74

I tried doing this using the group by-function, but realized it separates
the cells within a row from each other, and mixes them. I don´t want to think
about how much data I might have messed up in my database earlier doing
that... Hopefully not months of work....

And as usual, I would appreciate it if you could write what the
querycommands I need to use stand for(is short for), as I might have to
translate them into Swedish before I could use them... Thank you!!
 
UNTESTED SQL, but I think the following would work

SELECT A, B, Max(C) as BigC
FROM TheTable
WHERE B = (SELECT Max(Tmp.B)
FROM TheTable as TMP
WHERE Tmp.A = TheTable.A)
GROUP BY A, B

If this is unclear, post back.
 
I was able to do it, but it wasn't easy. It took a query based on a query
and sub-query, besides the table.

First the table, called Table1 (which matches your structure and data):

Field1 Field2 Field3
1 c 49
1 c 24
1 b 85
2 d 18
2 f 98
2 e 62
2 e 97
3 f 74
3 a 99

Here's the higher-level query called Query18, including the sub-query:

SELECT Table1.Field1, Query19.MaxOfField2, Max(Table1.Field3) AS MaxOfField3
FROM Table1 INNER JOIN Query19 ON (Table1.Field1 = Query19.Field1) AND
(Table1.Field2 = Query19.MaxOfField2)
GROUP BY Table1.Field1, Query19.MaxOfField2
HAVING (((Max(Table1.Field3)) In (select field3 from table1 inner join
query19 on (table1.field1 = query19.field1) and (table1.field2 = query19.
maxoffield2) WHERE (table1.field1 = query19.field1) and (table1.field2 =
query19.maxoffield2);)));

And here's the lower-level query called Query19, which is joined in the
previous SQL statement:

SELECT Table1.Field1, Max(Table1.Field2) AS MaxOfField2
FROM Table1
GROUP BY Table1.Field1;

Convoluted? Absolutely, but it works!

The result of running Query18?

Field1 MaxOfField2 MaxOfField3
1 c 49
2 f 98
3 f 74

Which is what you want.

HTH
 
I tested your SQL statement, John, and it works like a charm - and much
easier than mine!

John said:
UNTESTED SQL, but I think the following would work

SELECT A, B, Max(C) as BigC
FROM TheTable
WHERE B = (SELECT Max(Tmp.B)
FROM TheTable as TMP
WHERE Tmp.A = TheTable.A)
GROUP BY A, B

If this is unclear, post back.
The data set looks lite this:
A B C
Click to expand...
[quoted text clipped - 28 lines]
querycommands I need to use stand for(is short for), as I might have to
translate them into Swedish before I could use them... Thank you!!
Click to expand...
Click to expand...
 
Back
Top