Specific order of query results - CASE function

[christh]

Hi there

 

Hopefully somebody maybe able to help or point me in the right direction.

 

I am trying to get a specific record from a query to be the first record in the result.

 

In short I have

 

$result = mysql_query("
			SELECT * 
			FROM artist, gallery
			WHERE artist.artist_id = gallery.picture_artist AND gallery.profile like 'profile' AND artist.artist.cat='7'
			");

 

Now that will give me results containing the field 'artist_id' and what I would like to be able to do is to have a specific artist_id as the first result in the results table - say artist_id 57.

 

From what I have read, I think I should be using the CASE function but am struggling to find the right syntax.  In the results, the 'artist_id' field serves no purpose so I'm guessing I could use the CASE function to change the specified artist_id to -9999 and then use ORDER BY to get that record to the top...?

 

Something like

 

$result = mysql_query("
			SELECT * 
			FROM artist, gallery
                                WHERE artist.artist_id = gallery.picture_artist AND gallery.profile like 'profile' AND artist.artist.cat='7'
			CASE WHEN artist.artist_id =57
   				THEN artist.artist_id='-9999'
			ORDER BY artist.artist_id DESC 
			");

 

Could somebody confirm if this is the best/correct approach?  And secondly, help with the syntax on the CASE function as I'm obviously not implementing it correctly.

 

Many thanks in advance and apologies if the above makes little sense - I'll try and be clearer where necessary.

 

Thanks

 

Chris

 

[kicken]

Use the CASE statement in the ORDER BY clause to adjust the value during ordering.  I believe this should work:

 

SELECT * 
FROM artist, gallery
WHERE artist.artist_id = gallery.picture_artist AND gallery.profile like 'profile' AND artist.artist.cat='7'
ORDER BY
  CASE WHEN artist.artist_id=57 THEN -1 ELSE artist.artist_id END

 

[christh]

Hi kicken

 

Thanks for the answer - works perfectly.

 

Chris