ordering - 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 2008 Forums

Transact-SQL (2008)

ordering

Author

Topic

mahagh
Starting Member

1 Post

Posted - 2009-08-13 : 04:16:16

I want to query rows from a table according to this order:
-1st i want all items where contains(title,'"XX"')
-2nd i want all items where contains(author,'"XX"')
-3rd i want all items where contains(translator,'"XX"')

i want the results in this order with numbering next to each..
I tried a union between those 3 queries.The results were mixed.
Any idea?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-13 : 04:26:28

select 1 as id,* from items where title = 'XX'
union
select 2 ,* from items where author = 'XX'
union
select 3,* from items where translator ='XX'
order by id

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 05:52:26

Think mahagh needs LIKE rather than =

quote:

where contains(title,'"XX"')


SELECT
     *
FROM
    (
          SELECT 1 AS [Id], * FROM items WHERE [title] LIKE '%XX%'
    UNION SELECT 2 AS [Id], * FROM items WHERE [author] LIKE '%XX%'
    UNION SELECT 3 AS [Id], * FROM items WHERE [translator] LIKE '%XX%'
    )
    c
ORDER BY
    [ID] ASC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources