Is there a way to sort the table after records are inserted?

  • Is there a way to sort the table after records are inserted?

    Declare @tab Table(name varchar(10), value varchar(10))

    Declare @mystring varchar(10)

    set @mystring='AA'

    insert into @tab

    select 'A', 1

    union

    select 'AA',2

    select @mystring=REPLACE(@mystring,name,value) from @tab order by LEN(name) desc

    select @mystring

    Here is my problem

    1. I create a table with two cols Name and Value

    2. I insert records into this table using a union statement. (Ex records : {A,1},{AA,2})

    3. Now I have a string which I need to be replaced.

    Ex: if my string is AA, I need it to be replaced by 2. But now what happen is since in the table A is before AA, it replaces it with two 1’s , ie 11

    Sanz
  • The problem is you can't assign multiple values to a single variable. So your "select @mystring=...." statement will only return the last updated value. You also has the order of the parameters in the REPLACE statement wrong. The first parameter is the original value, the second parameter is the string to be replaced.

    And the most important rule: an order is only guaranteed if an ORDER BY is used.

    Use this code instead:

    Declare @tab Table(name varchar(10), value varchar(10))

    Declare @mystring varchar(10)

    set @mystring='AA'

    insert into @tab

    select 'A', 1

    union

    select 'AA',2

    select

    name

    , value

    , REPLACE(name,@mystring,value) as new_value

    from @tab

    order by name

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • I have used order by Len(name) desc so that AA will come first, but since order by will apply atlast, it will not work.

    One option I have in mind is to use a subquery like

    Declare @tab Table(name varchar(10), value varchar(10))

    Declare @mystring varchar(10)

    set @mystring='AA'

    insert into @tab

    select * from (select 'A' Name , 1 Value

    union

    select 'AA',2) A order by LEN(name) desc

    select @mystring=REPLACE(@mystring,name,value) from @tab

    select @mystring

    What you think?

    Sanz
  • Because you assign the updated values from a table to a single variable, it will still be possible to get the wrong results. The update on the table could result in multiple rows. Only the last updated row will be visible in the variable.

    So the basic of your solution is not correct. Do not assign multiple rows to a single variable. If you really need to assign an updated value to the variable, at least use the TOP 1 clause in combination with an ORDER BY. This will guarantee the row to be returned. Or apply a WHERE clause that will return a single row.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • The same query will work with two rows. The actual scenario has 100’s of data.

    Sanz
  • Sanz (8/28/2013)


    The same query will work with two rows. The actual scenario has 100’s of data.

    And like I said before: only ONE value of this multi-row query wil be stored in the @mystring variable.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Couple steps back...

    What are you trying to do?

    Bear in mind that a table has no defined order (by definition). A resultset only has a guaranteed order if an ORDER BY statement is specified on the query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • select @mystring=REPLACE(@mystring,name,value) from @tab

    We don't sort tables. The entire relational world is based on the concept that absolutely no assumptions can ever be made on the physical order of rows in a table. Among other advantages, this frees RDBMS systems to place physical rows however it needs them. So "sort a table" is meaningless. We only sort result sets.

    If you want an action to apply to one particular row, you must clearly identify that row. In this simple case, you can change your statement to:

    select @mystring=REPLACE(@mystring,name,value) from @tab where name = @mystring

    But your statement is more complex than it needs to be. Why do you even need the REPLACE?

    select @mystring=value from @tab where name = @mystring

    TommCatt
    In theory, there is no difference between theory and practice. In practice, there is.

  • You should be able to use a correlated subquery, like so:

    SELECT

    @mystring,

    REPLACE(@mystring, name, value) AS newstring

    FROM (

    SELECT TOP (1) * FROM @tab WHERE @mystring LIKE '%' + name + '%' ORDER BY LEN(name) DESC

    ) AS correlated

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 9 posts - 1 through 8 (of 8 total)

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