Sorting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sorting

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-29 : 08:51:41

Roshan writes "I have a table called Artists . One of the field in the artist table is "Artist_name".

Assume these the values of the fields

Artist_Id Artist_name
1 Andy the coolest
2 Coolest
3 Cood
4 Cool
5 Cooler

I want to search the artist table where artist name like 'cool'. So logically it will list all the records except artist_id 3 , but I want the output to be sorted based on the search criteria, so my ouput should be

Artist_Id Artist_name
4 Cool
5 Cooler
2 Coolest
1 Andy the coolest

Can you suggest me this output in a single query"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-29 : 08:57:11

select the fields you want, use like in the where clause and use order by..

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-29 : 10:37:58

RickD - what did you plan to order by?

Roshan - you can use the 'difference' function in your ordering (which works for your example, at least). If the difference function isn't sufficient for evaluating how good matches are, look into full-text searching, or write your own evaluation function.

--data
declare @Artists table (Artist_Id int, Artist_name varchar(50))
insert @Artists
          select 1, 'Andy the coolest'
union all select 2, 'Coolest'
union all select 3, 'Cood'
union all select 4, 'Cool'
union all select 5, 'Cooler'

--calculation
select * from @Artists where Artist_name like '%cool%' order by DIFFERENCE(Artist_name, 'cool') desc

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 11:24:35

haven't tested this so give it a go.


select * from @Artists 
where Artist_name like '%cool%' 
order by case when Artist_name like 'cool%' then 1
              when Artist_name like '%cool%' then 2
              when Artist_name like '%cool' then 3
              end

Go with the flow & have fun! Else fight the flow

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-29 : 11:42:07

quote:
Originally posted by spirit1

haven't tested this so give it a go.

I did, and it didn't work for the example

, but I do see where you're going

. A small modification 'sorts' it out (for the example at least) - it all depends on the sorting criteria Roshan wants to use, I guess.

select * from @Artists 
where Artist_name like '%cool%' 
order by case when Artist_name = 'cool' then 0
              when Artist_name like 'cool%' then 1
              when Artist_name like '%cool%' then 2
              when Artist_name like '%cool' then 3
              end

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 12:14:36

lol

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources