How can I use SELECT DISTINCT but maintain the original order

Say I have this result set with two fields (numbers and letters):
1 A
3 A
1 B
2 B

The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order?

When I try select distinct Number from MyResultSet it will reorder the new result set by the Number field and return
1
2
3

However, I'd like maintain the Letter order and return
1
3
2

hmm.. tricky one. I think this is not exactly what you were hoping for but it's the only thing i could come up with that works for sure.

Remember that database theory thinks or records as collection in a mathematical sense, and these do not have an order.

Good luck,

John

Code Snippet

declare @.KeepOrder table (Num int, Ord int)
declare @.Num int,
@.Ord int

declare c cursor fast_forward for

-- change this query to your table
select Num from Test
order by Let

open c
fetch next from c into @.num
set @.Ord = 1

while @.@.fetch_status = 0
begin
if not exists(select num from @.Keeporder where Num = @.Num)
begin
insert into @.Keeporder(num, ord) values(@.num, @.Ord)
set @.Ord = @.Ord + 1
end
fetch next from c into @.num
end

close c
deallocate c

select num
from @.Keeporder
order by ord

|||

Try:

create table dbo.t1 (

c1 int not null,

c2 char(1) not null

)

go

insert into dbo.t1 values(1, 'A')

insert into dbo.t1 values(3, 'A')

insert into dbo.t1 values(1, 'B')

insert into dbo.t1 values(2, 'B')

go

;with cte

as

(

select

c1,

c2,

row_number() over(partition by c1 order by c2) as rn

from

dbo.t1

)

select

c1

from

cte

where

rn = 1

order by

c2

go

drop table dbo.t1

go

AMB

|||

You could try something along these lines... Bruce

declare @.t1 table (ID int NULL, CD varchar(1) NULL)

insert into @.t1 (ID, CD) select 1, 'A'

insert into @.t1 (ID, CD) select 3, 'A'

insert into @.t1 (ID, CD) select 1, 'B'

insert into @.t1 (ID, CD) select 2, 'B'

select * from @.t1

;

WITH work_tbl AS

(

SELECT ROW_NUMBER() OVER (ORDER BY CD) AS 'RowNumber',

ID, CD

FROM @.t1

)

SELECT min(RowNumber), ID

FROM work_tbl

GROUP BY ID

ORDER BY 1

|||I really have to start looking into these new 2005 functions....|||great, thanks Bruce and HunchBack! that's exactly what I was looking for.

Is the WITH clause new for 2005? I haven't seen it before, but it's a lot easier than creating and dropping temporary tables.|||Yes, I believe the WITH is new.. it's nice for clearer code when first making a derived table.. and then referencing it multiple times in the main query. Note that you must put the semi-colon just before the WITH, strange... It won't work without that semi-colon... Bruce