Quote:
quote:
... I just want to get EITHER one record.
|
But
which row? As I said, there is no "pick a random row" function, nor is there a "first" row, etc.
I don't think you understand what the MAX aggregate function does.
The MAX function determines the Maximum value across the
set of rows defined by the GROUP BY clause. The values are not "jumbled up" as you suggest, but represent each maxmimum value as you requested. So, when you query:
Code:
SELECT max(NAME), max(STATE), max(COUNTRY), max(CODE) from buyer group by ID
you are requesting a resultset that contains the maximum NAME, maximum STATE, maximum COUNTRY, and maximum CODE value from each set of rows where the ID value is the same. You are asking for the maximum value of each column, so each will be its maximum across the entire set of rows for each ID, not necessarily from the same row. The Maxmimum value for a set of strings will be the string that colates last in your character set.
In order to pick, say, the "first" (or perhaps "last") row in each group as defined by the ID, you must have some column value that places the rows where the ID is the same into some defined order.
Let's try to use your CODE value as the ordering column.
Suppose you have the following query:
Code:
SELECT MAX(CODE), ID FROM buyer GROUP BY ID
This will select the value of the CODE column which is a maxmimum for each set of ID values. Using your data from your original post, this would be:
A02 01
B01 02
Now we can use this ordering to select each row from your table where the CODE value is this maximum value. We need to use a correlated subquery to do this:
Code:
SELECT ID, NAME, STATE, COUNTRY, CODE
FROM buyer B1
WHERE CODE=(SELECT MAX(CODE) FROM buyer B2 WHERE B1.ID=B2.ID)
The subquery finds the maximum CODE value for each ID value in turn, and the outer query then selects the contents of the row for the ID where the CODE value is that maximum.
Alternatively you could use the MIN function to instead retrieve the "first" row in each ID group.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Group By
