SQL Help 1

[cs2009]

I have this data in a table.

OrderNo SeqNo Rep
111111 3 AX1
222222 3 ZT5
333333 1 AB7
333333 3 TM4
444444 1 WA9

When the order number repeats (see order number 333333) I want to return the record with the lowest sequence number. In this case it is order number 333333 with rep AB7 having sequence number 1. I do not want the sequence number returned. I want my output to look like this:

OrderNo Rep
111111 AX1
222222 ZT5
333333 AB7
444444 WA9

Thanks.

 

[MarkSweetland]

How about something like this:

set nocount on 

declare @tbl TABLE( OrderNo int, SeqNo int, Rep varchar(3))
insert into @tbl
select 111111, 3 ,'AX1'
union all
select 222222, 3 ,'ZT5'
union all
select 333333, 1 ,'AB7'
union all
select 333333, 3 ,'TM4'
union all
select 444444, 1, 'WA9'


select a.OrderNo, a.Rep
from @tbl a
join (
	select OrderNo, Minseq=MIN(seqNo)
	from @tbl
	group by OrderNo
) as b
on a.OrderNo = b.OrderNo and a.SeqNo = b.Minseq

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[SQLScholar]

It may be easier for people if you dont title a post in the SQL programming forum.

Its kind of a given that you need SQL help - more descriptive titles do help people to respond.

Not trying to pick holes - just trying to help.

Take care

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------