locked
DataTable/DataView RRS feed

  • Question

  • User-1826049516 posted

    Hey,

    Is it possible to always force a single record to be first, despite what the sort order is.  I want to show the currently logged in user at the top of a list of all users.  There is quite a bit of code in the content page so doubling that up to have 1 data bound control for just that user and another for the rest would be overkill I think.

    Thanks

    Tuesday, September 20, 2016 11:58 AM

Answers

  • User3690988 posted

    You could try a Common Table Expression:

    DECLARE @userIn varchar(50) = 'Rick';
    
    With tblTop(ID, Name, BirthDate, Age )
    AS
    (
    	select ID, [name] AS Name, BirthDate, Age 
    	from Birthday
    	WHERE [Name] = @UserIn
    ),
    tblBottom(ID, Name, BirthDate, Age )
    AS
    (
    	select top 100 PERCENT ID, [name] AS Name, BirthDate, Age 
    	from Birthday
    	WHERE [Name] <> @UserIn
    	Order By [Name] ASC
    )
    
    SELECT ID, Name, BirthDate, Age  from tblTop
    UNION ALL
    SELECT ID, Name, BirthDate, Age  from tblBottom
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 27, 2016 2:36 PM
  • User3690988 posted

    Or maybe add a sort order parameter, to check:

    DECLARE @userIn nvarchar(50) = 'Rick'
    DECLARE @sortOrd nvarchar(10) = 'DESC'
    
    if @sortOrd = 'ASC'
    	begin
    		select ID, 
    		 CASE [Name]
    		 WHEN @userIn THEN ' '
    		 ELSE [Name]
    		 END AS sortName, [Name], BirthDate, Age 
    		from Birthday
    		Order By sortName asc
    	end
    else
    	begin
    		select ID, 
    		 CASE [Name]
    		 WHEN @userIn THEN 'zzzzzzzzzz'
    		 ELSE [Name]
    		 END AS sortName, [Name], BirthDate, Age 
    		from Birthday
    		Order By sortName desc
    	end
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 27, 2016 2:42 PM

All replies

  • User283571144 posted

    Hi ldoodle,

    ldoodle

    Is it possible to always force a single record to be first, despite what the sort order is.  I want to show the currently logged in user at the top of a list of all users. 

    According to your description, I suggest you could order the record in the database.

    Then you could use ADO.NET fill the database into datatable.

    In datatable, you could use datatable.select method to select the row according to column's value.

    Then you could copy this row and insert it to the first.

    More details, you could refer to follow codes:

          DataTable d1 = new DataTable();
                d1.Columns.Add("ID");
                d1.Columns.Add("url");
                d1.Rows.Add("1", "http://www.google.com");
                d1.Rows.Add("2", "http://www.facebook.com");
                string valueToSearch = "2";
                DataRow[] dr = d1.Select("ID ='" + valueToSearch + "'");// select the row you want
                DataRow newRow = d1.NewRow();
                // We "clone" the row
                newRow.ItemArray = dr[0].ItemArray;
                // We remove the old and insert the new
                d1.Rows.Remove(dr[0]);
                d1.Rows.InsertAt(newRow, 0);

    Best Regards,

    Brando

    Tuesday, September 20, 2016 12:36 PM
  • User3690988 posted

    I would probably handle this in my SQL that pulls the data.  I would use a CASE statement to pull an extra field for the user, then sort on that extra field.  For instance,  if I have a table with columns ID, Name, BirthDate, Age   and I want to put user Rick on top, I would do something like this:

    DECLARE @userIn varchar(50) = 'Rick'
    
    select ID, 
     CASE [Name]
     WHEN @userIn THEN ' '
     ELSE [Name]
     END AS sortName, [Name], BirthDate, Age 
    from Birthday
    Order By sortName

    Wednesday, September 21, 2016 5:51 PM
  • User-1826049516 posted

    I would probably handle this in my SQL that pulls the data.  I would use a CASE statement to pull an extra field for the user, then sort on that extra field.  For instance,  if I have a table with columns ID, Name, BirthDate, Age   and I want to put user Rick on top, I would do something like this:

    DECLARE @userIn varchar(50) = 'Rick'
    
    select ID, 
     CASE [Name]
     WHEN @userIn THEN ' '
     ELSE [Name]
     END AS sortName, [Name], BirthDate, Age 
    from Birthday
    Order By sortName

    I like that idea - so as the fake column would be empty for that user, it would be the first result when in ascending mode.  But, in descending mode they would be at the bottom - my web site has user sortable data.

    How could I get around that?

    Thursday, September 22, 2016 7:00 AM
  • User3690988 posted

    What is the type of user data that you would be sorting on?  If putting a blank puts it at the bottom, perhaps putting 'zzzzz' would work?

    Thursday, September 22, 2016 10:20 AM
  • User-1826049516 posted

    Problem is when asc the surname needs to be blank, when descending the surname needs to be zzzz or something.

    Tuesday, September 27, 2016 1:07 PM
  • User3690988 posted

    You could try a Common Table Expression:

    DECLARE @userIn varchar(50) = 'Rick';
    
    With tblTop(ID, Name, BirthDate, Age )
    AS
    (
    	select ID, [name] AS Name, BirthDate, Age 
    	from Birthday
    	WHERE [Name] = @UserIn
    ),
    tblBottom(ID, Name, BirthDate, Age )
    AS
    (
    	select top 100 PERCENT ID, [name] AS Name, BirthDate, Age 
    	from Birthday
    	WHERE [Name] <> @UserIn
    	Order By [Name] ASC
    )
    
    SELECT ID, Name, BirthDate, Age  from tblTop
    UNION ALL
    SELECT ID, Name, BirthDate, Age  from tblBottom
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 27, 2016 2:36 PM
  • User3690988 posted

    Or maybe add a sort order parameter, to check:

    DECLARE @userIn nvarchar(50) = 'Rick'
    DECLARE @sortOrd nvarchar(10) = 'DESC'
    
    if @sortOrd = 'ASC'
    	begin
    		select ID, 
    		 CASE [Name]
    		 WHEN @userIn THEN ' '
    		 ELSE [Name]
    		 END AS sortName, [Name], BirthDate, Age 
    		from Birthday
    		Order By sortName asc
    	end
    else
    	begin
    		select ID, 
    		 CASE [Name]
    		 WHEN @userIn THEN 'zzzzzzzzzz'
    		 ELSE [Name]
    		 END AS sortName, [Name], BirthDate, Age 
    		from Birthday
    		Order By sortName desc
    	end
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 27, 2016 2:42 PM