mySQL command questing.

I dont profess to be a master at SQL statements. Thanks for your help,
if anyone can give it.

I have a database that has a run column. A user can place in that
column a number, such as 1, 2, or 3.

I want to organize me list by this run number. The problem is that when
I call the query it places all the rows that have no number on the top.

For example:

SELECT DISTINCT id, Year, Make, Model, run_num FROM cars WHERE
userID='2' AND auctionID='20051108' ORDER BY run_num;

Will get me this..

+-----+------+--------------+--------------------------------+---------+
| id | Year | Make | Model | run_num |
+-----+------+--------------+--------------------------------+---------+
| 275 | 2004 | Toyota | Camry LE Sedan 4D | |
| 277 | 2004 | Toyota Truck | Sienna XLE Limited Minivan | |
| 279 | 2000 | Chrysler | Concorde LXi Sedan 4D | |
| 281 | 2004 | Nissan | 350Z Coupe 2D | |
| 284 | 2004 | Chevrolet | Cavalier Coupe 2D | |
| 285 | 2002 | Dodge Truck | Durango Sport Utility 4D | |
| 280 | 2003 | Audi | RS6 Sedan 4D | 1 |
| 276 | 2004 | Toyota Truck | Tacoma Xtra Cab | 1A |
| 278 | 1989 | Lincoln | Continental Signature Sedan 4D | 1B |
| 282 | 2004 | Chrysler | Sebring Touring Convertible 2D | 5 |
+-----+------+--------------+--------------------------------+---------+


But I want the run_num's to be on top, followed by the ones with the
blanks in the order of id perhaps.

Thanks..
-Richard

re: mySQL command questing.

>I have a database that has a run column. A user can place in that[color=blue]
>column a number, such as 1, 2, or 3.
>
>I want to organize me list by this run number. The problem is that when
>I call the query it places all the rows that have no number on the top.
>
>For example:
>
>SELECT DISTINCT id, Year, Make, Model, run_num FROM cars WHERE
>userID='2' AND auctionID='20051108' ORDER BY run_num;
>
>Will get me this..
>
>+-----+------+--------------+--------------------------------+---------+
>| id | Year | Make | Model | run_num |
>+-----+------+--------------+--------------------------------+---------+
>| 275 | 2004 | Toyota | Camry LE Sedan 4D | |
>| 277 | 2004 | Toyota Truck | Sienna XLE Limited Minivan | |
>| 279 | 2000 | Chrysler | Concorde LXi Sedan 4D | |
>| 281 | 2004 | Nissan | 350Z Coupe 2D | |
>| 284 | 2004 | Chevrolet | Cavalier Coupe 2D | |
>| 285 | 2002 | Dodge Truck | Durango Sport Utility 4D | |
>| 280 | 2003 | Audi | RS6 Sedan 4D | 1 |
>| 276 | 2004 | Toyota Truck | Tacoma Xtra Cab | 1A |
>| 278 | 1989 | Lincoln | Continental Signature Sedan 4D | 1B |
>| 282 | 2004 | Chrysler | Sebring Touring Convertible 2D | 5 |
>+-----+------+--------------+--------------------------------+---------+
>
>
>But I want the run_num's to be on top, followed by the ones with the
>blanks in the order of id perhaps.[/color]

Try:
ORDER BY run_num is null, run_num, id;

