-1

I need to select all rows from a table (see structure below) but that's more complex.

I want 5 unique idm but all rows with this idm (which is the ID of a conversation).. How can I do it ?

+------------+
| messagerie |
+------------+
|     ID     |
|    idm     |
|    send    | 
|   receipt  |
|   subject  |
|  message   |
+------------+
G Perdigal
  • 15
  • 3

1 Answers1

0
  • Select distinct idms for uniqueness and use LIMIT keyword to select only 5.
  • Use inner join clause to select all rows who have this idm.
  • Below query would give you 5 unique idms at random all the time.

SQL:

select *
from messagerie m1
inner join (select distinct idm
              from messagerie
              order by rand() LIMIT 5) m2
on m1.idm = m2.idm;
nice_dev
  • 17,053
  • 2
  • 21
  • 35
  • That sounds perfect.. except that I'm working on a strange SQL server that tells me : #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – G Perdigal Mar 21 '19 at 13:49
  • @GPerdigal What version of MySQL are you using? Also, I fixed the query from whereIn() to an inner join since it could generate new 5 rows all the time previously. – nice_dev Mar 21 '19 at 13:51
  • @GPerdigal It looks like you could also use whereIn() clause as the order of the execution [suggested](https://stackoverflow.com/a/24128128/4964822) here, but better be safe. – nice_dev Mar 21 '19 at 13:54
  • @GPerdigal I suggest you to better upgrade to MariaDB since you would be doing more complex operations as the application scales and you wouldn't want not supported keywords error which are necessary for the task in hand. – nice_dev Mar 21 '19 at 14:02
  • @GPerdigal [This link](https://mariadb.com/resources/blog/how-to-migrate-from-mysql-to-mariadb-on-linux-in-five-steps/) shall help. – nice_dev Mar 21 '19 at 14:03