Problem with group by

Hi all

I have a table with customerid, productcode & seqno.

customerid code seqno
08117701 222 1
08117701 223 2
08117701 224 3
20106401 441 1
20106401 442 2

what I need to return is:

customerid code seqno
08117701 224 3
20106401 442 2

the details from the top seqno from each record.

Thanks inadvance

Rich

Try the example below.

Chris

Code Snippet

DECLARE @.MyTable TABLE(CustomerID VARCHAR(8), Code INT, SeqNo INT)

INSERT INTO @.MyTable(CustomerID, Code, SeqNo)

SELECT '08117701', 222, 1 UNION

SELECT '08117701', 223, 2 UNION

SELECT '08117701', 224, 3 UNION

SELECT '20106401', 441, 1 UNION

SELECT '20106401', 442, 2

SELECT t.CustomerID,

t.Code,

t.SeqNo

FROM

(SELECT CustomerID,

Code,

SeqNo,

ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SeqNo DESC) AS [RowNumber]

FROM @.MyTable) t

WHERE t.[RowNumber] = 1

|||

Hi Chris

Thanks for the reply but I am using sql 2000 and row_number is not a recongized function name

Regards

Rich

|||

here you go (with 2000)

Code Snippet

Create Table #data (

[customerid] int ,

[code] int ,

[seqno] int

);

Insert Into #data Values('08117701','222','1');

Insert Into #data Values('08117701','223','2');

Insert Into #data Values('08117701','224','3');

Insert Into #data Values('20106401','441','1');

Insert Into #data Values('20106401','442','2');

Select Main.* From #Data Main

Join (Select customerid,max(seqno) seqno From #data Group By customerid) as TopSeq

On Main.SeqNo = TopSeq.SeqNo And Main.customerid = TopSeq.customerid;

Drop table #data;

|||I found this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=144651&SiteID=1

see if it helps

Cheers
|||

declare @.MyTable table

(CustomerID VARCHAR(8), Code INT, SeqNo INT)

INSERT INTO @.MyTable(CustomerID, Code, SeqNo)

SELECT '08117701', 222, 1 UNION

SELECT '08117701', 223, 2 UNION

SELECT '08117701', 224, 3 UNION

SELECT '20106401', 441, 1 UNION

SELECT '20106401', 442, 2

select * from @.MyTable m where SeqNo in (select max(SeqNo) from @.MyTable where CustomerId = m.CustomerId)

Is this what ur looking for ?