DataReader + SQL + sortorder

  • Thread starter Thread starter Brent
  • Start date Start date
B

Brent

Curious little problem...

Suppose I have these fields in my table ("NameTable"):

FirstName LastName SearchTerms

Kathleen Smith Smith Kathleen
Gladys Lee Lee Gladys
Lee Burton Oswald Lee

I'm searching for the string "lee" wherever it may appear.

My SQL is

"SELECT FROM NameTable.* WHERE SearchTerms Like '%lee%'"

I want to order my results as follows:

1. People with the last name of "Lee" are first;
2. People with the first name of "Lee" are second;
3. People having a "lee" in their name are third.

Unless I'm missing something, a SQL UNION statement doesn't seem to produce
the power to sort the results in this order.

Is it possible to run three separate SQL statements and stick the results
into a single datareader (using nextresult()), which I can then output to
the datagrid?

Thanks for any help!

--Brent
 
Brent said:
Curious little problem...

Suppose I have these fields in my table ("NameTable"):

FirstName LastName SearchTerms

Kathleen Smith Smith Kathleen
Gladys Lee Lee Gladys
Lee Burton Oswald Lee

I'm searching for the string "lee" wherever it may appear.

My SQL is

"SELECT FROM NameTable.* WHERE SearchTerms Like '%lee%'"
Click to expand...

SELECT * FROM NameTable
WHERE SearchTerms Like '%lee%'
ORDER BY CASE WHEN SearchTerms Like '% Lee' then 1
WHEN SearchTerms Like 'Lee %' then 2
ELSE 3 END


Davie
 
Try reposting this in microsoft.public.sqlserver.programming, if it can be done with sql, someone will know there.
 
Thanks, Davie! That worked like a charm!

--B


David Browne said:
SELECT * FROM NameTable
WHERE SearchTerms Like '%lee%'
ORDER BY CASE WHEN SearchTerms Like '% Lee' then 1
WHEN SearchTerms Like 'Lee %' then 2
ELSE 3 END


Davie
Click to expand...
 
Back
Top