

SSC Veteran
Group: General Forum Members
Last Login: Tuesday, October 27, 2015 8:04 AM
Points: 235,
Visits: 358


Hi,
I have one requirement and need help. I have a table:
CREATE TABLE MiscItems(ID INT, Description VARCHAR(50), DisplayOrder SMALLINT)
INSERT INTO MiscItems(ID,Description,DisplayOrder) SELECT 100, 'A1',1 UNION ALL SELECT 101,'A2',2 UNION ALL SELECT 102,'A3',3 UNION ALL SELECT 104,'A4',4 UNION ALL SELECT 105,'A5', 5 UNION ALL SELECT 106,'A6',6
SELECT * FROM MiscItems DROP TABLE MiscItems
I will display in UI: Description & Displayorder and both are editable in UI.
My requirement is User can modify DisplayOrder of his interest and clicks on save, then accordingly the display order should be saved in table. User can change multiple items at once and accordingly data should be re arranged and saved in table. Display order will not be a zero & non negative value.
1) Say A1, A2, A3, A4, A5, A6 are given Order as 1,2,3,4,5,6 2) Now, Change the existing Order of A33, A44, A55, A66 to a New order A31, A42, A53, A64. and Save. Expected : A31, A42, A53, A64, A51,A6 2 3)Now change to back vice versa: Existing order: : A31, A42, A53, A64, A51,A6 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A11, A2 2, A33, A44,A55,A66
I have below proc for updatimg display order:
CREATE PROCEDURE proc_Disporder @ID INT, @OldDisplayOrder INT, @NewDisplayOrder INT AS BEGIN IF(@OldDisplayOrder >= @NewDisplayOrder) OR (@OldDisplayOrder = 0) BEGIN
UPDATE miscitem SET DisplayOrder = DisplayOrder + (CASE WHEN @OldDisplayOrder = @NewDisplayOrder THEN 1 ELSE 2 END) FROM MiscItems miscitem WHERE ID <> @ID AND miscitem.DisplayOrder >= @NewDisplayOrder AND miscitem.DisplayOrder > 0 END IF(@OldDisplayOrder < @NewDisplayOrder) AND(@OldDisplayOrder <> 0) BEGIN
UPDATE miscitem SET DisplayOrder = (CASE (DisplayOrder  1) WHEN 0 THEN DisplayOrder ELSE (DisplayOrder  1) END) FROM MiscItems miscitem WHERE ID <> @ID AND miscitem.DisplayOrder <= @NewDisplayOrder AND miscitem.DisplayOrder >= @NewDisplayOrder  1 AND miscitem.DisplayOrder > 0 END DECLARE @MiscItems TABLE(DisplayOrder INT IDENTITY(1,1), ID INT) INSERT @MiscItems SELECT ID FROM MiscItems WHERE DisplayOrder > 0 ORDER BY DisplayOrder UPDATE src SET src.DisplayOrder = mi.DisplayOrder FROM MiscItems src INNER JOIN @MiscItems mi ON src.ID = mi.ID END
Its a logic error..,sorry for the confusion..
My data in table is: A1  1, A2  2, A3  3, A4  4, A5 5, A6 6
1. User updates : A3 to 1, A4 to 2, A5 to 3, A6 to 4 Expected : A31,A42,A53,A64,A15,A26
2. Existing : A31,A42,A53,A64,A15,A26 User updates : A3 to 3, A4 to 4, A5 to 5, A6 to 6 Expected : A1  1, A2  2, A3  3, A4  4, A5 5, A6 6




SSCoach
Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 15,800,
Visits: 16,164


deepkt (7/9/2013) Hi,
I have one requirement and need help. I have a table:
CREATE TABLE MiscItems(ID INT, Description VARCHAR(50), DisplayOrder SMALLINT)
INSERT INTO MiscItems(ID,Description,DisplayOrder) SELECT 100, 'A1',1 UNION ALL SELECT 101,'A2',2 UNION ALL SELECT 102,'A3',3 UNION ALL SELECT 104,'A4',4 UNION ALL SELECT 105,'A5', 5 UNION ALL SELECT 106,'A6',6
SELECT * FROM MiscItems DROP TABLE MiscItems
I will display in UI: Description & Displayorder and both are editable in UI.
My requirement is User can modify DisplayOrder of his interest and clicks on save, then accordingly the display order should be saved in table. User can change multiple items at once and accordingly data should be re arranged and saved in table. Display order will not be a zero & non negative value.
please help..
Not entirely sure what you are asking here. Part of your question sounds like you want to use a parameter as your sort order. This is certainly feasible. The other part makes it sound like you want store this sort order or something??? Keep in mind that the ONLY way to ensure order of rows from a table is to add an ORDER BY to your query.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Moden's splitter.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2  Dynamic Cross Tabs Understanding and Using APPLY (Part 1) Understanding and Using APPLY (Part 2)




