Page 1 of 1
SQL Query v8
Posted: 17 Feb 2010, 11:09
by GrimStoner
Another one...
I have a table with 5 columns...
* RowID (guid)
* [Date] (datetime)
* AssetID (guid)
* Value1 (int/varchar...)
* Value2 (int/varchar...)
I need to find the most recent values of Value1 and Value2 for all AssetID's.
This query
Code: Select all
SELECT MAX([Date]), AssetID FROM MyTable GROUP BY AssetID
does give me the most recent date of those values, but when I try to include them, i.e.
Code: Select all
SELECT MAX([Date]), AssetID, Value1, Value2 FROM MyTable GROUP BY AssetID
I get the error
Code: Select all
Column 'whatever' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
How do I include those columns in the returned table?
Re: SQL Query v8
Posted: 17 Feb 2010, 11:12
by c0d3r
SELECT MAX([Date]), AssetID, Value1, Value2 FROM MyTable GROUP BY AssetID, Value1, Value2
If you don't aggregate (e.g. use MAX, SUM, COUNT, MIN...) a column, it needs to be included in the GROUP BY.
Re: SQL Query v8
Posted: 17 Feb 2010, 11:25
by RuadRauFlessa
c0d3r wrote:SELECT MAX([Date]), AssetID, Value1, Value2 FROM MyTable GROUP BY AssetID, Value1, Value2
If you don't aggregate (e.g. use MAX, SUM, COUNT, MIN...) a column, it needs to be included in the GROUP BY.
which would be pointless as to what he actually wants.
Code: Select all
SELECT
AssetID,
(SELECT TOP1 [dbo].[MT].[Value1] FROM [dbo].[MyTable] WITH (NOLOCK) AS [MT] WHERE [dbo].[MT].[AssetID] = [dbo].[MyTable].[AssetID] ORDER BY [MT].[Date] DESC),
(SELECT TOP1 [dbo].[MT].[Value2] FROM [dbo].[MyTable] WITH (NOLOCK) AS [MT] WHERE [dbo].[MT].[AssetID] = [dbo].[MyTable].[AssetID] ORDER BY [MT].[Date] DESC)
FROM
[dbo].[MyTable] WITH (NOLOCK)
GROUP BY
[dbo].[MyTable].[AssetID]
should do the trick
Re: SQL Query v8
Posted: 17 Feb 2010, 11:30
by c0d3r
RuadRauFlessa wrote:c0d3r wrote:SELECT MAX([Date]), AssetID, Value1, Value2 FROM MyTable GROUP BY AssetID, Value1, Value2
If you don't aggregate (e.g. use MAX, SUM, COUNT, MIN...) a column, it needs to be included in the GROUP BY.
which would be pointless as to what he actually wants.
I fixed the code, not the logic behind it.
Re: SQL Query v8
Posted: 17 Feb 2010, 11:32
by RuadRauFlessa
c0d3r wrote:
I fixed the code, not the logic behind it.
Hehe true
Re: SQL Query v8
Posted: 17 Feb 2010, 12:02
by GrimStoner
RuadRauFlessa wrote:c0d3r wrote:SELECT MAX([Date]), AssetID, Value1, Value2 FROM MyTable GROUP BY AssetID, Value1, Value2
If you don't aggregate (e.g. use MAX, SUM, COUNT, MIN...) a column, it needs to be included in the GROUP BY.
which would be pointless as to what he actually wants.
Code: Select all
SELECT
AssetID,
(SELECT TOP1 [dbo].[MT].[Value1] FROM [dbo].[MyTable] WITH (NOLOCK) AS [MT] WHERE [dbo].[MT].[AssetID] = [dbo].[MyTable].[AssetID] ORDER BY [MT].[Date] DESC),
(SELECT TOP1 [dbo].[MT].[Value2] FROM [dbo].[MyTable] WITH (NOLOCK) AS [MT] WHERE [dbo].[MT].[AssetID] = [dbo].[MyTable].[AssetID] ORDER BY [MT].[Date] DESC)
FROM
[dbo].[MyTable] WITH (NOLOCK)
GROUP BY
[dbo].[MyTable].[AssetID]
should do the trick
Thanks...
Code: Select all
SELECT
AssetID,
(SELECT TOP 1 [MT].[DateTime] FROM [dbo].[MyTable] AS [MT] WHERE [MT].[CollarID] = [MyTable].[AssetID] ORDER BY [MT].[DateTime] DESC),
(SELECT TOP 1 [MT].[Value1] FROM [dbo].[MyTable] AS [MT] WHERE [MT].[CollarID] = [MyTable].[AssetID] ORDER BY [MT].[DateTime] DESC),
(SELECT TOP 1 [MT].[Value2] FROM [dbo].[MyTable] AS [MT] WHERE [MT].[CollarID] = [MyTable].[AssetID] ORDER BY [MT].[DateTime] DESC)
FROM
[dbo].MyTable
GROUP BY
[dbo].MyTable.AssetID
did the trick.
Re: SQL Query v8
Posted: 17 Feb 2010, 12:10
by RuadRauFlessa
Inner selects does place a very heavy burden on the SQL server though. I would suggest whenever you use them that you do use something such as
on them to minimize the read locking which happens.
Re: SQL Query v8
Posted: 17 Feb 2010, 23:42
by Mr_Norris
Some useless information... but did you know that you could drop the "with" and just have the "(nolock)". Like so:
Yes, I know, useless information. Just thought I'd share
Re: SQL Query v8
Posted: 19 Feb 2010, 07:38
by RuadRauFlessa
Yes although I like to use the full syntax. You never know which version of SQL you might have to connect the code to.