I have the following four columns:
1 3 ok 1
1 2 ok 1
1 1 server error 3
2 3 ok 1
2 2 ok 1
2 1 ok 1
9 3 ok 1
9 2 ok 1
9 1 not found 3
13 3 ok 1
13 2 ok 1
13 1 ok 1
Their data types are int, int, string, int. Call the columns
groupid, ordinalid, status, quantstatus.
I want the above to sort this way:
9 3 ok 1
9 2 ok 1
9 1 not found 3
1 3 ok 1
1 2 ok 1
1 1 server error 3
13 3 ok 1
13 2 ok 1
13 1 ok 1
2 3 ok 1
2 2 ok 1
2 1 ok 1
Notice those with a 3 in quantstatus appear first. They are ordered by:
groupid, ordinalid desc
Any suggestions?
Thanks,
BrettTry,
select
*
from
t
order by
case when left(groupid, 1) = '9' then 0 else 1 end asc,
ltrim(groupid) asc,
ordinalid desc
AMB
"Brett" wrote:
> I have the following four columns:
> 1 3 ok 1
> 1 2 ok 1
> 1 1 server error 3
> 2 3 ok 1
> 2 2 ok 1
> 2 1 ok 1
> 9 3 ok 1
> 9 2 ok 1
> 9 1 not found 3
> 13 3 ok 1
> 13 2 ok 1
> 13 1 ok 1
> Their data types are int, int, string, int. Call the columns
> groupid, ordinalid, status, quantstatus.
> I want the above to sort this way:
> 9 3 ok 1
> 9 2 ok 1
> 9 1 not found 3
> 1 3 ok 1
> 1 2 ok 1
> 1 1 server error 3
> 13 3 ok 1
> 13 2 ok 1
> 13 1 ok 1
> 2 3 ok 1
> 2 2 ok 1
> 2 1 ok 1
> Notice those with a 3 in quantstatus appear first. They are ordered by:
> groupid, ordinalid desc
> Any suggestions?
> Thanks,
> Brett
>
>|||That starts off OK. The first four groups are fine. But when a larger set
a data is considered, the query breaks down. Notice where group 19 and 20
are. They're column four has a 3. They should be where 13 and 14 are. How
can this be fixed?
9 3 ok 1
9 2 ok 1
9 1 na 3
1 3 ok 1
1 2 ok 1
1 1 na 3
10 3 ok 1
10 2 ok 1
10 1 na 3
11 3 ok 1
11 2 ok 1
11 1 na 3
13 3 ok 1
13 2 ok 1
13 1 ok 1
14 3 ok 1
14 2 ok 1
14 1 ok 1
17 3 ok 1
17 2 ok 1
17 1 ok 1
18 3 ok 1
18 2 ok 1
18 1 ok 1
19 3 ok 1
19 2 ok 1
19 1 na 3
2 3 ok 1
2 2 ok 1
2 1 ok 1
20 3 ok 1
20 2 ok 1
20 1 na 3
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9C230AE9-FCFB-4EFE-A559-C99868B2BE66@.microsoft.com...
> Try,
> select
> *
> from
> t
> order by
> case when left(groupid, 1) = '9' then 0 else 1 end asc,
> ltrim(groupid) asc,
> ordinalid desc
>
> AMB
> "Brett" wrote:
>|||Brett,
Try
select
groupid, ordinalid, status, quantstatus
from T as T1
order by
case where exists (
select * from T as T2
where T2.groupid = T1.groupid
and T2.quantstatus = 3
) then 0 else 1 end,
groupid, ordinalid desc
Steve Kass
Drew University
Brett wrote:
>I have the following four columns:
>1 3 ok 1
>1 2 ok 1
>1 1 server error 3
>2 3 ok 1
>2 2 ok 1
>2 1 ok 1
>9 3 ok 1
>9 2 ok 1
>9 1 not found 3
>13 3 ok 1
>13 2 ok 1
>13 1 ok 1
>Their data types are int, int, string, int. Call the columns
>groupid, ordinalid, status, quantstatus.
>I want the above to sort this way:
>9 3 ok 1
>9 2 ok 1
>9 1 not found 3
>1 3 ok 1
>1 2 ok 1
>1 1 server error 3
>13 3 ok 1
>13 2 ok 1
>13 1 ok 1
>2 3 ok 1
>2 2 ok 1
>2 1 ok 1
>Notice those with a 3 in quantstatus appear first. They are ordered by:
>groupid, ordinalid desc
>Any suggestions?
>Thanks,
>Brett
>
>
>|||The CASE statement isn't fully constructed.
CASE input_expression
WHEN when_expression THEN result_expression
END
You need an expression before and after the WHEN part. Right now you only
have one expression.
Thanks,
Brett
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23skWdcACFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Brett,
> Try
> select
> groupid, ordinalid, status, quantstatus
> from T as T1
> order by
> case where exists (
> select * from T as T2
> where T2.groupid = T1.groupid
> and T2.quantstatus = 3
> ) then 0 else 1 end,
> groupid, ordinalid desc
> Steve Kass
> Drew University
> Brett wrote:
>|||>> You need an expression before and after the WHEN part. Right now you
Not quite. Apart from Steve's typo with using "where" instead of WHEN, the
case expression is valid. You may want to check out SQL Server Books Online
for searched case, under the topic CASE.
ORDER BY CASE WHEN EXISTS (
SELECT * FROM T AS T2
WHERE T2.groupid = T1.groupid
AND T2.quantstatus = 3
) THEN 0 ELSE 1 END, groupid DESC, ordinalid DESC ;
Anith|||Sorry, didn't catch that. It works Perfect!
What exactly does this part do:
THEN 0 ELSE 1 END
Alejandro used it also.
Thanks,
Brett
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OlxPpABCFHA.1392@.tk2msftngp13.phx.gbl...
> Not quite. Apart from Steve's typo with using "where" instead of WHEN, the
> case expression is valid. You may want to check out SQL Server Books
> Online for searched case, under the topic CASE.
> ORDER BY CASE WHEN EXISTS (
> SELECT * FROM T AS T2
> WHERE T2.groupid = T1.groupid
> AND T2.quantstatus = 3
> ) THEN 0 ELSE 1 END, groupid DESC, ordinalid DESC ;
> --
> Anith
>|||Thanks for the catch, Anith!
SK
Anith Sen wrote:
>Not quite. Apart from Steve's typo with using "where" instead of WHEN, the
>case expression is valid. You may want to check out SQL Server Books Online
>for searched case, under the topic CASE.
>ORDER BY CASE WHEN EXISTS (
> SELECT * FROM T AS T2
> WHERE T2.groupid = T1.groupid
> AND T2.quantstatus = 3
> ) THEN 0 ELSE 1 END, groupid DESC, ordinalid DESC ;
>
>|||On Mon, 31 Jan 2005 22:52:42 -0500, Brett wrote:
>What exactly does this part do:
>THEN 0 ELSE 1 END
Hi Brett,
It's part of the CASE expression. If the WHEN clause (containing the
subquery with exists) evaluates to true, the CASE expression will evaluate
to 0 (the THEN value); otherwise, it'll return 1 (the ELSE part). This
value (0 or 1) won't be in the result set, but it's the first value in the
ORDER BY clause. So after ordering, all rows for which the EXISTS subquery
is true will be first (sorted on the value 0); all others last (sorted on
the value 1).
Second and third argument to the ORDER BY clause are responsible for
fixing the order within these two sub-groups.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
anu_ank wrote:
B_E_CANTCANCEL(0*80040E15).(System.Data)" .After this I am unable to proceed .