CODEWITHSUNDEEP
mysql
zonelsk
zonelsk

Reputation: 245

MySQL UNION and ORDER - multiple select queries on single table

i'm trying to select max 250 rows from one table. First i want to select ALL rows that have column locked='1' and after that select X number of rows that have column locked = '0' sorted by column since (timestamp).

X is 250 - num rows from firest query.

I tried this:

select * from (
select * from aktivni as a1 where locked = '1'
union
select * from aktivni as a2 where locked = '0'
) as tb1 limit 250

but with this I don't get correct values with second query (not ordered by since).

And if I do this:

select * from (
select * from aktivni as a1 where locked = '1'
union
select * from aktivni as a2 where locked = '0' ORDER BY since DESC
) as tb1 limit 250

than everything is ordered by since and I don't get ALL values that have column locked set to 1.

Is union right approach or can i do something else?

The workaround would be to write code in php to do that. First I would select all with: SELECT * FROM table WHERE locked = '1'

than I would count rows and with second query get remaining rows SELECT * FROM table WHERE locked = '0' LIMIT (250 - num_rows_from_first_query) but I want to avoid that.

Upvotes: 1

Views: 605

Answers (1)

sgeddes
sgeddes

Reputation: 62831

No need for a union - you can just use a condition in your order by with limit:

select locked, since
from aktivni cross
order by locked = '1' desc, since desc
limit 250

Upvotes: 2

Related Questions