Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select a maximum of 2 rows where a column has the same value

Tags:

sql

mysql

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.

like image 304
Caleb Doucet Avatar asked Oct 11 '12 17:10

Caleb Doucet


1 Answers

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
like image 78
Clodoaldo Neto Avatar answered Oct 08 '22 16:10

Clodoaldo Neto