SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sorting based on user input..


Sorting based on user input..

Author
Message
deepkt
deepkt
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 362
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64886 Visits: 17979
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 Modens 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)
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74991 Visits: 40985
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!
deepkt
deepkt
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 362
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
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74991 Visits: 40985
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
--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!
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51009 Visits: 10844
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
deepkt
deepkt
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 362
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..
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64886 Visits: 17979
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 Modens 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)
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51009 Visits: 10844
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
keebler96
keebler96
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search