SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Msg 8120, Level 16, State 1, Line 2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Donno
Starting Member

South Africa
3 Posts

Posted - 11/20/2012 :  08:59:05  Show Profile  Reply with Quote
I am trying to generate a report where by i get the latest date/time stamp for a device. but getting the following error.

Msg 8120, Level 16, State 1, Line 2
Column 'Donovan.dbo.Vehicle.iHardwareID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Code:SELECT distinct v.sSerialNo
, iHardwareID
,v.sDescription
,(g.iVehicleID)
,max (g.dtDateTime) datetime
,g.bIgnition
,g.fLatitude
,g.fLongitude
,g.fSpeed

from [Donovan].[dbo].[GPSData] as G
join [Donovan].[dbo].[Vehicle] as V

on v.iID = g.iVehicleID
group by ( v.sSerialNo)

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/20/2012 :  09:09:18  Show Profile  Reply with Quote
In most cases, when you have an aggregate function (MAX in your example), all the columns that are in the select list must either be inside an aggregate function, or they should also be listed in the GROUP BY clause. So your query should be one of these:
-- 1
SELECT v.sSerialNo,
       iHardwareID,
       v.sDescription,
       (g.iVehicleID),
       MAX(g.dtDateTime)                  [DATETIME],
       g.bIgnition,
       g.fLatitude,
       g.fLongitude,
       g.fSpeed
FROM   [Donovan].[dbo].[GPSData]       AS G
       JOIN [Donovan].[dbo].[Vehicle]  AS V
            ON  v.iID = g.iVehicleID
GROUP BY
       (v.sSerialNo),
       iHardwareID,
       v.sDescription,
       (g.iVehicleID),
       g.bIgnition,
       g.fLatitude,
       g.fLongitude,
       g.fSpeed
       
--- 2 
SELECT v.sSerialNo,
       MAX(g.dtDateTime)                  [DATETIME]
FROM   [Donovan].[dbo].[GPSData]       AS G
       JOIN [Donovan].[dbo].[Vehicle]  AS V
            ON  v.iID = g.iVehicleID
GROUP BY
       (v.sSerialNo)
But neither of these might be what you are looking for. If you are looking for all the columns corresponding to the latest g.dtDatetime, do the following:

SELECT * FROM
(
	SELECT v.sSerialNo,
		   iHardwareID,
		   v.sDescription,
		   (g.iVehicleID),
		   g.dtDateTime [datetime],
		   g.bIgnition,
		   g.fLatitude,
		   g.fLongitude,
		   g.fSpeed,
		   ROW_NUMBER() OVER (PARTITION BY v.sSerialNo ORDER BY g.dtDateTime DESC) AS RN
	FROM   [Donovan].[dbo].[GPSData]       AS G
		   JOIN [Donovan].[dbo].[Vehicle]  AS V
				ON  v.iID = g.iVehicleID
)s WHERE RN = 1;       
Go to Top of Page

Donno
Starting Member

South Africa
3 Posts

Posted - 11/20/2012 :  09:13:41  Show Profile  Reply with Quote
Thank you for your reply

Under normal circumstances there may be multiple Serial numbers that we need the latest date time stamp?

i am not sure if the soltuion give will just give the latest record or the latest reord for the specific serial number?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/20/2012 :  09:18:40  Show Profile  Reply with Quote
The third bit of code that I posted would do just that - give you one row for each serial number corresponding to the latest record (latest being defined as the max value of dtDateTime from GPSData table.
Go to Top of Page

Donno
Starting Member

South Africa
3 Posts

Posted - 11/20/2012 :  09:21:36  Show Profile  Reply with Quote
Thank you much appreciated,

i only saw that after my post.

again thank you
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/20/2012 :  10:06:58  Show Profile  Reply with Quote
you are quite welcome.0
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000