I have the following statement which puts all the rows that have a length of 
14 first and all other next.  But I want to sort the top select in ascending 
order and the bottom half in descending order. I know I can't do it directly 
as the ORDER BY statement needs to go after the last select.  I also thought 
about setting up 2 views each with an order statement.

But is there a way to just change this statement and do the same?

SELECT 0,FormatDescription
FROM Forms
WHERE LEN(FormatDescription) = 14
UNION
SELECT 1,FormatDescription
FROM Forms
WHERE EN(FormatDescription) <> 14

Thanks,

Tom 

There are many ways, here an example using a simple Case statement:

select * into #t from (select 1 as i union select 2 union select 3) as q

select * from
(
select 0 as r, i from #t
union select 1, i from #t
) as q
order by r,
 case when r = 0 then i else 0 end asc,
 case when r = 1 then i else 0 end desc

drop table #t

As I'm using the column r in an expression in the Order By clause, I must 
bury the whole Select Union queries into a subquery.  You could also use the 
ranking functions if you are on SQL-Server 2005+ or multiply one of your 
column with -1; if the column is numeric, of course.

-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"tshad" <toms@pdsa.com> wrote in message 
news:uekKDLvZKHA.5472@TK2MSFTNGP02.phx.gbl...
>I have the following statement which puts all the rows that have a length 
>of 14 first and all other next.  But I want to sort the top select in 
>ascending order and the bottom half in descending order. I know I can't do 
>it directly as the ORDER BY statement needs to go after the last select.  I 
>also thought about setting up 2 views each with an order statement.
>
> But is there a way to just change this statement and do the same?
>
> SELECT 0,FormatDescription
> FROM Forms
> WHERE LEN(FormatDescription) = 14
> UNION
> SELECT 1,FormatDescription
> FROM Forms
> WHERE EN(FormatDescription) <> 14
>
> Thanks,
>
> Tom
> 

I forgot to mention that you should use an UNION ALL instead of an UNION in 
your case.  The final result will be the same but the performance better.

-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
news:utS0VdvZKHA.2188@TK2MSFTNGP04.phx.gbl...
> There are many ways, here an example using a simple Case statement:
>
> select * into #t from (select 1 as i union select 2 union select 3) as q
>
> select * from
> (
> select 0 as r, i from #t
> union select 1, i from #t
> ) as q
> order by r,
> case when r = 0 then i else 0 end asc,
> case when r = 1 then i else 0 end desc
>
> drop table #t
>
> As I'm using the column r in an expression in the Order By clause, I must 
> bury the whole Select Union queries into a subquery.  You could also use 
> the ranking functions if you are on SQL-Server 2005+ or multiply one of 
> your column with -1; if the column is numeric, of course.
>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server 
> (French)
>
>
> "tshad" <toms@pdsa.com> wrote in message 
> news:uekKDLvZKHA.5472@TK2MSFTNGP02.phx.gbl...
>>I have the following statement which puts all the rows that have a length 
>>of 14 first and all other next.  But I want to sort the top select in 
>>ascending order and the bottom half in descending order. I know I can't do 
>>it directly as the ORDER BY statement needs to go after the last select. 
>>I also thought about setting up 2 views each with an order statement.
>>
>> But is there a way to just change this statement and do the same?
>>
>> SELECT 0,FormatDescription
>> FROM Forms
>> WHERE LEN(FormatDescription) = 14
>> UNION
>> SELECT 1,FormatDescription
>> FROM Forms
>> WHERE EN(FormatDescription) <> 14
>>
>> Thanks,
>>
>> Tom
>>
>
> 

That looks good.

I didn't think about using the case statement in the Order By statement.

Thanks,

Tom

"Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
news:utS0VdvZKHA.2188@TK2MSFTNGP04.phx.gbl...
> There are many ways, here an example using a simple Case statement:
>
> select * into #t from (select 1 as i union select 2 union select 3) as q
>
> select * from
> (
> select 0 as r, i from #t
> union select 1, i from #t
> ) as q
> order by r,
> case when r = 0 then i else 0 end asc,
> case when r = 1 then i else 0 end desc
>
> drop table #t
>
> As I'm using the column r in an expression in the Order By clause, I must 
> bury the whole Select Union queries into a subquery.  You could also use 
> the ranking functions if you are on SQL-Server 2005+ or multiply one of 
> your column with -1; if the column is numeric, of course.
>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server 
> (French)
>
>
> "tshad" <toms@pdsa.com> wrote in message 
> news:uekKDLvZKHA.5472@TK2MSFTNGP02.phx.gbl...
>>I have the following statement which puts all the rows that have a length 
>>of 14 first and all other next.  But I want to sort the top select in 
>>ascending order and the bottom half in descending order. I know I can't do 
>>it directly as the ORDER BY statement needs to go after the last select. 
>>I also thought about setting up 2 views each with an order statement.
>>
>> But is there a way to just change this statement and do the same?
>>
>> SELECT 0,FormatDescription
>> FROM Forms
>> WHERE LEN(FormatDescription) = 14
>> UNION
>> SELECT 1,FormatDescription
>> FROM Forms
>> WHERE EN(FormatDescription) <> 14
>>
>> Thanks,
>>
>> Tom
>>
>
> 

Why is the performance better?

Thanks,

Tom

"Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
news:O4b0rqvZKHA.1652@TK2MSFTNGP05.phx.gbl...
>I forgot to mention that you should use an UNION ALL instead of an UNION in 
>your case.  The final result will be the same but the performance better.
>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server 
> (French)
>
>
> "Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
> news:utS0VdvZKHA.2188@TK2MSFTNGP04.phx.gbl...
>> There are many ways, here an example using a simple Case statement:
>>
>> select * into #t from (select 1 as i union select 2 union select 3) as q
>>
>> select * from
>> (
>> select 0 as r, i from #t
>> union select 1, i from #t
>> ) as q
>> order by r,
>> case when r = 0 then i else 0 end asc,
>> case when r = 1 then i else 0 end desc
>>
>> drop table #t
>>
>> As I'm using the column r in an expression in the Order By clause, I must 
>> bury the whole Select Union queries into a subquery.  You could also use 
>> the ranking functions if you are on SQL-Server 2005+ or multiply one of 
>> your column with -1; if the column is numeric, of course.
>>
>> -- 
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Blog/web site: coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server 
>> (French)
>>
>>
>> "tshad" <toms@pdsa.com> wrote in message 
>> news:uekKDLvZKHA.5472@TK2MSFTNGP02.phx.gbl...
>>>I have the following statement which puts all the rows that have a length 
>>>of 14 first and all other next.  But I want to sort the top select in 
>>>ascending order and the bottom half in descending order. I know I can't 
>>>do it directly as the ORDER BY statement needs to go after the last 
>>>select. I also thought about setting up 2 views each with an order 
>>>statement.
>>>
>>> But is there a way to just change this statement and do the same?
>>>
>>> SELECT 0,FormatDescription
>>> FROM Forms
>>> WHERE LEN(FormatDescription) = 14
>>> UNION
>>> SELECT 1,FormatDescription
>>> FROM Forms
>>> WHERE EN(FormatDescription) <> 14
>>>
>>> Thanks,
>>>
>>> Tom
>>>
>>
>>
>
> 

