Help with a SQL ORDER BY statement

  • Thread starter Thread starter Jedi
  • Start date Start date
J

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
 
Jedi said:
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.
Click to expand...

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

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

Sort on that field while displaying the other.
 
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
 
Back
Top