Tricky SQL that _should_ be simple... 
Author Message
 Tricky SQL that _should_ be simple...

Hello,

This sounds like a simple SQL query, yet nowhere have I found what
would do the trick. Would anyone help me out?

The goal is basic.
I have this table which keeps logging data on some points in the form
of (point_id, timestamp, value).
My goal is to retrieve the latest information for all point_id.

My idea was to use a statement such as this one:

SELECT timestamp, point_id, value
FROM table_name
WHERE (timestamp, point_id) IN (SELECT Max(timestamp),point_id FROM
table_name GROUP BY point_id)

Unfortunately, SQL Server doesn't allow one to use several columns
with the IN operator, just one.

So, is there any solution to this problem in the SQL Server realm?  

Please help, I am running out of ideas.

Thanks,

Eric



Sat, 10 Apr 2004 07:55:34 GMT
 Tricky SQL that _should_ be simple...

Eric,

In the future, please post (simplified) table schemas (CREATE TABLEs),
sample data (INSERTs) and the results you want using the sample data.

Without the information requested above, the following is untested:

SELECT t1.timestamp, t1.point_id, t1.value
FROM table_name as t1
WHERE t1.timestamp = (SELECT Max(t2.timestamp)
                                             FROM table_name
                                             WHERE t2.point_id =
t1.point_id)

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Hello,

> This sounds like a simple SQL query, yet nowhere have I found what
> would do the trick. Would anyone help me out?

> The goal is basic.
> I have this table which keeps logging data on some points in the form
> of (point_id, timestamp, value).
> My goal is to retrieve the latest information for all point_id.

> My idea was to use a statement such as this one:

> SELECT timestamp, point_id, value
> FROM table_name
> WHERE (timestamp, point_id) IN (SELECT Max(timestamp),point_id FROM
> table_name GROUP BY point_id)

> Unfortunately, SQL Server doesn't allow one to use several columns
> with the IN operator, just one.

> So, is there any solution to this problem in the SQL Server realm?

> Please help, I am running out of ideas.

> Thanks,

> Eric



Sat, 10 Apr 2004 11:40:04 GMT
 Tricky SQL that _should_ be simple...
I am having a similar 'issue'; we're gonna have a system where a system will
log data from devices (agents) and rate the measured data with points.

Table schema is as such:
CREATE TABLE [dbo].[PointsTable] (
 [AgentID] [int] NULL ,
 [PointID] [int] NULL ,
 [Point] [real] NULL ,
 [UpdateID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]

Test data:
INSERT INTO PointsTable (AgentID, PointID, Point) VALUES (1000,1,1)
(UpdateID will be 1)
INSERT INTO PointsTable (AgentID, PointID, Point) VALUES (1000,2,1)
(UpdateID will be 2)
INSERT INTO PointsTable (AgentID, PointID, Point) VALUES (1001,1,3)
(UpdateID will be 3)
INSERT INTO PointsTable (AgentID, PointID, Point) VALUES (1001,2,4)
(UpdateID will be 4)
INSERT INTO PointsTable (AgentID, PointID, Point) VALUES (1000,1,0)
(UpdateID will be 5)
INSERT INTO PointsTable (AgentID, PointID, Point) VALUES (1000,3,2)
(UpdateID will be 6)

I need to construct a SELECT that will give me the latest PointID, Point for
every AgentID, so in this case it'll be
1000,1,0 (updateID 5)
1000,2,1 (updateID 2)
1000,3,2 (updateID 6)
1001,1,3 (updateID 3)
1001,2,4 (updateID 4)

I just cannot figure out how to accomplish this (I've tried DISTINCT'ing,
GROUP'ing and nested SELECTs) - I hope someone can help.

Thanks a bunch in advance!

Best regards,
-Allan Jensen



Sat, 10 Apr 2004 20:16:38 GMT
 Tricky SQL that _should_ be simple...
Dear BP Margolin,

You made my day!
Thank you so much for your kind help, I really appreciated it.

The SQL worked, except that one needed to define the t2 alias:

SELECT t1.timestamp, t1.point_id, t1.value
FROM table_name as t1
WHERE t1.timestamp = (SELECT Max(t2.timestamp)
                      FROM table_name As t2
                      WHERE t2.point_id = t1.point_id)

You got the hard part right, thanks again ! :)

Eric

Quote:

> Eric,

> In the future, please post (simplified) table schemas (CREATE TABLEs),
> sample data (INSERTs) and the results you want using the sample data.

> Without the information requested above, the following is untested:

> SELECT t1.timestamp, t1.point_id, t1.value
> FROM table_name as t1
> WHERE t1.timestamp = (SELECT Max(t2.timestamp)
>                                              FROM table_name
>                                              WHERE t2.point_id =
> t1.point_id)

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Hello,

> > This sounds like a simple SQL query, yet nowhere have I found what
> > would do the trick. Would anyone help me out?

> > The goal is basic.
> > I have this table which keeps logging data on some points in the form
> > of (point_id, timestamp, value).
> > My goal is to retrieve the latest information for all point_id.

> > My idea was to use a statement such as this one:

> > SELECT timestamp, point_id, value
> > FROM table_name
> > WHERE (timestamp, point_id) IN (SELECT Max(timestamp),point_id FROM
> > table_name GROUP BY point_id)

> > Unfortunately, SQL Server doesn't allow one to use several columns
> > with the IN operator, just one.

> > So, is there any solution to this problem in the SQL Server realm?

> > Please help, I am running out of ideas.

> > Thanks,

> > Eric



Sun, 11 Apr 2004 02:18:18 GMT
 Tricky SQL that _should_ be simple...
Allan,

-- option #1
select t1.*
from PointsTable as t1
join (select MaxUpdateID = max(UpdateID)
         from PointsTable
         group by AgentID, PointID) as t2
  on (t1.UpdateID = t2.MaxUpdateID)

-- option #2
select t1.*
from PointsTable as t1
where not exists (select *
                                from PointsTable as t2
                                where t2.AgentID = t1.AgentID
                                and   t2.PointID = t1.PointID
                               and   t2.UpdateID > t1.UpdateID)

-- option #3
select t1.*
from PointsTable as t1
where t1.UpdateID = (select max(t2.UpdateID)
                                        from PointsTable as t2
                                       where t2.AgentID = t1.AgentID
                                       and   t2.PointID = t1.PointID)

-- option #4
select t1.*
from PointsTable as t1
where t1.UpdateID = (select top 1 t2.UpdateID
                                       from PointsTable as t2
                                       where t2.AgentID = t1.AgentID
                                       and   t2.PointID = t1.PointID
                                       order by t2.UpdateID desc)

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I am having a similar 'issue'; we're gonna have a system where a system
will
> log data from devices (agents) and rate the measured data with points.

> Table schema is as such:
> CREATE TABLE [dbo].[PointsTable] (
>  [AgentID] [int] NULL ,
>  [PointID] [int] NULL ,
>  [Point] [real] NULL ,
>  [UpdateID] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]

> Test data:
> INSERT INTO PointsTable (AgentID, PointID, Point) VALUES (1000,1,1)
> (UpdateID will be 1)
> INSERT INTO PointsTable (AgentID, PointID, Point) VALUES (1000,2,1)
> (UpdateID will be 2)
> INSERT INTO PointsTable (AgentID, PointID, Point) VALUES (1001,1,3)
> (UpdateID will be 3)
> INSERT INTO PointsTable (AgentID, PointID, Point) VALUES (1001,2,4)
> (UpdateID will be 4)
> INSERT INTO PointsTable (AgentID, PointID, Point) VALUES (1000,1,0)
> (UpdateID will be 5)
> INSERT INTO PointsTable (AgentID, PointID, Point) VALUES (1000,3,2)
> (UpdateID will be 6)

> I need to construct a SELECT that will give me the latest PointID, Point
for
> every AgentID, so in this case it'll be
> 1000,1,0 (updateID 5)
> 1000,2,1 (updateID 2)
> 1000,3,2 (updateID 6)
> 1001,1,3 (updateID 3)
> 1001,2,4 (updateID 4)

> I just cannot figure out how to accomplish this (I've tried DISTINCT'ing,
> GROUP'ing and nested SELECTs) - I hope someone can help.

> Thanks a bunch in advance!

> Best regards,
> -Allan Jensen



Sun, 11 Apr 2004 06:53:30 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Embarassingly simple SQL question - what am I missing?!

2. Simple question..I am running a stored procedure from SQL PLUS

3. Simple question..I am running a stored procedure from SQL PLUS

4. Tricky query for me, should be simple for you

5. Simple Question - I am sure

6. Simple one what am i doing wrong

7. Simple set variable, what am I missing

8. Simple query fails - what am I missing?

9. SQL7, SIMPLE SIMPLE SIMPLE question

10. I am getting this message when i am tring to export or import anything using

11. error ORA-01855: AM/A.M. or PM/P.M. required

12. Busy Day = Slowdown from 12 AM - 5 AM


 
Powered by phpBB® Forum Software