Can I use a single MySQL query to select distinct rows and then non-distinct rows if a limit hasn't been reached?

Question

I hope I'm explaining this properly, my knowledge of MySQL is quite limited.

Let's say I have a table with rows that have a field called shape.

I'd like to select a bunch of rows from a table, but return all of the rows with unique shape field values first. If I have less than a certain number of rows, let's say 7, then I'd like to fill the remaining result rows with non-unique shape rows. The best way I can word it is that they're "ordered by uniqueness, and then by some other value".

So, I don't want: square, square, circle, circle, rectangle, square, triangle

I'd like to have: square, circle, rectangle, triangle, square, square, circle

Is this possible to do using a single SQL query? I'm using MySQL with PHP, if that makes any difference.

Thanks!

Answer 1

You might be able to do this using a UNION (i.e. use two different queries, but just combine the results)

Answer 2

You could use UNION ALL to merge 2 different (but same select) queries. But I don't think a LIMIT works on a UNION, then you'd have to:

SELECT unique query here, with limit 0,7 UNION ALL SELECT other query here, with limit 0.7

While them both having the same SELECT values, they get merged into 1 result, then you can simply cut off all rows after 7.

But maybe you can already do a LIMIT on the whole UNION, that I do not know.

Answer 3

You can select a limited number of rows from the combination of the distinct values first, in a union with your non-unique query (which you also limit by the maximum row count you want to retrieve).

ie. select field1, field2, ... from (select distinct field1, field2, ... from ... UNION select field1, field2, ... from ... LIMIT MAX_ROW_COUNT) AS total LIMIT MAX_ROW_COUNT