Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is there a way to sort the table after records are inserted? Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 4:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:25 AM
Points: 184, Visits: 852
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
Post #1489108
Posted Wednesday, August 28, 2013 4:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 2,304, Visits: 2,772
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’! **
Post #1489117
Posted Wednesday, August 28, 2013 4:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:25 AM
Points: 184, Visits: 852
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
Post #1489122
Posted Wednesday, August 28, 2013 5:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 2,304, Visits: 2,772
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’! **
Post #1489129
Posted Wednesday, August 28, 2013 5:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:25 AM
Points: 184, Visits: 852
The same query will work with two rows. The actual scenario has 100’s of data.

Sanz
Post #1489134
Posted Wednesday, August 28, 2013 5:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 2,304, Visits: 2,772
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’! **
Post #1489135
Posted Wednesday, August 28, 2013 5:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
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

Post #1489140
Posted Friday, August 30, 2013 1:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 30, 2013 1:37 PM
Points: 30, Visits: 17
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.
Post #1490278
Posted Friday, August 30, 2013 1:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,098, Visits: 3,155
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1490283
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse