Help with a SQL ORDER BY statement

[Jedi]

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

 

[Rick Brandt]

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.

Create another field in the query with an expression like...

SortField: IIf([SomeField]=4,0,[SomeField])

Sort on that field while displaying the other.

 

[Tom Ellison]

Dear Jedi:

Create another table that maps 4 into 1, 1 into 2, 2 into 3, and 3
into 4. Join with that and sort by the mapping:

"Mapping"
MapFrom MapTo
4 1
1 2
2 3
3 4

SELECT Letter, Number
FROM Theoretical T
INNER JOIN Mapping M ON M.MapFrom = T.Number
ORDER BY Letter, MapTo

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Jedi]

thx, you rock Rick

<SNIP>