SOLUTION 1:
It's probably simplest, conceptually, to build a temporary table which
provides the proper ordering. The "ordering" column below is a computed
value which determines the desired orderint of the File's. Then, you
can join with an ORDER BY clause that gives the desired order.
mysql> create temporary table temp
-> select substring(FileNumber,1,6) as File,
-> min(concat(1-RUSH, PDate, PTime, substring(FileNumber,1,6)))
as ordering
-> from mytable
-> group by File;
Query OK, 7 rows affected (0.04 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from temp;
+--------+-------------------+
| File | ordering |
+--------+-------------------+
| 1-1023 | 012/0408:001-1023 |
| 1-3333 | 012/0408:301-3333 |
| 1-4444 | 012/0406:001-4444 |
| 1-6655 | 112/1108:401-6655 |
| 1-7654 | 012/0508:001-7654 |
| 1-9868 | 112/0514:001-9868 |
| 1-9999 | 012/0408:001-9999 |
+--------+-------------------+
7 rows in set (0.00 sec)
mysql> select mytable.* from
-> mytable, temp
-> where substring(mytable.FileNumber,1,6) = temp.File
-> order by ordering,RUSH desc,PDate,PTime;
+------+------------+-------+-------+
| RUSH | FileNumber | PTime | PDate |
+------+------------+-------+-------+
| 1 | 1-4444-001 | 06:00 | 12/04 |
| 1 | 1-1023-001 | 08:00 | 12/04 |
| 0 | 1-1023-002 | 14:00 | 12/09 |
| 0 | 1-1023-003 | 11:00 | 12/10 |
| 1 | 1-9999-123 | 08:00 | 12/04 |
| 0 | 1-9999-124 | 09:30 | 12/09 |
| 0 | 1-9999-125 | 10:00 | 12/15 |
| 1 | 1-3333-000 | 08:30 | 12/04 |
| 1 | 1-7654-043 | 08:00 | 12/05 |
| 0 | 1-9868-000 | 14:00 | 12/05 |
| 0 | 1-6655-021 | 08:40 | 12/11 |
| 0 | 1-6655-022 | 13:30 | 12/15 |
+------+------------+-------+-------+
12 rows in set (0.01 sec)
SOLUTION 2:
Yes, you can do it in one select statement. You join two copies of your
table,
using one to compute the ordering. I don't know how to get rid of the
ordering column in the result, as the expression won't work in the ORDER BY
clause.
mysql> select A.*,
-> min(concat(1-B.RUSH, B.PDate, B.PTime,
substring(B.FileNumber,1,6))) as ordering
-> from mytable A, mytable B
-> where substring(A.FileNumber,1,6) = substring(B.FileNumber,1,6)
-> group by A.FileNumber
-> order by ordering, A.RUSH desc, A.PDate, A.PTime;
+------+------------+-------+-------+-------------------+
| RUSH | FileNumber | PTime | PDate | ordering |
+------+------------+-------+-------+-------------------+
| 1 | 1-4444-001 | 06:00 | 12/04 | 012/0406:001-4444 |
| 1 | 1-1023-001 | 08:00 | 12/04 | 012/0408:001-1023 |
| 0 | 1-1023-002 | 14:00 | 12/09 | 012/0408:001-1023 |
| 0 | 1-1023-003 | 11:00 | 12/10 | 012/0408:001-1023 |
| 1 | 1-9999-123 | 08:00 | 12/04 | 012/0408:001-9999 |
| 0 | 1-9999-124 | 09:30 | 12/09 | 012/0408:001-9999 |
| 0 | 1-9999-125 | 10:00 | 12/15 | 012/0408:001-9999 |
| 1 | 1-3333-000 | 08:30 | 12/04 | 012/0408:301-3333 |
| 1 | 1-7654-043 | 08:00 | 12/05 | 012/0508:001-7654 |
| 0 | 1-9868-000 | 14:00 | 12/05 | 112/0514:001-9868 |
| 0 | 1-6655-021 | 08:40 | 12/11 | 112/1108:401-6655 |
| 0 | 1-6655-022 | 13:30 | 12/15 | 112/1108:401-6655 |
+------+------------+-------+-------+-------------------+
12 rows in set (0.03 sec)
From: "Roger Davis" <[EMAIL PROTECTED]>
To: "Mysql" <[EMAIL PROTECTED]>
Subject: Order By or Group By Help Please
Date: Thu, 5 Dec 2002 19:54:57 -0500
Ok, I will try this one again.
I need some help on a select if it is possible. Take for Example the
following data.
--------------------------------------
| RUSH | FileNumber | PTime | PDate |
--------------------------------------
| 1 | 1-1023-001 | 08:00 | 12/04 |
| 1 | 1-4444-001 | 06:00 | 12/04 |
| 0 | 1-1023-002 | 14:00 | 12/09 |
| 1 | 1-3333-000 | 08:30 | 12/04 |
| 0 | 1-1023-003 | 11:00 | 12/10 |
| 1 | 1-9999-123 | 08:00 | 12/04 |
| 0 | 1-9999-124 | 09:30 | 12/09 |
| 0 | 1-6655-021 | 08:40 | 12/11 |
| 0 | 1-9999-125 | 10:00 | 12/15 |
| 1 | 1-7654-043 | 08:00 | 12/05 |
| 0 | 1-6655-022 | 13:30 | 12/15 |
| 0 | 1-9868-000 | 14:00 | 12/05 |
--------------------------------------
To end up grouped like this.
--------------------------------------
| RUSH | FileNumber | PTime | PDate |
--------------------------------------
| 1 | 1-4444-001 | 06:00 | 12/04 |
| 1 | 1-1023-001 | 08:00 | 12/04 |
| 0 | 1-1023-002 | 14:00 | 12/09 |
| 0 | 1-1023-003 | 11:00 | 12/10 |
| 1 | 1-9999-123 | 08:00 | 12/04 |
| 0 | 1-9999-124 | 09:30 | 12/09 |
| 0 | 1-9999-125 | 10:00 | 12/15 |
| 1 | 1-3333-000 | 08:30 | 12/04 |
| 1 | 1-7654-043 | 08:00 | 12/05 |
| 0 | 1-9868-000 | 14:00 | 12/05 |
| 0 | 1-6655-021 | 08:40 | 12/11 |
| 0 | 1-6655-022 | 13:30 | 12/15 |
--------------------------------------
Basically, I need this.
If it is a RUSH (1), It needs to be first
If there are duplicate files (Like 1-9999 (it has 2 dups) or 1-1023 (2
dups)) and one of them is a rush
They need to be grouped together.
Then sort it by PDate and PTime
I have tried several group by combinations but no luck so far. Any help
will be appreciated.
Thanks
Roger
Oh yeah, SQL, MySQL
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
