Order a list of elements? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Order a list of elements?

Author

Topic

pucci70
Starting Member

20 Posts

Posted - 2002-11-27 : 10:02:23

have a problem, with this data:

ID -- ORD
12 1
15 2
14 3
78 4

and so.... I want move up or down one row on other row (reorder the list
from client app), for ex:

ID -- ORD
15 1 -->move up
12 2
14 3
78 4

and so... wich SQL instructions I use for this logic???

any suggestion????

================================
Thank You All For Your Help ^^
Luca

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-27 : 10:16:09

Either use

ORDER BY
CASE when ID = 15 then 0 else 1 end, ID

or join to a table with IDs and a sort order column much as you have done yourself in your question, and order by the sort order column. The first is easy, the second is more flexible and future-proof.

-------
Moo.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-27 : 10:36:52

I think you missed the question, mr_mist. the data already has a sort table and a sort column. I think the question is, how can we easily update the sort table so that ID 15 is moved up 1 in the sort order?

The answer, I believe, is to swap the ORD values for the one you want to move and either the one just before it, or just after.

I'll post a stored proc later when I get a chance, if no one beats me to it.

- Jeff

pucci70
Starting Member

20 Posts

Posted - 2002-11-27 : 10:40:04

Perfect Jeff! where I can find this proc?

================================
Thank You All For Your Help ^^
Luca

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-27 : 10:54:59

What kind of data are you sorting? It's very rare to be sorting on a contrived value like that. What is ORD?

mr_mist's solution is a solid one. It solves the given problem. If you intended to get a more complicated solution, you'll need to provide a more complicated problem.

Jay White
{0}

pucci70
Starting Member

20 Posts

Posted - 2002-11-27 : 11:09:11

The ORD and ID fields is INTEGER, the mr_mist solution is good but not for my problem!
I want swap the rows into table:

ID -- ORD
12 1
15 2
14 3
78 4

if I want move down the row with ID 15, the new ORD field is OLDORD + 1 (and reordering all rows with new values)

ID -- ORD
12 1
14 2
15 3
78 4

if I want move up the row with ID 14, the new ORD field is OLDORD - 1
(and reordering all rows with new values)

ID -- ORD
14 1
12 2
15 3
78 4

of course with SELECT * FROM TABLE ORDER BY ORD
have return set with new order!

================================
Thank You All For Your Help ^^
Luca

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-27 : 11:14:08

I fear then that I'm missing something, but could you not then simply UPDATE the ord column that you already have with the new order? I assume that there is some business logic behind the re-ordering that you could apply to it?

Either that or take my original suggestion, but, instead of an order column, have an "offset" column that determines where the order of the item will be in relation to where it was originally, then derive an order from that.

-------
Moo.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-27 : 11:24:56

Luca, you need to stop thinking about 'moving a row down or up' ... this in not friggin microsoft office ... there is not such thing as moving a row. You can update values in column in rows. If you attack this like CS101 Bubble-Sort an Array, you'll head in the wrong direction. UPDATE ... that's all you need. UPDATE will do everything you've asked for.

Now, I don't care about the datatype (well, actually, it would be appropriate for you to provide some ddl and sample data so your audience could have something to work with) ... I am asking your what ORD and ID mean? Typically, when returning a resultset back to an user you order by meaningful things (like last name, birthdate, etc.) You, on the otherhand, are picking seemingly random numbers out of a hat and demanding they be in some order ... it makes the context of the problem difficult to understand for you reader.

Jay White
{0}

Edited by - Page47 on 11/27/2002 11:26:20

pucci70
Starting Member

20 Posts

Posted - 2002-11-27 : 11:33:04

OK! thanks a lot!

Of course the ID is a simple way to represent all field of table:

CREATE TABLE MENU(
[ID] INT,
MENU_TITLE VARCHAR(128),
TOOLTIP VARCHAR(2000),
ORD INT
)

I try.....

================================
Thank You All For Your Help ^^
Luca

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-27 : 11:49:58

Guys and gals. simmer down!

What is needed here by Pucci is very standard if you do a lot of reporting or developing.

1) It is VERY COMMON and a GOOD IDEA to store data like this for how to sort your information. Sometimes the PRIMARY KEY and the NAME are NOT the way your data needs to be presented. You need to maintain a sort table. In my company, most reports that show Offices or Departments are ordered by custom keys, because that's the way management wants it! And, you would not put logic with CASE's and such in every query that needs this ordering. you store it in a table.

2) Next, don't tell Pucci to forget about this "office crap" and moving rows around. Perhaps he IS developing a user friendly app where users need to be able to very easily change the order of things on the fly, up or down 1 row, at a time. I have done this. Others have done this. You need to give the user a nice interface to sort the information the way they want. You don't want the user to write and run STORED PROCS when they wish to change the sort on a report.

Does everyone understand? He can obviously use UPDATES or even hand-edit the tales as needed. It is a a nice stored proc he needs.

So, Page47, this is not rare or unusual. It IS rare and unusual if you are just a DBA who sits there all day and fiddles with databases and indexes and such, but NOT if you are a TRUE developer, programmer or report writer. I think you are very mistaken and out of line -- his needs were perfectly clear and justified.

