CODEWITHSUNDEEP
mysql
kiwisan
kiwisan

Reputation: 489

Get largest values from multiple columns from latest timestamps in MySql

I'm trying to get a list of the*usedpc values across multiple similar columns, and order desc to get worst offenders. Also, I need to only select the values from the most recent timestamp for each sys_id. Example data:

Sys_id | timestamp | disk0_usedpc | disk1_usedpc | disk2_usedpc
---
1  | 2016-05-06 15:24:10 | 75 | 45 | 35
1  | 2016-04-06 15:24:10 | 70 | 40 | 30
2  | 2016-05-06 15:24:10 | 23 | 28 | 32
3  | 2016-05-06 15:24:10 | 50 | 51 | 55

Desired result (assuming limit 2 for example):

1 | 2016-05-06 15:24:10 | disk0_usedpc | 75
3 | 2016-05-06 15:24:10 | disk2_usedpc | 55

I know I can get the max from each column using greatest, max and group timestamp to get only the latest values, but I can't figure out how to get the whole ordered list (not just max/greatest from each column, but the "5 highest values across all 3 disk columns").

EDIT: I set up a SQLFiddle page: http://sqlfiddle.com/#!9/82202/1/0

EDIT2: I'm very sorry about the delay. I was able to get all three solutions to work, thank you. If @PetSerAl can put his solution in an answer, I'll mark it as accepted, as this solution allowed me to very smoothly customise further.

Upvotes: 0

Views: 233

Answers (3)

user4003407
user4003407

Reputation: 22132

You can join vm_disk table with three row table to create separate row for each of yours disks. Then, as you have row per disk now, you can easily filter or sort them.

select
  `sys_id`,
  `timestamp`,
  concat('disk', `disk`, '_usedpc') as `name`,
  case `disk`
    when 0 then `disk0_usedpc`
    when 1 then `disk1_usedpc`
    when 2 then `disk2_usedpc`
  end as `usedpc`
from
  `vm_disk` join
  (
    select 0 as `disk`
    union all
    select 1
    union all
    select 2
  ) as `t`
where
  (`sys_id`, `timestamp`) in (
    select
      `sys_id`,
      max(`timestamp`)
    from `vm_disk`
    group by `sys_id`
  )
order by `usedpc` desc
limit 5

Upvotes: 1

Blank
Blank

Reputation: 12378

Try this:

select
    t1.sys_id, t1.`timestamp`,
    case locate(greatest(disk0_usedpc ,disk1_usedpc ,disk2_usedpc), concat_ws(',' ,disk0_usedpc ,disk1_usedpc ,disk2_usedpc))
        when 1 then 'disk0_usedpc'
        when 1 + length(concat(disk0_usedpc, ',')) then 'disk1_usedpc'
        when 1 + length(concat(disk0_usedpc, ',', disk1_usedpc, ',')) then 'disk2_usedpc'
    end as usedpc,
    greatest(disk0_usedpc ,disk1_usedpc ,disk2_usedpc) as amount
from yourtable t1
join (
    select max(`timestamp`) as `timestamp`, sys_id
    from yourtable 
    group by sys_id
) t2 on t1.sys_id = t2.sys_id and t1.`timestamp` = t2.`timestamp`
order by t1.`timestamp` desc
-- limit 2

SQLFiddle Demo

How it works, the sub query here is try to get the latest row for each group sys_id, as one kind of way in many solutions. Then you should get the greatest column in disk0_usedpc ,disk1_usedpc ,disk2_usedpc, as you wrote in your question, the function greatest is the plan. So greatest(disk0_usedpc ,disk1_usedpc ,disk2_usedpc) as amount can help you get the amount.

But also you want that column's name, here I used locate and concat, concat_ws(which avoids writing so many separators, here is comma ,).

Let's take row

1 | 2016-05-06 15:24:10 | 75 | 45 | 35

as an example:

concat_ws(',' ,disk0_usedpc ,disk1_usedpc ,disk2_usedpc) will give us "75,45,35", here 75's index in this string is 1, 45 is 4, 35 is 7. As you see, locate(greatest(disk0_usedpc ,disk1_usedpc ,disk2_usedpc), concat_ws(',' ,disk0_usedpc ,disk1_usedpc ,disk2_usedpc)) will return 1, so the greatest row is disk0_usedpc, here it makes.

Upvotes: 1

jerdiggity
jerdiggity

Reputation: 3665

Maybe something like this would work... I know it may look pretty redundant but it could save overhead caused by doing multiple joins to the same table:

SELECT md.Sys_id,
md.timestamp,
CASE
  WHEN
    md.disk0_usedpc > md.disk1_usedpc
      AND
    md.disk0_usedpc > md.disk2_usedpc
  THEN 'disk0_usedpc'
  WHEN
    md.disk1_usedpc > md.disk0_usedpc
      AND
    md.disk1_usedpc > md.disk2_usedpc
  THEN 'disk1_usedpc'
  ELSE 'disk2_usedpc'
END AS pcname,
CASE
  WHEN
    md.disk0_usedpc > md.disk1_usedpc
      AND
    md.disk0_usedpc > md.disk2_usedpc
  THEN md.disk0_usedpc
  WHEN
    md.disk1_usedpc > md.disk0_usedpc
      AND
    md.disk1_usedpc > md.disk2_usedpc
  THEN md.disk1_usedpc
  ELSE md.disk2_usedpc
END AS pcusage
FROM mydatabase md
GROUP BY md.Sys_id HAVING MAX(md.timestamp)
ORDER BY pcusage DESC

Upvotes: 1

Related Questions