SSChampion
Group: General Forum Members
Last Login: Today @ 5:08 PM
Points: 14,436,
Visits: 37,825


to guarantee a specific order, you must have an explicit ORDER BY as part of your command.
SELECT * FROM MiscItems ORDER BY DisplayOrder
Lowell
 help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!




SSC Veteran
Group: General Forum Members
Last Login: Tuesday, October 27, 2015 8:04 AM
Points: 235,
Visits: 358


1) Say A1, A2, A3, A4, A5, A6 are given Order as 1,2,3,4,5,6 2) Now, Change the existing Order of A33, A44, A55, A66 to a New order A31, A42, A53, A64. and Save. Expected : A31, A42, A53, A64, A51,A6 2 3)Now change to back vice versa: Existing order: : A31, A42, A53, A64, A51,A6 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A11, A2 2, A33, A44,A55,A66




SSChampion
Group: General Forum Members
Last Login: Today @ 5:08 PM
Points: 14,436,
Visits: 37,825


deepkt (7/9/2013)
1) Say A1, A2, A3, A4, A5, A6 are given Order as 1,2,3,4,5,6 2) Now, Change the existing Order of A33, A44, A55, A66 to a New order A31, A42, A53, A64. and Save. Expected : A31, A42, A53, A64, A51,A6 2 3)Now change to back vice versa: Existing order: : A31, A42, A53, A64, A51,A6 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A11, A2 2, A33, A44,A55,A66
yep, the answer didn't change, i think.
ORDER BY DisplayOrder
Lowell
 help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!




SSCrazy Eights
Group: General Forum Members
Last Login: Saturday, July 23, 2016 7:13 AM
Points: 9,168,
Visits: 8,479





SSC Veteran
Group: General Forum Members
Last Login: Tuesday, October 27, 2015 8:04 AM
Points: 235,
Visits: 358


I have below proc: for updating display order: CREATE PROCEDURE proc_Disporder @ID INT, @OldDisplayOrder INT, @NewDisplayOrder INT AS BEGIN IF(@OldDisplayOrder >= @NewDisplayOrder) OR (@OldDisplayOrder = 0) BEGIN
UPDATE miscitem SET DisplayOrder = DisplayOrder + (CASE WHEN @OldDisplayOrder = @NewDisplayOrder THEN 1 ELSE 2 END) FROM MiscItems miscitem WHERE ID <> @ID AND miscitem.DisplayOrder >= @NewDisplayOrder AND miscitem.DisplayOrder > 0 END IF(@OldDisplayOrder < @NewDisplayOrder) AND(@OldDisplayOrder <> 0) BEGIN
UPDATE miscitem SET DisplayOrder = (CASE (DisplayOrder  1) WHEN 0 THEN DisplayOrder ELSE (DisplayOrder  1) END) FROM MiscItems miscitem WHERE ID <> @ID AND miscitem.DisplayOrder <= @NewDisplayOrder AND miscitem.DisplayOrder >= @NewDisplayOrder  1 AND miscitem.DisplayOrder > 0 END DECLARE @MiscItems TABLE(DisplayOrder INT IDENTITY(1,1), ID INT) INSERT @MiscItems SELECT ID FROM MiscItems WHERE DisplayOrder > 0 ORDER BY DisplayOrder UPDATE src SET src.DisplayOrder = mi.DisplayOrder FROM MiscItems src INNER JOIN @MiscItems mi ON src.ID = mi.ID END
one of other scenario is failing.. plz help..




SSCoach
Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 15,800,
Visits: 16,164





SSCrazy Eights
Group: General Forum Members
Last Login: Saturday, July 23, 2016 7:13 AM
Points: 9,168,
Visits: 8,479





Grasshopper
Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:35 PM
Points: 22,
Visits: 147


1) Say A1, A2, A3, A4, A5, A6 are given Order as 1,2,3,4,5,6 2) Now, Change the existing Order of A33, A44, A55, A66 to a New order A31, A42, A53, A64. and Save. Expected : A31, A42, A53, A64, A51,A6 2 3)Now change to back vice versa: Existing order: : A31, A42, A53, A64, A51,A6 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A11, A2 2, A33, A44,A55,A66
Your expected results do not make sense. In your example after making changes, you have A5 and A6 repeating with two different order values. What happened to A1 and A4?
Expected : A31, A42, A53, A64, A51,A6 2
If you are trying to allow users to customize their sort order, you should add a user id field to your table.
Your proc to update the table should simply be an update statement setting the new order values that the user provided from the GUI. The front end application should do presentation layer validation to ensure the user input a number in correct format. Let them enter the numbers for their sort order and then save that list. It looks like you are over complicating this.