And if your example is what you are actually doing (splitting into two cases 
depending on LEN(FormatDescription),  you can do it without a UNION at all. 
Which will be a good thing sinc the version with a UNION or UNION ALL 
statement is likely to scan the whole Forms table twice, but the following 
only once:

Select FormatLengthType, FormatDescription
From (Select Case When LEN(FormatDescription) = 14 Then 0 Else 1 End As 
FormatLengthType,
   FormatDescription
   From Forms) As x
Order By Case When FormatLengthType = 0 Then '' Else FormatDescription End 
Asc,
    Case When FormatLengthType = 1 Then '' Else FormatDescription End Desc;

Tom

"Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
news:O4b0rqvZKHA.1652@TK2MSFTNGP05.phx.gbl...
>I forgot to mention that you should use an UNION ALL instead of an UNION in 
>your case.  The final result will be the same but the performance better.
>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server 
> (French)
>
>
> "Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
> news:utS0VdvZKHA.2188@TK2MSFTNGP04.phx.gbl...
>> There are many ways, here an example using a simple Case statement:
>>
>> select * into #t from (select 1 as i union select 2 union select 3) as q
>>
>> select * from
>> (
>> select 0 as r, i from #t
>> union select 1, i from #t
>> ) as q
>> order by r,
>> case when r = 0 then i else 0 end asc,
>> case when r = 1 then i else 0 end desc
>>
>> drop table #t
>>
>> As I'm using the column r in an expression in the Order By clause, I must 
>> bury the whole Select Union queries into a subquery.  You could also use 
>> the ranking functions if you are on SQL-Server 2005+ or multiply one of 
>> your column with -1; if the column is numeric, of course.
>>
>> -- 
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Blog/web site: coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server 
>> (French)
>>
>>
>> "tshad" <toms@pdsa.com> wrote in message 
>> news:uekKDLvZKHA.5472@TK2MSFTNGP02.phx.gbl...
>>>I have the following statement which puts all the rows that have a length 
>>>of 14 first and all other next.  But I want to sort the top select in 
>>>ascending order and the bottom half in descending order. I know I can't 
>>>do it directly as the ORDER BY statement needs to go after the last 
>>>select. I also thought about setting up 2 views each with an order 
>>>statement.
>>>
>>> But is there a way to just change this statement and do the same?
>>>
>>> SELECT 0,FormatDescription
>>> FROM Forms
>>> WHERE LEN(FormatDescription) = 14
>>> UNION
>>> SELECT 1,FormatDescription
>>> FROM Forms
>>> WHERE EN(FormatDescription) <> 14
>>>
>>> Thanks,
>>>
>>> Tom
>>>
>>
>>
>
> 

The UNION has an implicit Group By, which will do nothing here because of 
the column 0/1 that you have added.

-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"tshad" <toms@pdsa.com> wrote in message 
news:efxbD3vZKHA.4268@TK2MSFTNGP05.phx.gbl...
> Why is the performance better?
>
> Thanks,
>
> Tom
>
> "Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
> news:O4b0rqvZKHA.1652@TK2MSFTNGP05.phx.gbl...
>>I forgot to mention that you should use an UNION ALL instead of an UNION 
>>in your case.  The final result will be the same but the performance 
>>better.
>>
>> -- 
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Blog/web site: coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server 
>> (French)
>>
>>
>> "Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
>> news:utS0VdvZKHA.2188@TK2MSFTNGP04.phx.gbl...
>>> There are many ways, here an example using a simple Case statement:
>>>
>>> select * into #t from (select 1 as i union select 2 union select 3) as q
>>>
>>> select * from
>>> (
>>> select 0 as r, i from #t
>>> union select 1, i from #t
>>> ) as q
>>> order by r,
>>> case when r = 0 then i else 0 end asc,
>>> case when r = 1 then i else 0 end desc
>>>
>>> drop table #t
>>>
>>> As I'm using the column r in an expression in the Order By clause, I 
>>> must bury the whole Select Union queries into a subquery.  You could 
>>> also use the ranking functions if you are on SQL-Server 2005+ or 
>>> multiply one of your column with -1; if the column is numeric, of 
>>> course.
>>>
>>> -- 
>>> Sylvain Lafontaine, ing.
>>> MVP - Windows Live Platform
>>> Blog/web site: coding-paparazzi.sylvainlafontaine.com
>>> Independent consultant and remote programming for Access and SQL-Server 
>>> (French)
>>>
>>>
>>> "tshad" <toms@pdsa.com> wrote in message 
>>> news:uekKDLvZKHA.5472@TK2MSFTNGP02.phx.gbl...
>>>>I have the following statement which puts all the rows that have a 
>>>>length of 14 first and all other next.  But I want to sort the top 
>>>>select in ascending order and the bottom half in descending order. I 
>>>>know I can't do it directly as the ORDER BY statement needs to go after 
>>>>the last select. I also thought about setting up 2 views each with an 
>>>>order statement.
>>>>
>>>> But is there a way to just change this statement and do the same?
>>>>
>>>> SELECT 0,FormatDescription
>>>> FROM Forms
>>>> WHERE LEN(FormatDescription) = 14
>>>> UNION
>>>> SELECT 1,FormatDescription
>>>> FROM Forms
>>>> WHERE EN(FormatDescription) <> 14
>>>>
>>>> Thanks,
>>>>
>>>> Tom
>>>>
>>>
>>>
>>
>>
>
>