CODEWITHSUNDEEP
mysql
Envious
Envious

Reputation: 487

MySQL with Where IN and Distinct and Limit

SELECT id, server_id, start_time, end_time 
FROM errors 
WHERE server_id in (3, 12, 24, 25, 26, 27, 28, 29, 30) 
ORDER BY id DESC 
LIMIT 9

This is the query I'm trying to run to give me results where the server_id = 3, 12, 24, 25, 26, 27, 28, 29, 30. Instead, what I receive is server_id = 25, 25, 12, 25, 27, 27, 28, 28, 27. Note the repeating server_ids. The query gives me unique id but duplicate server_id.

Is there a way I can get results that would show me the last id for each server_id?

I've tried doing ORDER BY server_id but that gives me the same issue.

I tried running DISTINCT but that also does not work.

Upvotes: 0

Views: 44

Answers (2)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

you'll have to use some aggregation functions.

Something like

select 
  server_id,
  max(id),
  avg(start_time),--for example
  avg(end_time)--for example
from errors
where server_id in (3, 12, 24, 25, 26, 27, 28, 29, 30) 
group by server_id
order by id desc

if you need tht start_time and end_time corresponding to the max id by server_id, you may do

select e.id, e.server_id, e.start_time, e.end_time
from errors e
join (select server_id, max(id) maxid
      from errors
      group by server_id) t
  on t.maxid = e.id and e.server_id = t.server_id
where e.server_id in (3, 12, 24, 25, 26, 27, 28, 29, 30) 
order by e.id DESC

Upvotes: 1

xQbert
xQbert

Reputation: 35323

The issue you have is that you need only one record from each server with the max ID.. and relative information. You need to limit the results to just that max ID... Here's one way...

SELECT id, server_id, start_time, end_time 
FROM errors 
WHERE server_id in (3, 12, 24, 25, 26, 27, 28, 29, 30) 
and ID = (Select max(ID) from errors E2 where E2.server_ID=errors.server_ID)
ORDER BY id DESC 
LIMIT 9

Upvotes: 1

Related Questions