Apply limit 10 after accepting different records in mysql

Question

In a MySQL table, I would like to write 10 records with DISTINCT .

I am using Zend Framework.

$select = $this->getAdapter()->select() ->from('table', 'column')->group('column') ->limit(10, 0); 

This is a request generated by the above code.

SELECT table.column FROM table GROUP BY column LIMIT 10

What happens here is that MySQL takes 10 records and then applies the group. So finally, I get only 7 entries.

How to apply DISTINCT first, and then take 10 records from it?

Answer 1

Check that SQL is for the table - MySQL applies limit last, therefore does not do what you say. e.g. test against

 a0 a1 1 1 2 1 3 2 4 2 

and do select A.a1 from A group by a1 limit 2 . You should see 1, 2, not 1, 1.
[I wanted to say it as a "comment", not a "response", but I couldn’t]

Answer 2

I am not 100% sure what you are trying to do.

But if I read it correctly, you need 10 records with certain criteria, and then apply the group. and not vice versa.

Can you use WHERE in this case?

SELECT table.column FROM table WHERE "criteria" GROUP BY column LIMIT 10

Relations Mike

Answer 3

This may help you (I have not tested it, but I'm not sure if it works)

 SELECT DISTINCT column FROM table LIMIT 10 

If it does not work, you can use a temporary table (for example, (SELECT column FROM table) TEMP ), which will select individual elements, and then a query that will select the first ten results in this table.

Hope this helps :)

Answer 4

In ZF, you should use the distinct () method in the query chain:

 $select = $this->getAdapter()->select() ->distinct() ->from('table', 'column') ->limit(10, 0); 

Answer 5

 SELECT DISTINCT column FROM table LIMIT 10 GROUP BY column 

Not sure how to get into classes though ...