0

example, my data

XXX yyy  order_time
x1  y1  2019/11/11 00:11:22
x2  y2  2019/11/11_00:11:22
x3  y3  2019/11/11_00:22:33
x4  y4  2019/11/11_00:33:44
x5  y5  2019/11/11_00:33:44
x6  y6  2019/11/11_00:44:55

To get all data, I can use:

select xxx,yyy,order_time from my_table where condition=bala;

To get 3 set of the order_time

select distinct(order_time) from my_table order by order_time limit 3;

But how to combine them together? I want to get all data that the order time within the 3 set of the order_time.

expect would be:

XXX yyy  order_time
x1  y1  2019/11/11 00:11:22
x2  y2  2019/11/11_00:11:22
x3  y3  2019/11/11_00:22:33
x4  y4  2019/11/11_00:33:44
x5  y5  2019/11/11_00:33:44

Thanks!

2
  • Can you show your expected result as a table? And which mysql version are you using?
    – danblack
    Commented Jan 10, 2020 at 2:51
  • @danblack added.
    – Benniu
    Commented Jan 10, 2020 at 3:52

1 Answer 1

0

MySQL 8+

WITH cte AS ( SELECT *, 
                     DENSE_RANK() OVER (ORDER BY order_time) dr
              FROM my_table
              WHERE <conditions> )
SELECT xxx,
       yyy,
       order_time
FROM cte
WHERE dr <= 3

MySQL 5.+

SELECT my_table.*
FROM my_table 
JOIN ( SELECT DISTINCT order_time 
       FROM my_table 
       WHERE <conditions>
       ORDER BY order_time 
       LIMIT 3 ) times USING (order_time)
/* WHERE <conditions> */

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.