SQl-Unique-Query

pradeepspa · 06-20-2018, 12:03 PM

I am trying to do a query against the INFLUX-DB to get unique values.Below is the query I use,

Code:

select host AS Host,(value/100) AS Load from metrics  where time > now() - 1h and command='Check_load_current' and value>4000;

The output for the query is,

Quote:

time Host Load
---- ---- ----
1529508443000000000 host-1 42.72
1529508609000000000 host-5 53.94
1529508856000000000 host-1 40.37
1529508913000000000 host-2 41.02
1529508937000000000 host-1 44.49

What I actually want is the unique "Host" values. For example I want "host-1" as a output repeated only once(latest value) eventhough the load values are different.How can I achieve this? Any help would be much helpful.

scasey · 06-20-2018, 12:08 PM

Please look at how to use the ORDER-BY clause...and maybe LAST...

TB0ne · 06-20-2018, 12:12 PM

Quote:

Originally Posted by pradeepspa

I am trying to do a query against the INFLUX-DB to get unique values.Below is the query I use,

Code:

select host AS Host,(value/100) AS Load from metrics  where time > now() - 1h and command='Check_load_current' and value>4000;

The output for the query is

Code:

time Host Load
---- ---- ----
1529508443000000000 host-1 42.72
1529508609000000000 host-5 53.94
1529508856000000000 host-1 40.37
1529508913000000000 host-2 41.02
1529508937000000000 host-1 44.49

What I actually want is the unique "Host" values. For example I want "host-1" as a output repeated only once(latest value) eventhough the load values are different.How can I achieve this? Any help would be much helpful.

As with many of your previous threads, you are providing next to no useful information. You don't tell us anything about the structure of that table, version/distro of Linux, version of MySQL, etc. The query you posted doesn't match the output (you select two columns, but post 3).

Read the MySQL documentation on both the DISTINCT and GROUP BY directives.

dugan · 06-20-2018, 12:51 PM

Quote:

Originally Posted by scasey

Please look at how to use the ORDER-BY clause...and maybe LAST...

And GROUP BY and HAVING.

pradeepspa · 06-20-2018, 03:36 PM

Apologize for not providing essential info. I am running on centos-7 and this query is on INFLUX-DB. Since its similar to sql query i assumed both uses similar pattern.

scasey · 06-20-2018, 03:45 PM

Saw the name of the database engine in your OP...I also presume the syntax is similar (most ansi SQL is similar). Doesn’t really change anything: you need to learn what the suggested modifiers do and how to apply them to your selection. You’ll need to study the documentation for your database. We can’t do that for you.

pradeepspa · 06-20-2018, 03:51 PM

Thank you.