SQL Query

[PeteM]

I have a table that looks like this...

Id Date Type
1 20/03/05 4
2 21/03/05 2
3 22/03/05 4
4 19/03/05 1

I wish to retrieve all rows that have a type of '4' in date order. Then I wish to retrieve all other row types in date order. I'm trying to do this in a single query. Any ideas?

Pete

[zCat]

assuming type "4" is the highest type:
SELECT * FROM table ORDER BY "type" DESC, "date"

otherwise something like:

SELECT 1 AS priority, "date", * FROM table WHERE "type" = 4
UNION
SELECT 2 AS priority, "date", * FROM table WHERE "type"!= 4
ORDER BY 1,2

[zCat]

Was that any use?

[PeteM]

Sorry, haven't been back here for a while.

This is the code that I ended up with....

$sql = " 
(SELECT * 
FROM ". ADS_ADVERTS_TABLE ." 
WHERE ad_type_code = '4' 
AND expiry_date > CURDATE() 
ORDER BY id DESC) 
UNION 
(SELECT * 
FROM ". ADS_ADVERTS_TABLE ." 
WHERE ad_type_code <> '4' 
AND expiry_date > CURDATE() 
ORDER BY id DESC) 
LIMIT ".$start.", ".$ads_config['ads_per_page'];

Thanks, Pete