Group By

I have 3 records in BUYER table:-

ID |NAME |STATE |COUNTRY |CODE
01 |AA |A1 |A4 |A01
01 |AA |A2 |A3 |A02
02 |BB |B1 |B2 |B01

1) Considering 'Code' is unique.
2) ID might have more than one same.


I was using 'select max(ID), max(NAME), max(STATE), max(COUNTRY), max(CODE) from buyer group by ID'.

What i get was :-
01 AA A2 A4 A02 -- 1st record
02 BB B1 B2 B01 -- 2nd record

U notice that 1st record the data was mix up between the two same ID records.

How can i get :-

01 AA A1 A4 A01 -- 1st record
02 BB B1 B2 B01 -- 2nd record

The problem was caused by max() function. And i cant ignore the max() function because they required an aggregate funtion when i used group by.

Any help?

No are you really wanting MAX or are you wanting FIRST
(Take the ID Group it and give me the first value recorded for this group of data for this ID)

Max is the largest value within the group

Based on what you stated fo results

Using First
select ID, First(NAME), First(STATE), First(COUNTRY), First(CODE) from buyer group by ID



Using Max with some of the options
select ID, First(NAME), Min(STATE), max(COUNTRY), Min(CODE) from buyer group by ID

s-

it only works in Access Databases.. What if for SQL Server query..

I tried it. But is prompt out an error message, 'First is not a recognized function name'.

Im using SQL Query Analyser (Transact SQL).