I am trying to select a maximum of 2 rows where a column has the same value. ie:
id title accountid date
1 job 1 1 Oct. 1
2 job 2 1 Oct. 1
3 job 3 1 Oct. 1
4 job 1 2 Oct. 2
5 job a 3 Oct. 2
6 job z 4 Oct. 3
7 job 2 2 Oct. 3
8 job 3 2 Oct. 8
I want to select
1 job 1 1 Oct. 1
2 job 2 1 Oct. 1
<----- Skip this row because we already
have 2 from account 1
4 job 1 2 Oct. 2
5 job a 3 Oct. 2
6 job z 4 Oct. 3
7 job 2 2 Oct. 3
What I'm using right now to select is something like this:
SELECT *
FROM table
ORDER BY date DESC, RAND()
I've looked a bit into using HAVING COUNT(accountid) <= 2
but that's just led to confusion. I'm pretty new to using sql.
Thanks for your help!
UPDATE:
Hi, thanks for all the quick responses. I've tried each of them and couldn't seem to get them to work. I figured out a way to limit the jobs per account id using php. Thanks again for your time and efforts to help me solve this.
set @id := 0, @acid := 0;
select t.id, title, accountid, `date`
from
t
inner join (
select
id,
if(@acid = accountid, @i := @i + 1, @i := 1) as i,
@acid := accountid as acid
from t
order by accountid, `date` desc
) s on t.id = s.id
where s.i <= 2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With