sort twice in same statement

[HomerJS]

I'm not even sure if this is possible, but it never hurts to ask.

Inside a VB 6 program I want to retrieve data from a SQL table, using a SQL statement, in a specific way. In my exmple below, I want to show all tractors for one driver first, and then the rest of the information from the table, sorted by Driver.


In table:
Driver Tractor
1 F
2 H
2 S
3 A
4 W
4 Y
5 C

want to show:
Driver Tractor
4 W
4 Y
1 F
2 H
2 S
3 A
5 C

 

[balves]

How about a UNION query.

The first SELECT just gets the special driver, the second SELECT gets all but the special driver.

 

[HomerJS]

That's a good idea, but how do I get it so it shows it in the correct order? (Driver 4 first, then all the other drivers)

 

[tlbroadbent]

You can use a Case statement in the Order By clause as follows.

Select Driver, Tractor
From Table
Order By
Case Driver
When 4 Then 1 Else 2
End,
Driver,
Tractor Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[HomerJS]

Thank you tlbroadbent for the VERY helpful tip. It works perfectly.