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='2005 1108' 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 1246
>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='2005 1108' 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='2005 1108' 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='2005 1108' 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='2005 1108' 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='2005 1108' 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: relocations remain against allocatable but non-writable
sections
collect2: ld returned 1 exit status
error: command 'gcc' failed with exit status 1
|
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 be able to place the PHP file on the
server, and I guess you probably can't do that either. Talk about catch
22...
The only other way I can think of is to install MySQL on a machine you
control, then import the data there using the method I...
|
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 Mandrake 9.2
and am reinstalling everything. This thing is kicking my ass and I
can't seem to get past it. I could really use some help if anybody has any.
Just for reference, this was my primary source for information on
installation and...
|
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 Mandrake 9.2
and am reinstalling everything. This thing is kicking my ass and I
can't seem to get past it. I could really use some help if anybody has any.
Just for reference, this was my primary source for information on
installation and...
|
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 version of this document can be found at:
http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download
| |
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 own LAN,
the driver tells me it cannot make the connection. Here are the ODBC
driver connection parms:
Data Source Name: (free field - name my "my linux box" will do nicely)
Host/Server Name (or IP) - something like MSQLUserName@ServerName.net...
|
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. Despite
entering the required lines to "my.ini" (the new my.cnf) through notepad
AND MySQL Administrator, the cache does not work.
So, today I took a peek at the 'Health' tab in MySQL Administrator.
|
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 following: runs
several queries against different tables in a database; returns several
lists of pictures being used in the database; removes any obsolete
images from specific directories. The script is bash shell script.
The problem is that...
|
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 > table_products.sql";
system($command);
This works perfect and it gives me a file with a drop-table,
create-table and all the insert values.
|
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 through this without much trouble. Programming knowledge is not required.
Index
What is SQL?
Why MySQL?
Installing MySQL.
Using the MySQL command line interface
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |