MySQL ORDER BY issue

datona

know this is more a MySQL question but this board has oodles of brains here so here we go......

SELECT DISTINCT * FROM table GROUP BY paid DESC, rand()

The sql statement above is what I am using to display a full listing of all people in the database. That I want to do is list ALL people FIRST that have paid. The paid column has dates that the persons account expires. example: 2006-07-01

What I am attempting to do is NOT list by the 'paid' expire date but instead, the client_id (first column which is autoincrement) but still have the listing show the people that have 'paid' first, then all others that have not paid in random order.

Does anyone know the proper format (if one exists) that would show the 'paid' people in order of client_id instead of in order of the 'paid' date?!?!?

Thanks all for your help and/or input!

herve

Ordering on two criteria ?

SELECT DISTINCT * FROM table ORDER BY paid, client_id

Problem is, you will have something like this :

+------------+-----------+
|   paid     | client_id |
+------------+-----------+
| 2006-07-01 |   00001   |
| 2006-07-10 |   00005   |
| 2006-07-10 |   00009   |
| 2006-07-31 |   00017   |
| 2006-08-02 |   00025   |
| 2006-08-11 |   00035   |
| 2006-08-25 |   00100   |
| 2006-08-31 |   00123   |
|            |   00002   |
|            |   00003   |
|            |   00004   |
|            |   00005   |
|            |   00006   |
|            |   00008   |
+------------+-----------+

So people without 'paid' value will not be in random order.

Or may be split the problem into two smaller SQL query ?

SELECT DISTINCT * FROM table ORDER BY paid, client_id WHERE (paid != "")

SELECT DISTINCT * FROM table WHERE (paid = "") ORDER BY rand()

Roger_Ramjet

Use a Union Query to select and order the results as you want.