t-sql solutions needed
-
Here's a seemingly simply, but somewhat annoying problem. I'd lke to see what solutions come up with to this problem.
TIA,
Bill.
A table has data that looks like this:
national 1st
abc national
national 123
international
national abcd
many to go
...
I need a t-sql statement that doesn't use temporary tables and returns the data ordered as follows:
national 123
national 1st
national abcd
abc national
international
many to go
In other words, the data is divided into two groups. The first group contains, alphabetized, all fields beginning with the word "national". The second group contains the remainder of the data from the original table, alphabetized.
-
Declare @Search as varchar(25)
set @Search = 'id'
Select name from dbo.SysColumns where name like '%' + @Search + '%'
order by case when name like @Search + '%' THEN 0 ELSE 1 END,name
-
"Like" will enable you to sort the data. Notice that like it is not case sensitive in finding strings.
HTH Mike
DROP TABLE #Test
IF Object_ID('TempDB...#Test')>0
DROP TABLE #Test
CREATE TABLE #Test
(
N VarChar(50)
)
INSERT INTO #Test VALUES('National 1st')
INSERT INTO #Test VALUES('national 123')
INSERT INTO #Test VALUES('International')
INSERT INTO #Test VALUES('many to go')
INSERT INTO #Test VALUES('ABC National')
DECLARE @SearchString VarChar(50)
SET @SearchString ='National'
SELECT N AS Sorted
FROM #test
order by case when N like @SearchString + '%' THEN 0 ELSE 1 END,N
/*
Returns
Sorted
----------------------------------------------
national 123
National 1st
ABC National
International
many to go
(5 row(s) affected)
To reverse the SORT use
order by case when N like '%' + @SearchString + '%' THEN 0 ELSE 1 END,N
Returns
Sorted
----------------------------------------------
ABC National
International
national 123
National 1st
many to go
(5 row(s) affected)
*/
-
How is it different from my solution???
-
There are no differences. You guys are suggesting the same technique for sorting.
/Kenneth
-
That's what I thaught... I've seen cases where the same solution is reposted when the thread is huge... or posted at the same time. But posting the same solution a few hours apart when there is only 1 message after the question is a first for me...
-
BTW, no offence to Michael, I know he's a great helper on this message board.
-
I usually read all the post before adding to a thread. In this case I started developing a query and was interupted. Then when posting just glanced at Remi's Solution saw the ref to dbo.SysColumns and said to myself, self this query does not deal with syscolumns and posted my answer.
Remi's answers are usually right to the point and use the data included in the question to make them easly understood. Not that I am above using someone's work but I do try to give them credit.Mike
-
I like using the sys* tables... they are in every database and it avoids the time loss of making/deleting the temp tables... and I figured that my exemple was clear enough in this case
.Still no offense taken and I must recredit everyone else who thaught me this stuff here.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply