November 23, 2011 at 9:42 am
Hi!!!
Let's consider next tables:
create table Prods -- table of products
(id int, name nvarchar(10), sort int)
insert into Prods
select * from
(select 1 id, 'A' name, 1 sort
Union all
select 2 id, 'B' name, 2 sort
Union all
select 3 id, 'C' name, 3 sort
Union all
select 4 id, 'D' name, 4 sort
Union all
select 5 id, 'E' name, 5 sort
Union all
select 6 id, 'F' name, 6 sort
Union all
select 7 id, 'G' name, 7 sort
Union all
select 8 id, 'H' name, 8 sort
Union all
select 9 id, 'I' name, 9 sort
Union all
select 10 id, 'J' name, 10 sort
Union all
select 11 id, 'K' name, 11 sort
Union all
select 12 id, 'L' name, 12 sort
Union all
select 13 id, 'M' name, 13 sort
Union all
select 14 id, 'N' name, 14 sort
) x
as an input parameter I receive next table
create table NewP -- the new positions of my
(id int, name nvarchar(10), sort int)
insert into NewP
select * from
(
select 5 id, 'E' name, 2 sort
Union all
select 11 id, 'K' name, 3 sort
Union all
select 13 id, 'M' name, 6 sort
) za
The output of my procedure is:
SELECT * FROM Prods ORDER BY Sort
Based on the values from NewP.Sort I want to set up the new positions of those products into Prods table.
In other words: the product E (id=5) is currently on position 5 and should be moved on position 2 (NewP.Sort = 2 where NewP.Id = 5)
So, after the update, the table should look like
idnamesort
1A1
2B4
3C5
4D7
5E2
6F8
7G9
8H10
9I11
10J12
11K3
12L13
13M6
14N14
Is there a way to do it without using the while loop?
Thanks in advance!!
November 23, 2011 at 10:05 am
So if you have three new positions coming in, E=2, K=3, and M=6, what happens to the Names (B,C,F) that were in those positions? At first I thought they were swapped with the new sort postition, but your sample output doesn't follow that. How did B get sort 4?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
November 23, 2011 at 10:23 am
Imagine that you have a webpage where you can see the list and decide to move the products (re-sort them). So when you press "SAVE" button: E is on position 2, K is on position 3 so B will go on position 4, C on position 5, M on position 6 and so on. The idea is when you drag and drop a product in list, only that product will be flagged as "moved". In my case, the "moved" products are E, K and M.
After refresh the list (select * from prods order by sort) will look like:
idnamesort
1A1
5E2
11K3
2B4
3C5
13M6
4D7
6F8
7G9
8H10
9I11
10J12
12L13
14N14
Thanks!
November 23, 2011 at 1:39 pm
Sounds like an interface issue not a tsql issue if I am understanding correctly, that is, allow a user to re-order a list and then save the new order back to the database.
Does this describe what you want to do?
http://www.pedrera.com/blog/asp-net-listview-drag-and-drop-reordering-using-jquery/
November 23, 2011 at 1:49 pm
Oh sorry I see the distinction. The interface is not sending back the entire dataset with the new rankings but instead just the 'changes'.
November 23, 2011 at 2:27 pm
What troubles me is the idea that you are trying to store rows in a specific order in a table. This isn't a spreadsheet or a flat file, but conceptually you believe it is. Relational tables have no sequence, in the sense that you mean. You only control sequence with ORDER BY clauses at the end of your SELECT statements. While an index may logically save you the work of a sort when you want to see rows in a particular order, physically they can be in different sequences on each page in memory, or even on different pages.
Remember this: In the absence of a A SELECT/ORDER BY, you have no guarantee concerning the sequencing of your output. There is no default order. When you have one or more indexes on a table, the optimizer will use whichever one it deems most convenient or sort prior to doing a join to speed up performance. You have limited control over those decisions, but they definitely affect the sequence of how rows are presented to you. An ORDER BY fixes that.
There is no need to try to insert/delete rows in the table to "move" them. Just update the [Sort] columns for each. I assume that is the sequence in which you want them presented.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 23, 2011 at 2:34 pm
I believe the OP is looking for an Update statement of the 'Sort' column in his table, not delete/inserts. So the physical order wouldn't matter, only the value of the sort column, so that the data can be presented back to the user in the user-defined custom sort.
November 23, 2011 at 2:37 pm
I think I understand the problem now. He wants the [Sort] columns for all rows with a number equal to or greater to the "moved" row to be incremented.
Yes?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 23, 2011 at 2:37 pm
You're right.
thanks!
November 23, 2011 at 2:39 pm
But the IDs should remain the same, only the sort value will change on each row. Right?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 23, 2011 at 2:44 pm
Yes
November 23, 2011 at 2:46 pm
Coming right up.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 23, 2011 at 2:57 pm
>>I think I understand the problem now. He wants the [Sort] columns for all rows with a number equal to or greater to the "moved" row to be incremented.
I would think there would need to be some decrementing as well.
If 2 moves to 14 then what was 3 needs to become 2.
November 23, 2011 at 4:50 pm
Sorry to take so long. My daughter called from NZ and she gets priority 🙂
Anyway, I think this will give you the results you want. Use this as a CTE to update all your rows.
select p.ID,p.Name --,p.sort,n.sort
,row_number() over(order by
coalesce(n.sort,(p.sort+(select COUNT(*) from #NewP n where n.sort <= p.sort)))
,n.sort desc) as newsort
from #prods p
left join #NewP n on p.id = n.id
order by newsort
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 23, 2011 at 5:11 pm
YES!
That's what I'm looking for.
THANKS!!!!
I was thinking about a similar solution ... but you gave it before me 🙂
Why did you use coalesce instead of isnull because there are only two values to be compared?
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply