[SOLVED] table select by order sequence problem

[rickoverman]

Hello Everyone,

 

I have a problem with a query

 

I have the following table

 

tb1:

id name pricechart ordernr   srvid

---------------------------------------------

1 rick hotel   GRP             2     1

2 rick hotel   PRI               1       1

3 Jonas Hotel GRP              2      2

4 Jonas Hotel PRI             1     2

5 Allan Hotel   PRI             1       1

 

 

now what i want to have as result are all the distinct names with a specific pricechart, the pricechart has to be either PRI or GRP and followed up by some sequence.  like for example if GRP pricechart is not available then display the PRI.

 

a result for the above described sequence (GRP -> PRI ) will result in the following:

 

id name pricechart ordernr   srvid

---------------------------------------------

1 rick hotel   GRP             2     1

3 Jonas Hotel GRP              2      2

5 Allan Hotel   PRI             1       1

 

OR if the sequence is turned around  like display first PRI if the pricechart is there else use the GRP    (PRI -> GRP) the folllwoing result would be displayed:

 

id name pricechart ordernr   srvid

---------------------------------------------

 

2 rick hotel   PRI               1       1

4 Jonas Hotel PRI             1     2

5 Allan Hotel   PRI             1       1

 

 

Can anyone help me?  I tried it now multiple times myself but without any luck

 

Thanks in advance!

Rick

 

 

 

 

 

[Wildbug]

This probably isn't the best way, but it works in this case.

 

mysql> SELECT * FROM hotels;
+------+-------------+------------+---------+-------+
| id   | name        | pricechart | ordernr | srvid |
+------+-------------+------------+---------+-------+
|    1 | Rick Hotel  | PRI        |       1 |     1 |
|    2 | Rick Hotel  | GRP        |       2 |     1 |
|    3 | Jonas Hotel | GRP        |       2 |     2 |
|    4 | Jonas Hotel | PRI        |       1 |     2 |
|    5 | Allan Hotel | PRI        |       1 |     1 |
+------+-------------+------------+---------+-------+
5 rows in set (0.00 sec)

mysql> SELECT hotels.* FROM hotels JOIN (SELECT name,LEFT(GROUP_CONCAT(pricechart ORDER BY pricechart='GRP' DESC SEPARATOR ''),3) AS pc FROM hotels GROUP BY name) AS h2 ON (hotels.name=h2.name AND hotels.pricechart=h2.pc);
+------+-------------+------------+---------+-------+
| id   | name        | pricechart | ordernr | srvid |
+------+-------------+------------+---------+-------+
|    2 | Rick Hotel  | GRP        |       2 |     1 |
|    3 | Jonas Hotel | GRP        |       2 |     2 |
|    5 | Allan Hotel | PRI        |       1 |     1 |
+------+-------------+------------+---------+-------+
3 rows in set (0.00 sec)

mysql> SELECT hotels.* FROM hotels JOIN (SELECT name,LEFT(GROUP_CONCAT(pricechart ORDER BY pricechart='PRI' DESC SEPARATOR ''),3) AS pc FROM hotels GROUP BY name) AS h2 ON (hotels.name=h2.name AND hotels.pricechart=h2.pc);
+------+-------------+------------+---------+-------+
| id   | name        | pricechart | ordernr | srvid |
+------+-------------+------------+---------+-------+
|    1 | Rick Hotel  | PRI        |       1 |     1 |
|    4 | Jonas Hotel | PRI        |       1 |     2 |
|    5 | Allan Hotel | PRI        |       1 |     1 |
+------+-------------+------------+---------+-------+
3 rows in set (0.00 sec)

[rickoverman]

Hi Wildbug,

 

didn't try the group_concat yet but will today.

 

Thanks for your reply!

[Barand]

or

SELECT h.* FROM hotel h
INNER JOIN (SELECT name, MIN(pricechart) as pc FROM hotel GROUP BY name) as x
ON h.name = x.name AND h.pricechart = x.pc

To give PRI priority, change MIN to MAX

[rickoverman]

Hi all,

 

I have been busy with the code you all have provided, and i believe i got it to work now.

 

I adapted the query so that it can handle more than 2 pricecharts in a sequence. and only returns the specified pricecharts.

 

SELECT table4.* FROM table4 JOIN 
(
        SELECT 
        srvid,
        MIN(field(pricecart,'GRP','PRI')) as tc
        FROM table4 GROUP BY srvid
) AS h2 ON (
      (table4.pricecart='PRI'   OR  table4.pricecart='GRP') AND    
      table4.srvid=h2.srvid AND field(table4.pricecart,'GRP','PRI')=h2.tc
);

 

Thanks for all the help!