mySQL command questing.

[Richard Ragon]

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

[Gordon Burditt]

>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

[Richard Ragon]

Gordon Burditt wrote:

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

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

[Gordon Burditt]

>>>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

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..

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

[Richard Ragon]

Gordon Burditt wrote:

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

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..

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

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