Thread: Re: Need help for constructing query
Just want to add: > 1) Display the rows with the highest date per id. That gives as many rows as > ids exist. > > select id, max(date) from mytable group by id; > > gives just the id and the date, not the other values. I think of doing this > in two steps: select id, max(date),min,value,max from mytable group by id,min,value,max; Gives too many rows. I don't know why. If I can manage to fix the query above just to output one row per id I can solve it. Marco
If you group by a unique value you in effect perform no grouping at all... What you need to do, in a subquery, is find the max(date) over the data you want to group by. Then, in the outer query select the record(s) that match that date. It is in the outer query where you can then add in any additional fields that are associated with the filtered records. SELECT * FROM table t INNER JOIN (SELECT id, max(date) AS limitdate FROM table GROUP BY id) limiter WHERE t.id = limiter.id AND t.date = limiter.limitdate It becomes somewhat simpler if you have a single value PRIMARY KEY on table: SELECT * FROM table WHERE table.pk = (SELECT pk FROM table tsub WHERE tsub.id = table.id ORDER BY tsub.date DESC LIMIT 1); There are also other variations but the concept holds. You MUST be using a form of sub-query (or JOIN) to make it work. Also keep in mind that if you want to ensure only a single record per ID that an ID cannot be associated with the same date more than once. The second query mitigates this by using a PRIMARY KEY along with a "LIMIT 1" clause. The first query, however, can return multiple records with the same ID if they share the same date. David J. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Marco Sent: Friday, March 25, 2011 10:10 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Need help for constructing query Just want to add: > 1) Display the rows with the highest date per id. That gives as many rows as > ids exist. > > select id, max(date) from mytable group by id; > > gives just the id and the date, not the other values. I think of doing > this in two steps: select id, max(date),min,value,max from mytable group by id,min,value,max; Gives too many rows. I don't know why. If I can manage to fix the query above just to output one row per id I can solve it. Marco -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Hi David,
thanks for your quick answer. I tried to perform both queries but I failed.
Maybe it's because of the fact that I simplified my example and the one table
is actually a join of two tables. The actual tables look as follows:
monitorsensor=> select * from sensors;
sensorid | typename | locationid | min | max
----------+-------------+------------+-----+-----
4 | Particle | 3 | |
5 | Humidity | 4 | |
6 | Temperature | 4 | |
7 | Humidity | 5 | |
1 | Temperature | 1 | 24 | 35
2 | Humidity | 1 | 125 | 135
3 | Humidity | 2 | 55 | 66
(7 rows)
sensorid is primary key.
monitorsensor=> select * from sensordata limit 5;
sensorid | datetime | value
----------+------------------------------+--------
1 | 2010-01-01 01:01:01.23456+01 | 31
2 | 2010-01-01 01:02:01.23456+01 | 131.39
3 | 2010-01-01 01:03:01.23456+01 | 62.07
1 | 2010-01-01 01:04:01.23456+01 | 33.5
2 | 2010-01-01 01:05:01.23456+01 | 133.84
(5 rows)
sensorid is foreign key. Primary key is the combination of
(sensorid, datetime). Here the complete problem statement:
Select all rows that exceeded the alarm values within the last 10 min
except those where the last inserted entry (determined by datetime) didn't
exceeded the alarm value.
I feel that my solution is overcomplicated. I solved it as follows.
SELECT sensorid, min, value, max, datetime
FROM sensordata NATURAL JOIN sensors
WHERE
datetime
BETWEEN CURRENT_TIMESTAMP - INTERVAL '10 minutes'
AND CURRENT_TIMESTAMP
AND ( value<min OR value>max )
EXCEPT
(
SELECT sensorid, min, value, max, t.datetime
FROM
(
SELECT sensorid, datetime, min, value, max, MAX(datetime)
OVER (PARTITION BY sensorid) AS last
FROM sensordata NATURAL JOIN sensors
WHERE
datetime
BETWEEN CURRENT_TIMESTAMP - INTERVAL '10 minutes'
AND CURRENT_TIMESTAMP
) AS t
WHERE
t.datetime=t.last AND ( value>=min AND value<= max )
);
Marco
Over complicated or not the solution makes sense and seems to be correct.
As described you ended up using a sub-query within the EXCEPT clause in
order to return just the most recent sensor reading for each sensor (with
the additional range check for min/max). I've never actually used an EXCEPT
before and while alternatives can work this is intuitive.
I'm just getting used to using Widows myself (since before about a month ago
I was running 8.2) but they do make doing this intra-record aggregations
easier (though non-Window solutions are often possible).
Barring any performance concerns I'd just use what you have and move on.
SQL is very good at getting results but most non-trivial expressions do
become complicated. Using views and/or functions can hide of the complexity
but it still ends up being present.
David J.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Marco
Sent: Friday, March 25, 2011 12:24 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need help for constructing query
....
Select all rows that exceeded the alarm values within the last 10 min except
those where the last inserted entry (determined by datetime) didn't exceeded
the alarm value.
I feel that my solution is overcomplicated. I solved it as follows.
SELECT sensorid, min, value, max, datetime FROM sensordata NATURAL JOIN
sensors WHERE
datetime
BETWEEN CURRENT_TIMESTAMP - INTERVAL '10 minutes'
AND CURRENT_TIMESTAMP
AND ( value<min OR value>max )
EXCEPT
(
SELECT sensorid, min, value, max, t.datetime
FROM
(
SELECT sensorid, datetime, min, value, max, MAX(datetime)
OVER (PARTITION BY sensorid) AS last
FROM sensordata NATURAL JOIN sensors
WHERE
datetime
BETWEEN CURRENT_TIMESTAMP - INTERVAL '10 minutes'
AND CURRENT_TIMESTAMP
) AS t
WHERE
t.datetime=t.last AND ( value>=min AND value<= max ) );
Marco
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general