First, the lines with run_num not null go before those with run_num
null (as you wanted). Within the ties, ordering is by run_num
(which, for those with run_num null, are still tied). Then within
the still-tied groups the ordering is by id. This also means that
records with equal non-null run_num (if that's possible) will be
ordered by id.

You can get more complicated ordering by using things like if().

Gordon L. Burditt

re: mySQL command questing.

Gordon Burditt wrote:
[color=blue][color=green]
>>I have a database that has a run column. A user can place in that
>>column a number, such as 1, 2, or 3.
>>
>>I want to organize me list by this run number. The problem is that when
>>I call the query it places all the rows that have no number on the top.
>>
>>For example:
>>
>>SELECT DISTINCT id, Year, Make, Model, run_num FROM cars WHERE
>>userID='2' AND auctionID='20051108' ORDER BY run_num;
>>
>>Will get me this..
>>
>>+-----+------+--------------+--------------------------------+---------+
>>| id | Year | Make | Model | run_num |
>>+-----+------+--------------+--------------------------------+---------+
>>| 275 | 2004 | Toyota | Camry LE Sedan 4D | |
>>| 277 | 2004 | Toyota Truck | Sienna XLE Limited Minivan | |
>>| 279 | 2000 | Chrysler | Concorde LXi Sedan 4D | |
>>| 281 | 2004 | Nissan | 350Z Coupe 2D | |
>>| 284 | 2004 | Chevrolet | Cavalier Coupe 2D | |
>>| 285 | 2002 | Dodge Truck | Durango Sport Utility 4D | |
>>| 280 | 2003 | Audi | RS6 Sedan 4D | 1 |
>>| 276 | 2004 | Toyota Truck | Tacoma Xtra Cab | 1A |
>>| 278 | 1989 | Lincoln | Continental Signature Sedan 4D | 1B |
>>| 282 | 2004 | Chrysler | Sebring Touring Convertible 2D | 5 |
>>+-----+------+--------------+--------------------------------+---------+
>>
>>
>>But I want the run_num's to be on top, followed by the ones with the
>>blanks in the order of id perhaps.[/color]
>
>
> Try:
> ORDER BY run_num is null, run_num, id;
>
> First, the lines with run_num not null go before those with run_num
> null (as you wanted). Within the ties, ordering is by run_num
> (which, for those with run_num null, are still tied). Then within
> the still-tied groups the ordering is by id. This also means that
> records with equal non-null run_num (if that's possible) will be
> ordered by id.
>
> You can get more complicated ordering by using things like if().
>
> Gordon L. Burditt[/color]

Ok, Thanks for writting back.. I tried adding the additional factors at
the end, just like your line example. But it pretty much returns the same.

I understand that what your saying, and I'll continue to play with it,
but I wanted the run_num's to come first, followed by the blanks ordered
by id...

Again, thanks for pointing me in the right track, but still no go..

-Richard

re: mySQL command questing.

>>>I have a database that has a run column. A user can place in that[color=blue][color=green][color=darkred]
>>>column a number, such as 1, 2, or 3.
>>>
>>>I want to organize me list by this run number. The problem is that when
>>>I call the query it places all the rows that have no number on the top.
>>>
>>>For example:
>>>
>>>SELECT DISTINCT id, Year, Make, Model, run_num FROM cars WHERE
>>>userID='2' AND auctionID='20051108' ORDER BY run_num;
>>>
>>>Will get me this..
>>>
>>>+-----+------+--------------+--------------------------------+---------+
>>>| id | Year | Make | Model | run_num |
>>>+-----+------+--------------+--------------------------------+---------+
>>>| 275 | 2004 | Toyota | Camry LE Sedan 4D | |
>>>| 277 | 2004 | Toyota Truck | Sienna XLE Limited Minivan | |
>>>| 279 | 2000 | Chrysler | Concorde LXi Sedan 4D | |
>>>| 281 | 2004 | Nissan | 350Z Coupe 2D | |
>>>| 284 | 2004 | Chevrolet | Cavalier Coupe 2D | |
>>>| 285 | 2002 | Dodge Truck | Durango Sport Utility 4D | |
>>>| 280 | 2003 | Audi | RS6 Sedan 4D | 1 |
>>>| 276 | 2004 | Toyota Truck | Tacoma Xtra Cab | 1A |
>>>| 278 | 1989 | Lincoln | Continental Signature Sedan 4D | 1B |
>>>| 282 | 2004 | Chrysler | Sebring Touring Convertible 2D | 5 |
>>>+-----+------+--------------+--------------------------------+---------+
>>>
>>>
>>>But I want the run_num's to be on top, followed by the ones with the
>>>blanks in the order of id perhaps.[/color]
>>
>>
>> Try:
>> ORDER BY run_num is null, run_num, id;
>>
>> First, the lines with run_num not null go before those with run_num
>> null (as you wanted). Within the ties, ordering is by run_num
>> (which, for those with run_num null, are still tied). Then within
>> the still-tied groups the ordering is by id. This also means that
>> records with equal non-null run_num (if that's possible) will be
>> ordered by id.
>>
>> You can get more complicated ordering by using things like if().
>>
>> Gordon L. Burditt[/color]
>
>Ok, Thanks for writting back.. I tried adding the additional factors at
>the end, just like your line example. But it pretty much returns the same.
>
>I understand that what your saying, and I'll continue to play with it,
>but I wanted the run_num's to come first, followed by the blanks ordered
>by id...
>
>Again, thanks for pointing me in the right track, but still no go..[/color]

Ok, what's really in the field that you call 'blank'? I assumed
you meant null. Is it run_num = '' ? run_num = ' '?
run_num = ' '? run_num = ' '? You'd use that test instead instead
of run_num is null.

Gordon L. Burditt

re: mySQL command questing.

Gordon Burditt wrote:
[color=blue][color=green][color=darkred]
>>>>I have a database that has a run column. A user can place in that
>>>>column a number, such as 1, 2, or 3.
>>>>
>>>>I want to organize me list by this run number. The problem is that when
>>>>I call the query it places all the rows that have no number on the top.
>>>>
>>>>For example:
>>>>
>>>>SELECT DISTINCT id, Year, Make, Model, run_num FROM cars WHERE
>>>>userID='2' AND auctionID='20051108' ORDER BY run_num;
>>>>
>>>>Will get me this..
>>>>
>>>>+-----+------+--------------+--------------------------------+---------+
>>>>| id | Year | Make | Model | run_num |
>>>>+-----+------+--------------+--------------------------------+---------+
>>>>| 275 | 2004 | Toyota | Camry LE Sedan 4D | |
>>>>| 277 | 2004 | Toyota Truck | Sienna XLE Limited Minivan | |
>>>>| 279 | 2000 | Chrysler | Concorde LXi Sedan 4D | |
>>>>| 281 | 2004 | Nissan | 350Z Coupe 2D | |
>>>>| 284 | 2004 | Chevrolet | Cavalier Coupe 2D | |
>>>>| 285 | 2002 | Dodge Truck | Durango Sport Utility 4D | |
>>>>| 280 | 2003 | Audi | RS6 Sedan 4D | 1 |
>>>>| 276 | 2004 | Toyota Truck | Tacoma Xtra Cab | 1A |
>>>>| 278 | 1989 | Lincoln | Continental Signature Sedan 4D | 1B |
>>>>| 282 | 2004 | Chrysler | Sebring Touring Convertible 2D | 5 |
>>>>+-----+------+--------------+--------------------------------+---------+
>>>>
>>>>
>>>>But I want the run_num's to be on top, followed by the ones with the
>>>>blanks in the order of id perhaps.
>>>
>>>
>>>Try:
>>> ORDER BY run_num is null, run_num, id;
>>>
>>>First, the lines with run_num not null go before those with run_num
>>>null (as you wanted). Within the ties, ordering is by run_num
>>>(which, for those with run_num null, are still tied). Then within
>>>the still-tied groups the ordering is by id. This also means that
>>>records with equal non-null run_num (if that's possible) will be
>>>ordered by id.
>>>
>>>You can get more complicated ordering by using things like if().
>>>
>>> Gordon L. Burditt[/color]
>>
>>Ok, Thanks for writting back.. I tried adding the additional factors at
>>the end, just like your line example. But it pretty much returns the same.
>>
>>I understand that what your saying, and I'll continue to play with it,
>>but I wanted the run_num's to come first, followed by the blanks ordered
>>by id...
>>
>>Again, thanks for pointing me in the right track, but still no go..[/color]
>
>
> Ok, what's really in the field that you call 'blank'? I assumed
> you meant null. Is it run_num = '' ? run_num = ' '?
> run_num = ' '? run_num = ' '? You'd use that test instead instead
> of run_num is null.
>
> Gordon L. Burditt[/color]

That was it!! I guess it's NOT null.. buy instead blank! Wow.. I could
kiss you.

SELECT DISTINCT id, Year, Make, Model, run_num FROM cars WHERE
userID='2' AND auctionID='20051108' ORDER BY run_num='', run_num, id;

Worked! Places the sorting numbers on top, followed by the '' order by
the id.. Excellent.

I'll have to study this, but it looks like this is it!

Once, again, Thanks

-Richard