I have table like so:
id | order_id | product_id
------------------------------------
1 | 356 | 80
2 | 356 | 81
3 | 357 | 77
4 | 357 | 80
5 | 357 | 78
6 | 358 | 90
What I would like to be able to do is to grab the 2 lowest order_id values without knowing how many there are to begin with, but I am having trouble thinking of how to create the query to select these rows...
SELECT * FROM table ORDER BY order_id ASC
Is there a way to do this within MySQL, that will always return the lowest order_id value rows, so in this case, I should get the first 2 rows returned. If the first 2 rows didn't exist, than I should get returned rows 3 - 5 (totaling 3 rows for order id 357).
How to do this?
Please try this:
SELECT
*
FROM
table INNER JOIN
(
SELECT MIN(order_id) AS min_order_id
FROM table
) AS t
ON t.order_id = table.order_id;
t.inner join between your table and this alias t ON order_id.Try this:
select *
from yourtable
where order_id in (
select order_id from yourtable order by order_id ASC limit 1
)
Or use join
select t1.*
from yourtable t1
join (select order_id from yourtable order by order_id ASC limit 1) t2
on t1.order_id = t2.order_id
Aggregate functions can be used in the HAVING clause of a query, so you should be able to get all rows where the order_id matches the lowest order_id value using the MIN function in a HAVING clause:
SELECT * FROM table
HAVING order_id = MIN(order_id)