CODEWITHSUNDEEP
mysql
Izopi4a
Izopi4a

Reputation: 500

Mysql select with IN, limit rows to 1 for each match

lets say I have this table:

| id | record_id | date_updated |
|----|-----------|--------------|
| 1  | 1         | 19-03-2015   |
| 2  | 1         | 18-03-2015   |
| 3  | 1         | 17-03-2014   |
| 4  | 2         | 01-01-2015   |
| 5  | 2         | 05-02-2015   |

so the results I am looking for are :

| id | record_id | date_updated |
|----|-----------|--------------|
| 1  | 1         | 19-03-2015   |
| 4  | 2         | 01-01-2015   |

I have array with record ids.

$records = [1,2];

So I can do something like:

select * from `mytable` 
WHERE `record_id` IN ($records) 
AND mytable.date_update > 01-01-2014 
AND mytable.date_updated < 12-12-2015

so mysql will select records wich match date_updated criteria ( and record id ofc ), which are more then 1 for each record ID, basically I want to make him limit the rows for each $record_id to 1

If it is even possible.

//it is super hard to explain the problem, the real case is that this is a sub query of another query, but the real example is 10 rows query and 100 columns table, so it will be even more hard to explain the situation and for someone to read it / udnerstands it. Hopefully someone will understand my problem, if not I will try to explain more.

Thanks

Upvotes: 2

Views: 2408

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

There are many ways to get the record per group, and since you need only once you can easily do as below 

select t1.* from table_name t1 
where (
  select count(*) from table_name t2 
   where t1.record_id = t2.record_id
  ) > =0 
and 
t1.date_updated > '2014-01-01' and date_updated < '2015-12-12'  
group by t1.record_id ;

There are other way too using left join

select t1.* from table_name t1 
left join table_name t2 on t1.record_id = t2.record_id 
and t1.id >t2.id where t2.id is null

This will give you data with asc order with id

If you need data with max(id) for a record_id you can use 

t1.id < t2.id

instead of 

t1.id >t2.id

The same comparison you can do with first query.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172428

You can try using the group by clause

SELECT * 
  FROM `mytable`
 WHERE id IN (
               SELECT min(id) 
                 FROM `mytable`
                 WHERE `record_id` IN ($records) 
                 AND mytable.date_update > 01-01-2014 
                 AND mytable.date_updated < 12-12-2015 
                 group by record_id
             );

Upvotes: 2

Related Questions