sql server programming: Help with a SQL ORDER BY statement -- sql server programming

Re: Help with a SQL ORDER BY statement

Steve Kass
11/16/2003 9:28:14 PM

You could try asking in microsoft.public.access.queries, but
if access lets you use IIF in an order by clause, you can use
this:

order by
letter,
iif(number=4,0,number)

SK

[quoted text, click to view]

Help with a SQL ORDER BY statement

Jedi
11/16/2003 11:58:10 PM

Hi,

I have the following theoretical table:

Letter, Number
A, 1
A, 4
A, 3
A, 2
B, 3
B, 1
B, 4
B, 2

What I would like to do is create a SQL statement that gives me the info in
numerical order, but also with 4 always first

In pseudo terms:

select * from table1 order by 4, Number ASC

That will give me:

A, 4
A, 1
A, 2
A, 3
B, 4
B, 1
B, 2
B, 3

Is there any way of accomplishing this. I thought perhaps a union select,
but that puts all the 4's at the end, not at the beginning of each group.

Thanks,
Bodi

Re: Help with a SQL ORDER BY statement

Jedi
11/17/2003 12:58:08 AM

Thx, that probably would have worked, but I'm using Access XP, and It won't
take the CASE statement. Sorry, should have stated that before.

Bodi

[quoted text, click to view]

<SNIP>

Re: Help with a SQL ORDER BY statement

Steve Kass
11/17/2003 3:17:53 AM

Uri,

If you look at the desired output Bodi included in the post,
it's not descending order , it's 4 first, then the remaining integers
in increasing order:

4
1
2
3

SK

[quoted text, click to view]

Re: Help with a SQL ORDER BY statement

Uri Dimant
11/17/2003 7:17:10 AM

Jedi
Look at this one works for you
CREATE TABLE t1
(
COL1 CHAR(1),
COL2 TINYINT
)
INSERT INTO t1 VALUES ('A',1)
INSERT INTO t1 VALUES ('A',2)
INSERT INTO t1 VALUES ('A',3)

INSERT INTO t1 VALUES ('B',1)
INSERT INTO t1 VALUES ('B',2)
INSERT INTO t1 VALUES ('B',3)

SELECT * FROM t1 ORDER BY COL1 ASC,COL2 DESC
DROP TABLE t1

[quoted text, click to view]

Re: Help with a SQL ORDER BY statement

Alejandro Mesa
11/17/2003 9:54:44 AM

Try,

SELECT Table1.c1, Table1.c2
FROM Table1
ORDER BY Table1.c1, IIf([c2]=4,0,1), Table1.c2;

AMB

Re: Help with a SQL ORDER BY statement

Uri Dimant
11/17/2003 11:50:31 AM

Steve
You are right , my mistake.

[quoted text, click to view]

Re: Help with a SQL ORDER BY statement

Rajesh Patel
11/17/2003 1:26:07 PM

hey, you don't need to put table1.c2 in order by clause. iif condition would
be enough.

Rajesh Patel

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.