CODEWITHSUNDEEP
mysql
Steven
Steven

Reputation: 19425

How do I return 10 rows where 5 is for a specific value?

I have a table for special offers (shopping). The table contains special offers from 50 stores, 5 from Oslo and 45 from other cities in Norway.

By default, I want to select 10 rows by date, country and city.

My current query looks like this:

SELECT b.name, a.title, a.shortDescription, a.longDescription, a.startTime, a.endTime, b.fk_countryID, b.city
FROM sl_sale a
LEFT JOIN sl_store b ON a.fk_storeID = b.id
WHERE a.endTime >= NOW()
AND b.fk_countryID = 'NO'
AND b.city = 'Oslo'
ORDER BY a.startTime ASC
LIMIT 10

My problem is that it will only return 5 rows because b.city = 'Oslo'.

How can I return 10 rows, where the other 5 are from other cities where a.endTime >= NOW()?
Do I have to make two queries?

Upvotes: 0

Views: 62

Answers (3)

dnuttle
dnuttle

Reputation: 3830

SELECT b.name, a.title, a.shortDescription, a.longDescription, a.startTime, a.endTime, b.fk_countryID, b.city
FROM sl_sale a
LEFT JOIN sl_store b ON a.fk_storeID = b.id
WHERE (a.endTime >= NOW() OR b.city='Oslo')
AND b.fk_countryID = 'NO'
ORDER BY a.startTime ASC
LIMIT 10

Upvotes: 0

Nahydrin
Nahydrin

Reputation: 13517

SELECT b.name, a.title, a.shortDescription, a.longDescription, a.startTime, a.endTime,     b.fk_countryID, b.city
FROM sl_sale a
LEFT JOIN sl_store b ON a.fk_storeID = b.id
WHERE b.fk_countryID = 'NO'
AND (a.endTime >= NOW() OR b.city = 'Oslo')
ORDER BY a.startTime ASC
LIMIT 10

Upvotes: 0

Jason McCreary
Jason McCreary

Reputation: 72981

If you always want records for Olso first, you could move it from the WHERE clause to the ORDER clause:

SELECT b.name, a.title, a.shortDescription, a.longDescription, a.startTime, a.endTime, b.fk_countryID, b.city
FROM sl_sale a
LEFT JOIN sl_store b ON a.fk_storeID = b.id
WHERE a.endTime >= NOW()
AND b.fk_countryID = 'NO'
ORDER BY b.city = 'Oslo' DESC, a.startTime ASC
LIMIT 10

This effectively limits it to 10 results matching that WHERE condition, but any rows where b.city = 'Oslo' will be ordered to the top. Note: That could be more than 5 rows.

Upvotes: 2

Related Questions