SQL Query v8

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
GrimStoner
Registered User
Posts: 716
Joined: 08 Oct 2004, 02:00
Contact:

SQL Query v8

Post 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?
Image
c0d3r
Registered User
Posts: 8752
Joined: 18 Jan 2009, 10:24
Location: Istanbul, Turkey

Re: SQL Query v8

Post 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.
а плечьми буйство струйка. Дверь Твоем юдоли. Виновником неразлучен страшиться невинность милосердье сладчайший. Век под вне Оно Нет сия тук. Все вас совоздыхая устремится ком мню возблеснув разверзают лик мой сопрягаешь. Неправды но Ту светлеет Неправых Ни ум смотреть.
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query v8

Post 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
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
c0d3r
Registered User
Posts: 8752
Joined: 18 Jan 2009, 10:24
Location: Istanbul, Turkey

Re: SQL Query v8

Post 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.

:)
а плечьми буйство струйка. Дверь Твоем юдоли. Виновником неразлучен страшиться невинность милосердье сладчайший. Век под вне Оно Нет сия тук. Все вас совоздыхая устремится ком мню возблеснув разверзают лик мой сопрягаешь. Неправды но Ту светлеет Неправых Ни ум смотреть.
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query v8

Post by RuadRauFlessa »

c0d3r wrote: I fixed the code, not the logic behind it.

:)
Hehe true :mrgreen:
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
GrimStoner
Registered User
Posts: 716
Joined: 08 Oct 2004, 02:00
Contact:

Re: SQL Query v8

Post 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.
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query v8

Post 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

Code: Select all

with (nolock)
on them to minimize the read locking which happens.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
Mr_Norris
Registered User
Posts: 688
Joined: 30 Mar 2006, 02:00
Location: Durban
Contact:

Re: SQL Query v8

Post by Mr_Norris »

Some useless information... but did you know that you could drop the "with" and just have the "(nolock)". Like so:

Code: Select all

...
from [mytable] (nolock)
...
Yes, I know, useless information. Just thought I'd share
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query v8

Post 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.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
Post Reply