How can this be ordered/grouped?

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)

how can it automatically generating a ordered number

hi,

i am a newcomer and a freshman in asp.net. i am now writing a web-based system for SME as my final year project. i am going to use sql server and asp.net in C# to perform my final year project.

as asp.net is new for me, i would have some simple problems to ask.

1. in the project, i would like the system can automatically generate the enquiry number for each new order input to the system. for example today is 05 July 2006, the enquiry number would like 2006211xxxx, where 2006 is year, 211 is the day count start from 1 Jan and xxxx is the random number/ ordered number. how can i implement this? i even don't know how to generate the ordered number. could anyone help me

2. if there is an unknown test sample in each order input. as the sample number for each order is different, how can i set a flexible table that can have different number of rows for user to input the test result.

thanks

Rgds, universe

1. I suggest you can have two columns: one used to record the inserted data for the row, another for row ID. So you can create a table as following:

create table tbl_test(id int identity(1,1),RecDate smalldatetime default getdate(), Description varchar(200))
create table tbl_test1(id uniqueidentifier default newid(),RecDate smalldatetime default getdate(), Description varchar(200))

insert into tbl_test(description) select 'This is a test row'

insert into tbl_test1(description) select 'This is a test row'

2. Sorry I'm not clear about this issue

How can I use SELECT DISTINCT but maintain the original order

Say I have this result set with two fields (numbers and letters):
1 A
3 A
1 B
2 B

The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order?

When I try select distinct Number from MyResultSet it will reorder the new result set by the Number field and return
1
2
3

However, I'd like maintain the Letter order and return
1
3
2

hmm.. tricky one. I think this is not exactly what you were hoping for but it's the only thing i could come up with that works for sure.

Remember that database theory thinks or records as collection in a mathematical sense, and these do not have an order.

Good luck,

John

Code Snippet

declare @.KeepOrder table (Num int, Ord int)
declare @.Num int,
@.Ord int

declare c cursor fast_forward for

-- change this query to your table
select Num from Test
order by Let

open c
fetch next from c into @.num
set @.Ord = 1

while @.@.fetch_status = 0
begin
if not exists(select num from @.Keeporder where Num = @.Num)
begin
insert into @.Keeporder(num, ord) values(@.num, @.Ord)
set @.Ord = @.Ord + 1
end
fetch next from c into @.num
end

close c
deallocate c

select num
from @.Keeporder
order by ord

|||

Try:

create table dbo.t1 (

c1 int not null,

c2 char(1) not null

)

go

insert into dbo.t1 values(1, 'A')

insert into dbo.t1 values(3, 'A')

insert into dbo.t1 values(1, 'B')

insert into dbo.t1 values(2, 'B')

go

;with cte

as

(

select

c1,

c2,

row_number() over(partition by c1 order by c2) as rn

from

dbo.t1

)

select

c1

from

cte

where

rn = 1

order by

c2

go

drop table dbo.t1

go

AMB

|||

You could try something along these lines... Bruce

declare @.t1 table (ID int NULL, CD varchar(1) NULL)

insert into @.t1 (ID, CD) select 1, 'A'

insert into @.t1 (ID, CD) select 3, 'A'

insert into @.t1 (ID, CD) select 1, 'B'

insert into @.t1 (ID, CD) select 2, 'B'

select * from @.t1

;

WITH work_tbl AS

(

SELECT ROW_NUMBER() OVER (ORDER BY CD) AS 'RowNumber',

ID, CD

FROM @.t1

)

SELECT min(RowNumber), ID

FROM work_tbl

GROUP BY ID

ORDER BY 1

|||I really have to start looking into these new 2005 functions....|||great, thanks Bruce and HunchBack! that's exactly what I was looking for.

Is the WITH clause new for 2005? I haven't seen it before, but it's a lot easier than creating and dropping temporary tables.|||Yes, I believe the WITH is new.. it's nice for clearer code when first making a derived table.. and then referencing it multiple times in the main query. Note that you must put the semi-colon just before the WITH, strange... It won't work without that semi-colon... Bruce

How can I use SELECT DISTINCT and maintain the original order

Say I have a result set with two fields numbers and letters.

1 A
3 A
1 B
2 B

The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order?

When I try

select distinct Number from MyResultSetit will reorder the new result set by the Number field and return

1
2
3

However, I'd like maintain the Letter order and return

1
3
2

try this

create

table #test(nnint,llchar(1))

insert

into #test

values

(1,'A')

insert

into #test

values

(3,'A')

insert

into #test

values

(1,'B')

insert

into #test

values

(2,'B')

select

nnfrom(select nn,min(ll)llfrom #test

group

by nn

)

aa

order

by ll

drop

table #test