order rows in sql query based on which rows meet condition first

I would like to know if there is anyway in sql server to order rows based on which rows meet the conditions first , for example if i am writing a query like this

SELECT * FROM IMAGES 
WHERE NAME LIKE '%TEST%'
AND DESCRIPTION LIKE '%TEST%'

I want rows that match the name to appear bef开发者_如何转开发ore rows that match the description , usually sql server will arrange them by their ID , so...anyway to do this ?! (note : I dont want to use union statement as that's gonna exhaust the database ).

First, the UNION would not necessarily exhaust the database; as written, it might not make a difference at all, performance-wise, depending on how the query optimizer decided to handle it.

Second, as your where clause contains an "AND", all your results will match on both description and name, so I don't think that's what you want.

Third, assuming you meant to have an "OR" in your query, you could try something like this:

select
    *
from
    images i
where 
    i.name like '%TEST%'
    or i.description like '%TEST%'
order by
    case
        when i.name like '%TEST%' then 0
        else 1
    end asc;

You can use CASE statements in your ORDER BY clause to get what you are after.

ORDER BY CASE(WHEN NAME LIKE '%TEST' THEN 1 ELSE 0 END) DESC

EDIT:

Sample code as proof of concept in SQL Server:

DECLARE @t table(id int)

INSERT INTO @t
VALUES
(1),
(2),
(3),
(4),
(5)

SELECT *
FROM @t
ORDER BY(CASE WHEN id > 3 THEN 1 ELSE 0 END) DESC