SELECT* FROM mytable ORDER BY
LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');
I took your sample data, loaded it into a table called mytable and ran it.
Here are the results:
mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.04 sec)
mysql> create table mytable
-> (
-> names varchar(10),
-> `group` int
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into mytable values
-> ('susanita', 10),
-> ('miguelito', 5),
-> ('mafalda', 7),
-> ('manolito', 8),
-> ('libertad', 6),
-> ('felipe', 9),
-> ('guille', 8);
Query OK, 7 rows affected (0.09 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM mytable;
+-----------+-------+
| names | group |
+-----------+-------+
| susanita | 10 |
| miguelito | 5 |
| mafalda | 7 |
| manolito | 8 |
| libertad | 6 |
| felipe | 9 |
| guille | 8 |
+-----------+-------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM mytable ORDER BY
-> LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');
+-----------+-------+
| names | group |
+-----------+-------+
| felipe | 9 |
| mafalda | 7 |
| libertad | 6 |
| susanita | 10 |
| manolito | 8 |
| guille | 8 |
| miguelito | 5 |
+-----------+-------+
7 rows in set (0.01 sec)
mysql>
Give it a Try !!!
UPDATE 2011-09-06 12:33 EDT
Here is another approach:
SELECT* FROM mytable ORDER BY
IF(FIELD(`group`,9,7,6,10,8,5)=0,99999,FIELD(`group`,9,7,6,10,8,5));
This will force any groups other than 9,7,6,10,8,5 to appear at the very bottom of the query.
UPDATE 2011-09-06 14:39 EDT
mysql> SELECT names, `group`
-> FROM mytable
-> WHERE `group` IN (9,7,6,10,8,5)
-> ORDER BY find_in_set(`group`,'9,7,6,10,8,5');
+-----------+-------+
| names | group |
+-----------+-------+
| felipe | 9 |
| mafalda | 7 |
| libertad | 6 |
| susanita | 10 |
| manolito | 8 |
| guille | 8 |
| miguelito | 5 |
+-----------+-------+
7 rows in set (0.00 sec)
Hey @Nick, yours works as well against my sample data !!!
From the question, it looks like what you have written quasi-resembles what Oracle calls an anonymous code block. MySQL does not facilitate such a mechanism.
With regard to what you want to accomplish, you do not need a stored procedure.
Try assembling the SQL statement like this:
@stmt = 'SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs FROM buyerList AS b ';
@stmt = CONCAT(@stmt,'LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass ');
@stmt = CONCAT(@stmt,'LEFT JOIN pricelists p ON ');
SELECT GROUP_CONCAT(CONCAT('(p.sid = a.sid AND p.preisliste = ',sid,' AND p.ean = a.ean AND p.iln = ',pricelist',)') SEPARATOR ' OR ')
INTO @LeftJoinClause FROM preislisten;
@stmt = CONCAT(@stmt,@LeftJoinClause,' WHERE b.bid = ?');
SELECT @stmt\G
This will print out the desired query
If it is the desired query, execute it
PREPARE sql FROM @stmt;
EXECUTE sql using @param_iln;
DEALLOCATE PREPARE sql;
Give it a Try !!!
If you actually want the query to be small without hardcoding every value just write the code with a more straightforward LEFT JOIN setup
SELECT p.sid, ifnull(p.pricelist,"BASE"), count(*) AS recs FROM buyerList AS b
LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
LEFT JOIN pricelists p ON
(p.sid = a.sid AND p.preisliste = a.sid AND p.ean = a.ean)
WHERE b.bid = ?
GROUP BY p.sid,p.pricelist;
I also just noticed you have a.sid and a.ean, what table has an alias of a
?
Best Answer
I think this would work - although it's rather obfuscated:
More clearly: