need help with select where query

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 2005 Forums

Transact-SQL (2005)

need help with select where query

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-09-20 : 13:43:24

Hi,
I have a table with the column Namen and Title and other columns

I want to do a seacrh by a keyWord @KeyWord but I need to look up the keyword in both the Name and the Title, something like:

where Name like '%' + @KeyWord + '%' OR Title like '%' + @KeyWord + '%'

But I need the total returned resultset to be sorted to include the rows for which the Name column is like the keayword then after those rowset I want the rows for which the 2nd column Title is like @KeyWord to be returned at the end of the resultset.

I mean: I need the resultset to include first all the rows for Name like @keyword and the rows for which Title like '%' + @KeyWord must be last in the resultset.

Also I dont want the rows to be duplicated, meaning if a row is contained in the first resultset because Name like @KeyWord I don t want that row to be also in the second result set when its title is like @KeyWord

Thanks a lot for brainstorming with me.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-20 : 13:47:33

select *
from
(select 1 as Num, ...
from...
where Name like '%' + @KeyWord + '%'
union
select 2 as Num, ...
from...
where Title like '%' + @KeyWord + '%'
) t
order by Num

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 13:48:07

ELECT <cols>,'Rowset1'
FROM yourtable
WHERE [Name] like '%' + @KeyWord + '%'
UNION
SELECT <cols>,'Rowset2'
FROM yourtable
WHERE Title like '%' + @KeyWord + '%'

Jim

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-20 : 13:49:30

jim:
note that your solutions doesn't guarantee the ordering

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-09-21 : 09:12:52

Thanks for your help guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources