none
Sorting Issue RRS feed

  • Question

  • Below is the result set that I am currently displaying 

    Order_by

    Country

    Capital

    Continent

    8

    United States

    Washington

    North America

    20

    Australia

    Canberra

    Australia

    5

    Russia

    Moscow

    Europe

    77

    Canada

    Ottawa

    North America

    88

    Denmark

    Copenhagen

    Europe

    288

    Iraq

    Baghdad

    Asia

    165

    Japan

    Tokyo

    Asia

    3

    China

    Shanghai

    Asia

    50

    New Zealand

    Auckland

    Australia

    90

    Nigeria

    Lagos

    Africa

    The columns Country,Capital and Continent as all sortable columns where users can sort them either in ascending or descending order. But at the same time I want the value in  order by column updated as well. Lets say If I sort Country in ascending order then the resultset should look like this:

    Order_by

    Country

    Capital

    Continent

    3

    Australia

    Canberra

    Australia

    5

    Canada

    Ottawa

    North America

    8

    China

    Shanghai

    Asia

    20

    Denmark

    Copenhagen

    Europe

    50

    Iraq

    Baghdad

    Asia

    77

    Japan

    Tokyo

    Asia

    88

    New Zealand

    Auckland

    Australia

    90

    Nigeria

    Lagos

    Africa

    165

    Russia

    Moscow

    Europe

    288

    United States

    Washington

    North America

     and same is the case of other columns. sorting any column also sorts and updates the value in order_by column.  I am using a simple store procedure to display the above resultset. So I think I need to make changes in the store procedure to achieve this goal. Or if some 1 else have a better solution then please share the code. I would be very grateful


    Friday, January 31, 2014 7:05 PM

Answers

  • I would advice to ask this question on the .Net forum, you may get better answers there.

    Also, this whole solution doesn't sound right - if two users will use the same control, they will override the data.

    Why you wish to store it in such way? If it is some user preference, then you should store is in some user session data.

    On the sort events in the control you will need to execute updates like following one:

    UPDATE dbo.your_table

    SET order_by = ROW_NUMBER() OVER (ORDER BY {your new order here})

    Saturday, February 1, 2014 2:38 AM

All replies

  • Please provide your table DDL along with your sample data and your expected result, thanks.


    Friday, January 31, 2014 7:19 PM
  • Sample data and expected result is already provided above
    Friday, January 31, 2014 7:27 PM
  • Can you share your table definition? It will help to understand your question. Thanks.
    Friday, January 31, 2014 7:43 PM
  • CREATE TABLE [dbo].[Table](
        [pkid] [int] IDENTITY(1,1) NOT NULL,
        [Country] [varchar](50) NULL,
        [Capital] [varchar](50) NULL,
        [Continent] [varchar](50) NULL,
        [Order_By] [int] NULL,
     CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED
    (
        [pkid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    Friday, January 31, 2014 7:50 PM
  • You cannot do what you are expecting. All columns in a row goes together when you sort on any of the column in either direction.

    Here is your table and data in case other people may need:

    CREATE TABLE [dbo].[YourTable](
    	[pkid] [int] IDENTITY(1,1) NOT NULL,
    	[Country] [varchar](50) NULL,
    	[Capital] [varchar](50) NULL,
    	[Continent] [varchar](50) NULL,
    	[Order_By] [int] NULL
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    SET IDENTITY_INSERT [dbo].[YourTable] ON 
    
    GO
    INSERT [dbo].[YourTable] ([pkid], [Country], [Capital], [Continent], [Order_By]) 
    VALUES (21, N'United States', N'Washington', N'North America', 8),(22, N'Australia', N'Canberra', N'Australia', 20)
    ,(23, N'Russia', N'Moscow', N'Europe', 5)
    ,(24, N'Canada', N'Ottawa', N'North America', 77)
    , (25, N'Denmark', N'Copenhagen', N'Europe', 88)
    , (26, N'Iraq', N'Baghdad', N'Asia', 288)
    ,(27, N'Japan', N'Tokyo', N'Asia', 165)
    ,(28, N'China', N'Shanghai', N'Asia', 3)
    ,(29, N'New Zealand', N'Auckland', N'Australia', 50)
    ,(30, N'Nigeria', N'Lagos', N'Africa', 90)
    
    SET IDENTITY_INSERT [dbo].[YourTable] OFF
    GO

    :

    Friday, January 31, 2014 8:04 PM
  • ok so now what will happen when I sort by country?
    Friday, January 31, 2014 8:12 PM
  • I suspect that your question is rater related to a specific GUI that SQL engine itself.

    Please share what application are you using to display the results.

    Friday, January 31, 2014 8:15 PM
  • You can check these queries:

    select * from [YourTable]
    Order by [Country] ASC
    
    select * from [YourTable]
    Order by [Country] DESC
    

    Friday, January 31, 2014 8:15 PM
  • One way,

    ;With cteOrderBy As
    (Select Order_By, Row_Number() Over(Order By Order_By) As rn 
    From dbo.YourTable),
    cteCountry As
    (Select Country, Capital, Continent, Row_Number() Over(Order By Country) As rn 
    From dbo.YourTable)
    Select o.Order_By, c.Country, c.Country, c.Capital, c.Continent
    From cteOrderBy o
    Inner Join cteCountry c On o.rn = c.rn
    

    Thanks to Jingyang Li for providing sample DDL and DML.

    Tom

    Friday, January 31, 2014 8:22 PM
  • I am using asp.net GridView control . Basically what I want  is that I want my sort orders be saved in the database table
    Friday, January 31, 2014 8:23 PM
  • ok let me make it simple. SO whenever you sort the column in any order  the value in order_by column changes. 
    Friday, January 31, 2014 8:48 PM
  • I would advice to ask this question on the .Net forum, you may get better answers there.

    Also, this whole solution doesn't sound right - if two users will use the same control, they will override the data.

    Why you wish to store it in such way? If it is some user preference, then you should store is in some user session data.

    On the sort events in the control you will need to execute updates like following one:

    UPDATE dbo.your_table

    SET order_by = ROW_NUMBER() OVER (ORDER BY {your new order here})

    Saturday, February 1, 2014 2:38 AM
  • I am moving it to .net.

    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Sunday, February 2, 2014 10:32 AM