NOW --

here's your solution.

Create a stored procedure, with two parameters: @OriginalID and @Direction, where 1 = down and -1 equals up.

declare @SwapID int;
declare @SwapORD int;

IF @Direction = -1
@SwapID = (SELECT Max(ID) FROM SortTable WHERE ID < @OriginalID)
ELSE
@SwapID = (SELECT Min(ID) FROM SortTable WHERE ID > @OriginalID);

SET @SwapORD = (SELECT ORD FROM SortTable WHERE ID = @SwapID);

UPDATE SortTable SET ORD = (SELECT ORD FROM SortTable WHERE ID = @OriginalID) WHERE ID = @SwapID;

UPDATE SortTable SET ORD = @SwapORD WHERE ID = @ID;

The above is very rough, don't have SQL running right now, but doesn't anyone out there get the idea?? He needs a generic alogorithm to change the ORD field in this table on the fly, up or down 1, as needed.

If someone can clean up my code and add error checking (stored procs are not my speciality) we should be all set.

- Jeff

Edited by - jsmith8858 on 11/27/2002 11:50:18

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-27 : 11:50:41

Here is a very simple update statement to 'move' a given row 'up by 1' ... you should be able to adapt this to be much more complicated.


update menu
set
    ord = case
              when ord = @ord_to_move_up then ord + 1
              when ord = @ord_to_move_up + 1 then ord - 1
              else ord
          end
where
    ord between @ord_to_move_up and @ord_to_move_up + 1

Jay White
{0}

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-27 : 12:28:20

quote:
It IS rare and unusual if you are just a DBA who sits there all day and fiddles with databases and indexes and such, but NOT if you are a TRUE developer, programmer or report writer.

So you must be a TRUE developer …

quote:
his needs were perfectly clear

and clairvoyant at that …

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-27 : 12:41:56

Read the entire post. Maybe I am clairvoyant, since he and I were on the same page the entire time.

Don't get upset, I was just defending him after you jumped all over him.

Read your "this isn't friggin microsoft" post again.

But, again, if you don't see the need for maintaining sort values for reports and the need for letting a client app do some re-ordering on the fly based on user input, then I envy you. Must be only simple "give me total orders by customer" type reporting you have dealt with! Sometimes you maybe even sorted it by name OR number!! tricky stuff.

- Jeff

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-27 : 13:30:58

Did I jump all over him? Or did I point out that you can't move a row like Excel. There is not concept of 'move a row' in this world. Maybe you read my words with a different inflection ... if you where truely defending pucci70, you'd point to my words and say "that's not nice" rather than pull out some half-assed insults and cut-downs. You wanna debate phraseology ... find someone else ... You wanna show me how to 'move a row' ... be my guest ...

Now looking back, I see where I said sorting by a non-meaningful column wasn't 'typical', but I can't seem to find the place where I said it was 'wrong' or 'a bad idea'.

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-27 : 13:52:14

'moving a row' was just a way to make the problem udnerstandable. It could be the ordering of columns or anything like that.

By you saying 'There is not concept of 'move a row' in this world', it just confirms what I mentioned: you are thinking with a DBA hat on and not as a developer or report writer.

Your DBA world, the way data is stored, has no concept of rows. Correct. But in other worlds, such as when the data must appear on a report or the screen or in some human-readable and presentable format, the concept of "Rows" and "Columns" become extremely important.

I apologize for a little sarcasm at the end of my last post; that was uncalled for.

If you ever worked for an accounting or finance dept, and many reports needed various ways of sorting or rolling up accounts, you would see that these needs are very typical.

- Jeff

pucci70
Starting Member

20 Posts

Posted - 2002-12-02 : 06:09:59

I have found this solution that's work great for me.......

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

use tempdb
go
drop table #swap
go
create table #swap([id] CHAR(1) , ord int)
go
set nocount on
insert into #swap
select 'A',1 union select 'B',2 union select 'C',3 union select 'D',4 union select 'E',5 union select 'F',6

---Initiate swapping procedure
declare @id_to_swap CHAR(1), @direction bit --0 UP, 1 DOWN
declare @neword int, @oldord int, @maxord int, @minord int
declare @swaptemp table(neword int IDENTITY UNIQUE, [id] CHAR(1) , ord int)
--Copy table into new temp table with order
insert into @swaptemp select * from #swap
--Min e Max ord value
select @maxord=max(ord) from #swap
select @minord=min(ord) from #swap
--Set swapping value and direction
select @id_to_swap = 'F', @direction = 1

select @oldord = neword from @swaptemp where [id]=@id_to_swap

if(@oldord=@minord)AND(@direction = 1)
return

if(@oldord=@maxord)AND(@direction = 0)
return

if @direction = 0
set @neword = @oldord -1
else
set @neword = @oldord + 1

update @swaptemp set ord = @neword where neword = @oldord
update @swaptemp set ord = @oldord where neword = @neword

select * from #swap --first
update #swap set #swap.ord = t1.ord
from @swaptemp t1 where #swap.[id] = t1.[id]
select * from #swap order by ord --after swapping procedure

================================
Thank You All For Your Help ^^
Luca

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources