473,289 Members | 1,884 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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
Nov 23 '05 #1
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
Nov 23 '05 #2
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
Nov 23 '05 #3
>>>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
Nov 23 '05 #4
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

Nov 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
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:...
0
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...
3
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...
0
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...
0
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...
16
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...
1
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....
3
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...
3
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 >...
6
Atli
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...
0
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...
0
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...
0
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 =...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
0
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...
1
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)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.