0

Just have a tricky blockING with MySQL. I've 3 tables :

TV

| TV_ID  |  TV_name  |
----------------------
|    1   |    HBO    |
|    2   |    BBC    |
|    3   |  Fox news |
----------------------
----------------------
----------------------

Emission

| E_ID | E_TV_ID |   E_NAME   |
-------------------------------
|   1  |    1    |   Weather  |
|   2  |    1    |   News     |
|   3  |    1    |   FAKE1    |
|   4  |    1    |   FAKE2    |
|   5  |    1    |   FAKE3    |
|   6  |    1    |   FAKE4    |
|   7  |    2    |   FAKE5    |
|   8  |    2    |   FAKE6    |
|   9  |    2    |   FAKE7    |
|   10 |    2    |   FAKE8    |
|   11 |    2    |   FAKE9    |
|   12 |    2    |   FAKE10   |
|   13 |    2    |   FAKE11   |
|   14 |    3    |   FAKE12   |
|   15 |    3    |   FAKE13   |
|   16 |    3    |   FAKE14   |
|   17 |    3    |   FAKE15   |
|   18 |    3    |   FAKE16   |
|   19 |    3    |   FAKE17   |
|   20 |    3    |   FAKE18   |
-------------------------------
-------------------------------
-------------------------------

Replay

| R_ID | R_E_ID |  R_DATE  | R_URL_REPLAY |
-------------------------------------------
|  1   |   1    | 20150431 |     URL1     |
|  2   |   20   | 20150431 |     URL2     |
|  3   |   19   | 20150431 |     URL3     |
|  4   |   2    | 20150431 |     URL4     |
|  5   |   7    | 20150431 |     URL5     |
|  6   |   16   | 20150430 |     URL6     |
|  7   |   10   | 20150430 |     URL7     |
|  8   |   1    | 20150430 |     URL8     |
|  9   |   4    | 20150430 |     URL9     |
|  10  |   9    | 20150430 |     URL10    |
|  11  |   19   | 20150429 |     URL11    |
|  12  |   2    | 20150429 |     URL12    |
|  13  |   1    | 20150429 |     URL13    |
|  14  |   12   | 20150429 |     URL14    |
-------------------------------------------
-------------------------------------------
-------------------------------------------

And I want to create ONLY ONE query to get 3rd last emission of each TV, order by date and TV (if possible). So for this exemple, I've 3 TV. 3*3 = 9 emissions, like :

| TV_ID  |  E_NAME   | R_URL_REPLAY |
-------------------------------------
|    1   |  Weather  |     URL1     |
|    1   |   FAKE2   |     URL4     |
|    1   |   FAKE6   |     URL8     |
|    2   |   FAKE3   |     URL5     |
|    2   |   FAKE8   |     URL7     |
|    2   |   FAKE7   |     URL10    |
|    3   |   FAKE18  |     URL2     |
|    3   |   FAKE17  |     URL3     |
|    3   |   FAKE14  |     URL6     |

I've try many solution (INNER JOIN -- SELECT .. FROM ( SELECT ...) -- Use var @:= -- Sub-sub-sub-sub query ) but not works.. Only works if I use UNION, but I've more than 20 TV, and write 20 UNION is really urgly..

If you have suggestion,

Thanks in advance,

magexcustomer
  • 35
  • 6

1 Answers1

0

It's not straightforward, but in a nutshell, sort your replays by tv and date, then rank them, then select those that match your rank criteria.

select * 
  from (
    select if(@prev = e_tv_id, @rank := @rank +1, @rank := 1 and @prev := e_tv_id) as rank, q.*
      from ( 
        select e.e_tv_id, r_date, r_url_replay
          from emission e
          join (select @prev := 0, @rank := 1) q
            inner join replay r
            on r.r_e_id = e.e_id
        order by e.e_tv_id asc, r.r_date desc
     ) q
  ) qq
where rank <=3 ;

demo here

pala_
  • 8,901
  • 1
  • 15
  • 32