Selecting 2 colums - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting 2 colums

Author

Topic

finnj6
Starting Member

1 Post

Posted - 2009-05-27 : 15:14:39

Hi I want to select A Day, Time, and MAX(Value) from a table and only group by day. So in my results I want the Day the max value and what time it occured at.

My select Statement is

SELECT Day,MAX(Value) From Sensors Where Name='Bang' Group by Day 
UNION
SELECT Time From Sensors Where MAX(Value)= Value

The error Im getting is
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

But I don't know what this means.

I'm sure I'm doing something stupid but I can't figure it out. Hopefully a fresh pair of eyes will help.

Thanks
Jeff

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-27 : 15:31:17

Your second SELECT query is failing because you're trying to use an aggregate function MAX() in the WHERE clause. Instead you could do something like this:

SELECT Time FROM Sensors WHERE Value = (SELECT MAX(Value) FROM Sensors)

Some days you're the dog, and some days you're the fire hydrant.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-28 : 01:48:09

Also there should be equal number of columns in each select statements

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 11:21:06

[code]
SELECT s1.Day,s1.Time,s2.latest
From Sensors s1
inner join (SELECT Day,MAX(Value) as latest
FROM Sensors
GROUP BY Day
)s2
ON s2.Day=s1.Day
AND s2.latest=s1.Value
Where s1.Name='Bang'
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources