Sorting based on user input..

  • 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 > 0ORDER 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

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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!

  • 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

  • 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!

  • 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.

  • 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 > 0ORDER 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..

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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. 😉

  • 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.

  • 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

  • Change your stored procedure to work with input in a table-valued parameter which holds the new sort order for all values. Or at least all values for which the sort order change. The stored procedure then becomes a straight update. The actual logic should be in the UI.

    I don't know exactly how your UI works, but I assume that you present the data in a grid or list box, and the user can then move rows up and down in the control. When the user presses save, you compute the new numbers and then populate the TVP with the data. If you saved the old sort numbers, you can gain some efficiency by comparing with the old numbers and only populate the TVP with the change. But if the table only has some 50 rows, that is not really worth the complexity.

    If you have never worked with table-valued parameters before, see this article on my web site: http://www.sommarskog.se/arrays-in-sql-2008.html.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Thanks for the reply.

    In UI I am binding data to a grid.. all the logic will be processed using stored proc and from back end.

    In UI user will modify Displayorder of his interest and it will be passed to SP and need to process it.

    If multiple updates are there then multiple times proc will be called. I am fine with Multiple calls to database.

    Thanks..

  • deepkt (7/10/2013)


    Thanks for the reply.

    In UI I am binding data to a grid.. all the logic will be processed using stored proc and from back end.

    In UI user will modify Displayorder of his interest and it will be passed to SP and need to process it.

    If multiple updates are there then multiple times proc will be called. I am fine with Multiple calls to database.

    And that's where your problems start. I suggested using a TVP for two reasons:

    • It's more efficient. You get a single round-trip to the database, instead of doing many calls. This is not the least important if you later would move the database to the cloud or somewhere else where there is a long latency between client and database.
    • It's simpler. The current logic you have is very difficult to get right, as you already have experienced. The first call goes right, but the second call gets messed up, because you are likely to make it from the situation before the first call. To compensate for this the client has to compensate for the logic in the stored procedure, or the stored procedure has to compensate for the logic in the client. Neither of these are good software-engineering practices, as different modules should be decoupled from each other as much as possible.

    With the solution I suggest, the stored procedure is dirt simple: it's a single update from the TVP. Well, maybe a single MERGE statement as the user may add or delete rows as well. You need to some more work in the client - but not a lot. I don't know anything about grid-binding, but I assume that you can bind a DataTable to the grid, and you can pass a DataTable to a TVP.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland is right - you only need one round-trip to the database instead of one for each row.

    In .NET, when you bind data to a grid, it's usually done in the code-behind and is very straightforward. This is where you use a SQL statement and include the ORDER BY clause. The user is then free to edit the fields. When the user clicks save, do the client-side validation then do your validation in the code-behind. Create and populate your table-value parameter and then call your procedure.

    I don't know which development environment you're using, but I'd imagine that there would be something similar.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply