SQL Query help

[MastermindSQL]

I have a table in a following format:

PK ID NextID
-- --- ------
1 200 300
2 100 200
3 300 400
4 50 100

Now I want to get a resultset like:

ID DisplayOrder
--- ------------
50 1
100 2
200 3
300 4
400 5

Thanks in advance.

 

[bborissov]

SELECT Id FROM YourTable ORDER BY Id

Borislav Borissov

 

[MastermindSQL]

Borislav Borissov, What if my table is like:

PK ID NextID
-- --- ------
1 AXD NNQ
2 AYX AXD
3 NNQ QWX
4 ABC AYX

Now I want to get a resultset like:

ID DisplayOrder
--- ------------
ABC 1
AYX 2
AXD 3
NNQ 4
QWX 5

 

[jbenson001]

What is the logic(rules) behind your ordering? In your second example, the ID is not sorted alphabetically, so how would you determine the order you are displaying? Is there some other column that can be used?

Jim

 

[MastermindSQL]

Hi Jim, there is nothing in alphabetical/ numeric order. It is basically like a linked list. For example Item A points to "NextItem" Item X and so on. A simple ORDER BY will not solve this problem.

Item --> NextItem --> AnotherItem and so on. I need to get what is the ordering of each Item. Thanks.

 

[maswien]

Try following code:

MyTable:

PK     ID     NextID
--     ---    ------
1      200    300
2      100    200
3      300    400
4      50     100


create table #result
  ( id int, displayorder int identity(1,1))
insert into #result (id) select min(id) from MyTable
declare @next_id as int
select @next_id = next_id from MyTable 
  where id = (select max(id) from #result)
while @next_id is not null
begin
  insert into #result(id) values (@next_id)
  set @next_id = null
  select @next_id = next_id from MyTable 
    where id = (select max(id) from #result)
end
select * from #result

 

[SQLBill]

I would create a temporary table with two columns. One column called ID and of the same datatype as your ID column. The second column would be DispayOrder set as IDENTITY (1,1). Then SELECT the ID values into the new table and they will be assigned a DisplayOrder based on the identity seed.

-SQLBill

Posting advice: FAQ481-4875