|
|
Posted by octangle on 06/07/06 20:42
/* Query attempt #4 (Hugo) */
SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate", MAX(InvoiceNumber)
as "InvoiceNumber", MAX(TaxableAmount) as "TaxableAmount"
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1
I talked to a few folks around the office and none of us had ever
though to use MAX() to force values out of a query using a GROUP BY
clause...
e.g. if the query were changed to look like this:
SELECT *
FROM t1
GROUP BY RegJrnID
HAVING COUNT(*) = 1
The following error occurs for each column not mentioned in the GROUP
BY clause: "Column 't1.InvoiceDate' is invalid in the select list
because it is not contained in either an aggregate function or the
GROUP BY clause." So MAX() forces these values to participate in the
result set generated by this query...
My question with this is, "Is this technique safe for all major DBs
(Oracle, SQL Server, DB2 and MySQL) and will it work with all column
types?"
Navigation:
[Reply to this message]
|