bring certain values to top 1

[thompom]

hi this might not be an sql question but here goes

the folowing sql groups all makes in the table and counts the total of each make

SELECT count(stock.make) AS makecount, stock.make AS makeid, stock.make AS makequick
FROM stock
GROUP BY makeid

is there a way to get certain makes at the top of the recordset - these are vw and hyundai?

i dont want to put a special orderby column in the table
because this will be a regular update from another company who produces the table and would mean adding the order column every time the table is updated.

 

[towerbase]

Yes, use the CASE function.

Something like this should sort vw and hyundai to the top and the rest in alphabetical order:

SELECT count(stock.make) AS makecount, stock.make AS makeid, stock.make AS makequick,
CASE stock.make 
  WHEN 'vw' THEN 1 
  WHEN 'hyundai' THEN 2 
ELSE 3 
END AS id

FROM stock
GROUP BY makeid
ORDER BY id, makeid

Andrew
Hampshire, UK

 

[sleipnir214]

Data in MySQL tables, like the data in the tables of any relational database, has no intrinsic ordering. The only order placed on the data is when it is being fetched from the database.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[thompom]

have * on me - thanks towerbase