Can where limit be made in mysql query?

Question

Since there are more than one line that meets the defined condition in the section where I added "limit 1" in the following query, I want the query to continue by taking the first line that satisfies the condition using this limitation. Can I make such a limitation where I mentioned?

table1

|   id  |   name    |
|-------|-----------|
|   1   |   pro1    |
|   2   |   pro2    |
|   3   |   pro3    |

table2

| id | start_date |  end_date  | product_id | daily | old_daily |
|----|------------|------------|------------|-------|-----------|
| 1  | 2021-02-19 | 2021-03-21 |  1         | 700   | 800       |
| 2  | 2021-02-19 | 2021-03-21 |  2         | 400   | 550       |
| 3  | 2021-02-19 | 2021-03-21 |  3         | NULL  | 700       |
| 4  | 2021-03-22 | 2021-04-21 |  2         | NULL  | 600       |
| 5  | 2021-04-22 | 2021-05-21 |  2         | NULL  | 650       |

select
  `table1`.`id`, `name`,
  (CASE WHEN table2.daily IS NOT NULL THEN table2.daily 
     ELSE table2.old_daily END) AS price 
from `table1` 
inner join `table2` on `table1`.`id` = `table2`.`product_id`
where (date(`end_date`) >= '2021-02-11' LIMIT 1)
   or (date(`start_date`) <= '2025-02-11' 
  and date(`end_date`) >= '2025-02-11')
order by `price` DESC

Limit1 does not work in this query. The unlimited query result is as follows:

| id | name | price |
|----|------|-------|
| 1  | pro1 | 700   |
| 2  | pro2 | 400   |
| 3  | pro3 | 700   |
| 2  | pro2 | 600   |
| 2  | pro2 | 650   |

The result I want to get:

| id | name | price |
|----|------|-------|
| 1  | pro1 | 700   |
| 2  | pro2 | 400   |
| 3  | pro3 | 700   |

What result are you looking for? Like, why do you think you need a `LIMIT` here? — gen_Eric, Feb 11 '21 at 17:50
@RocketHazmat I added the result I wanted. Can you check it again? — Ahmet Çetintaş, Feb 11 '21 at 17:54
Sounds like instead of a `LIMIT`, what you want is a `GROUP BY table1.id` (and possibly a `GROUP_CONCAT()`). — gen_Eric, Feb 11 '21 at 17:55
Try removing `LIMIT 1` and try replacing `order by price DESC` with `group by table1.id ASC`. See this demo: http://sqlfiddle.com/#!9/86ed8e/5 — gen_Eric, Feb 11 '21 at 17:59
@RocketHazmat actually I didn't want them to be grouped here, but this works for me now as a result. Thanks. Also, could we do as Strawberry said? — Ahmet Çetintaş, Feb 11 '21 at 18:39

Groudon · Accepted Answer · 2021-02-11T18:20:01.213

I guess in these cases you should use the UNION feature, as MySQL is based on the relations and functions concept. And your query is like, "the 1st condition shall satisfy only once".

I have mentioned an ambiguity regarding why MySQL and set theory does not support this at the end of the solution.

One solution can be,

select `table1`.`id`, `name`, 
(CASE WHEN table2.daily IS NOT NULL THEN table2.daily ELSE table2.old_daily END) AS price  
from `table1` inner join `table2` on `table1`.`id` = `table2`.`product_id` where 
date(`end_date`) >= '2021-02-11' LIMIT 1 order by `price` DESC
UNION 
select `table1`.`id`, `name`, 
(CASE WHEN table2.daily IS NOT NULL THEN table2.daily ELSE table2.old_daily END) AS price  
from `table1` inner join `table2` on `table1`.`id` = `table2`.`product_id` where 
(date(`start_date`) <= '2025-02-11' and date(`end_date`) >= '2025-02-11') order by `price` DESC;

There is a case of ambiguity assuming the conditions given by you as A OR B:

A record, R1 satisfies A, but not B
A record, R2 satisfies A and B both

If R1 is read first will we consider R2, as we are limiting the use of condition A by 1 but using condition B with OR clause?

`As of now, I do not know of any set operator/function that has the power to limit the use of any sub-condition.` Steady Padawan — Strawberry, Feb 11 '21 at 18:16

Can where limit be made in mysql query?

1 Answers1