Msg 8120, Level 16, State 1, Line 2

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

SQL Server 2008 Forums

Transact-SQL (2008)

Msg 8120, Level 16, State 1, Line 2

Author

Topic

Donno
Starting Member

3 Posts

Posted - 2012-11-20 : 08:59:05

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-20 : 09:09:18

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;

Donno
Starting Member

3 Posts

Posted - 2012-11-20 : 09:13:41

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?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-20 : 09:18:40

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.

Donno
Starting Member

3 Posts

Posted - 2012-11-20 : 09:21:36

Thank you much appreciated,

i only saw that after my post.

again thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-20 : 10:06:58

you are quite welcome.0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources