How to append a column data to a MAX Aggregate function in SQL? -


column names: name, reading, channel, time

i'm trying find max value each distinct name. each name has many values read on time interval of once every hour. goal pick out max value each distinct name , record time interval occurred in.

so far have gotten code:

 select name, max(reading) "max_reading", data, channel, time   interval_data   group name, data, channel

however, gives me error saying time has aggregated or grouped by. there easy way append time max value occurred @ output , not have perform grouped by/aggregate function on column?

sample data:

name    reading     data    channel     time 1       1           1       1           1/15/2015 09:00 1       3           1       1           1/15/2015 10:00 1       2           1       1           1/15/2015 11:00 1       5           1       1           1/15/2015 12:00 2       2           1       1           1/15/2015 09:00 2       4           1       1           1/15/2015 10:00 2       6           1       1           1/15/2015 11:00 2       5           1       1           1/15/2015 12:00 3       7           1       1           1/15/2015 09:00 3       3           1       1           1/15/2015 10:00 3       5           1       1           1/15/2015 11:00 3       2           1       1           1/15/2015 12:00

desired output: (max reading each distinct name time when max reading reading occurred)

name    reading     data    channel     time 1       5           1       1           1/15/2015 12:00 2       6           1       1           1/15/2015 11:00 3       7           1       1           1/15/2015 09:00

without knowing how data , channel come play, use find data need each name's max(reading):

select     id.name, id.reading, id.data, id.channel, id.type      interval_data id join (     select          name, max(reading) reading              interval_data     group         name     ) id_agg     on      id.name = id_agg.name      ,      id.reading = id_agg.reading

if need channel , type distinguish unique row, include in inner-join sub-query , in join's on parameters.


Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more

ruby on rails - Seeing duplicate requests handled with Unicorn -

recently upgraded our ec2 instances c3.large in order boost more unicorn workers handle increased traffic our site. (6xworkers per machine - 2 ec2 instances). when did this, started seeing duplicate records being created! looks somehow, maybe 2 unicorn workers attempting process same request? in nginx logs see 1 [post] request particular rails controller - yet 2 records being created in database. i'm hitting kind of race condition issue - have no idea how debug this. doesn't happen time, when it's frustrating well. noticed 2 database records within 5 seconds of each other. (and yes disable button when user clicks submit button). unicorn 4.8.2 ruby 1.9.3 rails 3.2.14 any great! thanks! although may not fix problem, upgrade ruby 2.0.0. there blog mentioning idea upgrade ruby 2.0.0 if using unicorn. for example: https://www.digitalocean.com/community/articles/how-to-optimize-unicorn-workers-in-a-ruby-on-rails-app specifically: if using ruby 1.9, ...
Read more