Custom sorting in sql query

[pb2000]

Hello,
I learn SQL SERVER.

I wrote:

SELECT
"All"
UNION SELECT DISTINCT
KindsColumn From ProductsTable

I obtain:
----------------------
1. AA_kind
2. AB_kind
3. All
4. B_kind
5. Z_kind

But i take it into DropDownList where 'All' should be at the top.

I would like to obtain (where 2-5 are sorted)
---------------------------
1. All
2. AA_kind
3. AB_kind
4. B_kind
5. Z_kind

Thank You fot the answer. It will be nice lesson for me.

[robjens]

Ah. I had to see for a bit what you wanted exactly. Thought at first you wanted capital and small case letters sorted in a certain sequence. This kind of "sorting" you won't get the result though. I assume you'd want "All" as the value of a ComboBox/dropdown list to have it run a function lateron perhaps? Else I don't see any reason you'd have that value "All" in there, just add it later to a control using .Add method. If it's pure database we are talking here, you will need to add another field integer type and sort on that like All (1) AA_kind (2) etc. Then just hide the column. You'd still be able to sort on the ABC just enforce All to the top.

[mafaisal]

Hello,
I learn SQL SERVER.

I wrote:

SELECT
"All"
UNION SELECT DISTINCT
KindsColumn From ProductsTable

I obtain:
----------------------
1. AA_kind
2. AB_kind
3. All
4. B_kind
5. Z_kind

But i take it into DropDownList where 'All' should be at the top.

I would like to obtain (where 2-5 are sorted)
---------------------------
1. All
2. AA_kind
3. AB_kind
4. B_kind
5. Z_kind

Thank You fot the answer. It will be nice lesson for me.

That Means,

Select fld From (
Select 1 Flag,'All' Fld Union All Select 2 Flag, KindsColumn fld 
From ProductsTable ) a 
Order by Flag,Fld

This may be help you

Faisal M A

[ck9663]

Try this:

SELECT 0 as sortorder,
"All"
UNION SELECT DISTINCT 1 as sortorder
KindsColumn From ProductsTable
order by 1, 2

Happy Coding!!!

~~ CK