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

Marco <netuse@lavabit.com> Friday 25 March 2011 14:25:47

Hi,

I have a table like this:

id date min max value
1 2011-03-25 20 30 17
3 2011-03-21 40 55 43
3 2011-03-23 40 55 52
2 2011-02-25 5 2
4 2011-03-15 74
4 2011-03-25 128
1 2011-03-22 20 30 24

I'm looking for a query that outputs the last rows (highest date) per id
where the value is between min and max. I already have problems displaying
the last rows per id. Something like

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:

1) Display the rows with the highest date per id. That gives as many rows
as ids exist.
2) Remove the rows that do not match ( value<max and value>min )

Marco

SELECT * FROM where (min < value and value < max) and (id, date) in (
SELECT id, max(date) WHERE (min < value and value < max) group by id)

1st check (min < value and value < max) may be not needed, dependig what You
are looking for.

I didn't testd this

On 2011-03-27 rsmogura@softperience.eu (Radosław Smogura) wrote:

Marco <netuse@lavabit.com> Friday 25 March 2011 14:25:47

Hi,

I have a table like this:

id date min max value
1 2011-03-25 20 30 17
3 2011-03-21 40 55 43
3 2011-03-23 40 55 52
2 2011-02-25 5 2
4 2011-03-15 74
4 2011-03-25 128
1 2011-03-22 20 30 24

I'm looking for a query that outputs the last rows (highest date) per id
where the value is between min and max. I already have problems displaying
the last rows per id. Something like

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:

1) Display the rows with the highest date per id. That gives as many rows
as ids exist.
2) Remove the rows that do not match ( value<max and value>min )

Marco

SELECT * FROM where (min < value and value < max) and (id, date) in (
SELECT id, max(date) WHERE (min < value and value < max) group by
id)

This seems to do the task. Thanks you for the snippet!

Marco