[MySQL] Сложный непонятный запрос

1) SELECT content.* FROM content JOIN photo_albums ON content.id_co=photo_albums.id_co ORDER BY content.id_co DESC

?

сделал!

SELECT d.id_co,

d.id_pa  from (

select



n.id_co,

e.id_pa



FROM content as n



LEFT JOIN (

select

  id_co,id_pa

from

  photo_albums



limit 10

)  as c

ON c.id_co = n.id_co



INNER JOIN (

select

  id_co,id_pa

from

  photo_albums



limit 10

)  as e

ON n.id_co = e.id_co AND c.id_pa < e.id_pa

  order by created desc



) `a`

 JOIN (

select

 id_co,id_pa

 from  photo_albums

  where id_co in (SELECT id_co from photo_albums)

   limit 50

) as d



ORDER by d.id_co desc

вывод:

+-------+-------+

| id_co | id_pa |

+-------+-------+

|   120 |    87 | 

|   120 |    88 | 

|   120 |    90 | 

|   120 |    91 | 

|   119 |    74 | 

|   118 |    71 | 

|   118 |    72 | 

|   118 |    75 | 

|   118 |    76 | 

|   118 |    77 | 

|   118 |    78 | 

|   117 |    89 | 

|   117 |    25 | 

|   117 |    26 | 

|   116 |    73 | 

|   115 |    34 | 

|   115 |    42 | 

|   115 |    79 | 

|   113 |    85 | 

|   113 |    86 | 

|   113 |    59 | 

|   113 |     9 | 

|   113 |    61 | 

|   113 |    48 | 

|   113 |    49 | 

|   113 |    50 | 

|   113 |    52 | 

|   113 |    80 | 

|   113 |    53 | 

|   113 |    81 | 

|   113 |    82 | 

|   113 |    83 | 

|   113 |    84 | 

|   112 |    45 | 

|   111 |    60 | 

|   110 |    62 | 

|   110 |    44 | 

|   109 |    58 | 

|   109 |    54 | 

|   109 |    55 | 

|   109 |    56 | 

|   109 |    57 | 

|   107 |    63 | 

|   106 |    64 | 

|    67 |    10 | 

|    66 |     3 | 

|    66 |    12 | 

|    66 |    13 | 

|    47 |     2 | 

|    47 |     5 | 

+-------+-------+

50 rows in set (0.00 sec)

работает как надо!

ВОПРОС:

хотел сделать еще по другому, чтобы в выводе было максимум 10 уникальных id_co c таблицы content

т.е. хочу сделать по страничный вывод и чтобы было максимум 10 id_co на странице (ну и к нему привязано какое-то количество id_pa, см.условие)

вот никак не могу понят ькуда прикрутить LIMIT, чтобы было 10 уникальных id_co

короче, это все порнография

не получилоcь ничего

НО я сделал запрос в котором вывел 10 элементов:

 select t1.id_co  
 	   from content AS t1
 INNER join photo_albums as t2
        ON t1.id_co = t2.id_co
 INNER join photo_files as t3
        ON t2.id_pa = t3.id_pa
        
        GROUP BY t1.id_co 
        ORDER BY t2.created desc
LIMIT 10

а потом сконструировал такой:

( select t1.id_pa,
 						 t1.id_co,
   (SELECT min(t6.id_pf) FROM photo_files AS t6 WHERE t6.id_pa = t1.id_pa) as id_pf_min,                                             
        (SELECT MAX(t7.id_pf) as pf FROM photo_files AS t7 WHERE t7.id_pa = t1.id_pa ) as id_pf_max  
         						  
 	   from photo_albums AS t1
    where t1.id_co = '666'
    ORDER by t1.id_co desc
    limit 5

 UNION ALL

( select t1.id_pa,
 						 t1.id_co,
   (SELECT min(t6.id_pf) FROM photo_files AS t6 WHERE t6.id_pa = t1.id_pa) as id_pf_min,                                             
        (SELECT MAX(t7.id_pf) as pf FROM photo_files AS t7 WHERE t7.id_pa = t1.id_pa ) as id_pf_max  
         						  
 	   from photo_albums AS t1
    where t1.id_co = '667'
    ORDER by t1.id_co desc
    limit 5

полностью, на perl:

my $a = $DBH->selectall_arrayref("

	 select t1.id_co  
 	   from content AS t1
 INNER join photo_albums as t2
        ON t1.id_co = t2.id_co
 INNER join photo_files as t3
        ON t2.id_pa = t3.id_pa
        
        GROUP BY t1.id_co 
        ORDER BY t2.created desc

", { Slice => {} } );



 my $sql_p = join " UNION ALL ", map {"( select t1.id_pa,
 						 t1.id_co,
   (SELECT min(t6.id_pf) FROM photo_files AS t6 WHERE t6.id_pa = t1.id_pa) as id_pf_min,                                             
        (SELECT MAX(t7.id_pf) as pf FROM photo_files AS t7 WHERE t7.id_pa = t1.id_pa ) as id_pf_max  
         						  
 	   from photo_albums AS t1
    where t1.id_co = ".$_->{id_co}."
    ORDER by t1.id_co desc
    limit 5 ) " } @$a;


my $aa = $DBH->selectall_arrayref( $sql_p, { Slice => {} } );


@{$c->stash->{photo_new}} = map { {%$_ }} @$aa;