## SQL Group By, Aggregate Functions

### SQL Group By, Aggregate Functions

I'm somewhat new to SQL, and here's my problem.  I have a table with
three columns, one of which I want to find the max of and the second
of which I want to group by.  So, I'm doing something like this:

select max(c1), c2, c3
from tbl
group by c2, c3

Problem with this is that I don't really want to group by the third
column.  I want to group by the second and "find out" what the third
happens to be (i.e., what the data field of c3 is in the row where c1
is max, "grouped by" c2).  Sorry if this is somewhat unclear.  Can
anyone help me?  Thanks.

### SQL Group By, Aggregate Functions

Does the table have a primary key?  Is it c2?  You could do this with a
derived table...

SELECT b.c1, b.c2, a.c3
FROM
table a,
(
SELECT
c1 = MAX(c1), c.2
FROM table
GROUP BY c2
) b
WHERE a.c2 = b.c2

Quote:> I'm somewhat new to SQL, and here's my problem.  I have a table with
> three columns, one of which I want to find the max of and the second
> of which I want to group by.  So, I'm doing something like this:

> select max(c1), c2, c3
> from tbl
> group by c2, c3

> Problem with this is that I don't really want to group by the third
> column.  I want to group by the second and "find out" what the third
> happens to be (i.e., what the data field of c3 is in the row where c1
> is max, "grouped by" c2).  Sorry if this is somewhat unclear.  Can
> anyone help me?  Thanks.

### SQL Group By, Aggregate Functions

Quote:>                 c1 = MAX(c1), c.2

Should be c2, not c.2
I think I need new glasses

If I have two tables:

Master

M_ID
M_DESC

and Detail

D_ID
D_MASTER (= M_ID)
D_AMOUNT

and I want to get a total for each master, I would code the query as

SELECT M_DESC, M_ID, SUM(D_AMOUNT) AS TOTAL FROM DETAIL
JOIN MASTER ON M_ID=D_MASTER
GROUP BY M_ID

Seems to work fine in most cases, but one of our database vendors insists
that that syntax is illegal under SQL-92 and that the result of the SUM
function can not be trusted to be accurate. Is he right? (I hope not: we
have this format query all over the place)

Robert