Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Sorting based on user input.. Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 10:29 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 13, 2014 2:47 AM
Points: 219, Visits: 336
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 A3-3, A4-4, A5-5, A6-6 to a New order A3-1, A4-2, A5-3, A6-4. and Save. Expected : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2
3)Now change to back vice versa: Existing order: : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A1-1, A2 -2, A3-3, A4-4,A5-5,A6-6

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 : A3-1,A4-2,A5-3,A6-4,A1-5,A2-6

2. Existing : A3-1,A4-2,A5-3,A6-4,A1-5,A2-6
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
Post #1471735
Posted Tuesday, July 9, 2013 10:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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)
Post #1471740
Posted Tuesday, July 9, 2013 10:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 12,910, Visits: 32,015
to guarantee a specific order, you must have an explicit ORDER BY as part of your command.
SELECT
* FROM MiscItems ORDER BY DisplayOrder



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1471741
Posted Tuesday, July 9, 2013 10:48 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 13, 2014 2:47 AM
Points: 219, Visits: 336

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 A3-3, A4-4, A5-5, A6-6 to a New order A3-1, A4-2, A5-3, A6-4. and Save. Expected : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2
3)Now change to back vice versa: Existing order: : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A1-1, A2 -2, A3-3, A4-4,A5-5,A6-6
Post #1471748
Posted Tuesday, July 9, 2013 10:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 12,910, Visits: 32,015
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 A3-3, A4-4, A5-5, A6-6 to a New order A3-1, A4-2, A5-3, A6-4. and Save. Expected : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2
3)Now change to back vice versa: Existing order: : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A1-1, A2 -2, A3-3, A4-4,A5-5,A6-6

yep, the answer didn't change, i think.
ORDER BY DisplayOrder



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1471750
Posted Tuesday, July 9, 2013 10:55 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 1:00 PM
Points: 4,196, Visits: 3,235
So what are you after here? Copying and pasting a few lines from the original post isn't of much help, since we've already read it.

If you're trying to control the order it appears in your application GUI, do what Lowell said in your query to pull the data and add an ORDER BY clause.

If you're after something else, you'll need to explain what you want to accomplish.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1471752
Posted Tuesday, July 9, 2013 11:20 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 13, 2014 2:47 AM
Points: 219, Visits: 336
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..
Post #1471764
Posted Tuesday, July 9, 2013 12:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
deepkt (7/9/2013)

one of other scenario is failing.. plz help..


What is failing? Are you getting an error message? Or is it a logic error? I am still confused as to what you are trying to do here.


_______________________________________________________________

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)
Post #1471774
Posted Tuesday, July 9, 2013 12:09 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 1:00 PM
Points: 4,196, Visits: 3,235
Sean Lange (7/9/2013)
deepkt (7/9/2013)

one of other scenario is failing.. plz help..


What is failing? Are you getting an error message? Or is it a logic error? I am still confused as to what you are trying to do here.

Whew! I'm glad I'm not the only one.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1471776
Posted Tuesday, July 9, 2013 12:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 A3-3, A4-4, A5-5, A6-6 to a New order A3-1, A4-2, A5-3, A6-4. and Save. Expected : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2
3)Now change to back vice versa: Existing order: : A3-1, A4-2, A5-3, A6-4, A5-1,A6- 2 change, A3 to 3, A4 to 4, A5 to 5, A6 to 6.. then expected order: A1-1, A2 -2, A3-3, A4-4,A5-5,A6-6


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 : A3-1, A4-2, A5-3, A6-4, A5-1,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.
Post #1471780
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse