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 4 1228
>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
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
>>>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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Cathy Hui |
last post by:
Do u know why do i get the following message when trying to build the
MySql-Python (1.2.0) on my Solaris 8 system? (with mysql 4.0.21 and
python 2.4). thanks!
error mesg:
ld: fatal:...
|
by: Donald Tyler |
last post by:
Then the only way you can do it that I can think of is to write a PHP script
to do basically what PHPMyAdmin is trying to do but without the LOCAL in
there.
However to do that you would need to...
|
by: Mark Adams |
last post by:
I am a relative newbie to MySQL. I had a Postfix+Courier+MySQL mail
server running for several months. It took me a week or so to get it up
and running in September. Now, I did a clean upgrade to...
|
by: Mark Adams |
last post by:
I am a relative newbie to MySQL. I had a Postfix+Courier+MySQL mail
server running for several months. It took me a week or so to get it up
and running in September. Now, I did a clean upgrade to...
|
by: Mike Chirico |
last post by:
Interesting Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (GPU Free Documentation License) 2004
Last Updated: Mon Jun 7 10:37:28 EDT 2004
The latest...
|
by: MLH |
last post by:
Using MS Access, I have attached to MySQL servers in other states
and other countries on the other side of my router. But when I use the
MySQL ODBC driver 3.51 to connect to a MySQL server on my...
|
by: Good Man |
last post by:
Hi there
I've noticed some very weird things happening with my current MySQL setup
on my XP Laptop, a development machine.
For a while, I have been trying to get the MySQL cache to work....
|
by: the.natalie |
last post by:
Hi.
I am a newbie to mysql, cron, and shell scripting, so please bear with
me. I have a script that is used for updating an image directory based
on contents in a database. The script does the...
|
by: apostolosl |
last post by:
Hi there.
I need to update a remote database from a local one. I use mysqldump in
my php page in order to create a file for each table, like this:
$command="mysqldump table_products >...
|
by: Atli |
last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users.
Anyone should be able to get...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |