DataReader + SQL + sortorder

[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

[David Browne]

"Brent" <bbigler @ ahem-'whoopee' . com> wrote in message
news:2A*****************@newssvr27.news.prodigy.co m...

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%'"

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

[Raterus]

Try reposting this in microsoft.public.sqlserver.programming, if it can be done with sql, someone will know there.

"Brent" <bbigler @ ahem-'whoopee' . com> wrote in message news:2A*****************@newssvr27.news.prodigy.co m...

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]

Thanks, Davie! That worked like a charm!

--B
"David Browne" <davidbaxterbrowne no potted me**@hotmail.com> wrote in
message news:uO**************@tk2msftngp13.phx.gbl...

"Brent" <bbigler @ ahem-'whoopee' . com> wrote in message
news:2A*****************@newssvr27.news.prodigy.co m...

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%'"

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