Group By problem...

The_Chancer

Trying to get data to group.. but unsure of the group by function. I have a query as follows:

SELECT company FROM $which WHERE company='$type' GROUP BY company ORDER BY company ASC

Which gets the grouping done OK.

However what I would really like to get is below:

SELECT company,ref FROM $which WHERE company='$type' GROUP BY company ORDER BY company ASC

Which as you can tell, falls flat on its own query. So the question is this, how can I get both the company name and reference out, when I want to order by the company name when it has been grouped, without getting the message

Warning: MS SQL message: Column 'company.ref' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (severity 16)

Running M$SQL Server 7
IIS 5

Cheers,

tomhath

The only reason to use a GROUP BY is when you are also using Row Aggregate functions (SUM, COUNT, etc).

My guess is that what you really want is the DISTINCT keyword:

SELECT DISTINCT company, ref
FROM $which
WHERE company='$type'
ORDER BY company ASC, ref ASC

The_Chancer

It might work, but doesn't Distinct only strip those where the row is indentical ?

In my case, the reference numbers will be different, but the company name will be identical.

Using the group by method, can I use an aggregate function such as Max to pick the highest ref number out of the grouped company names ? (confusing I know...)

GOT IT !!!
OK use

SELECT company,MAX(ref) FROM $which GROUP BY company ORDER BY company ASC

which pulls out exactly what I want - thanks for the inadvertent tip on aggregate functions... ;-)