Re: Remove Duplicate Rows

dale....@xxxxxxxxx wrote:
Thanks for the quick response!

Here's my complete query:

SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
Job.KSName, GETDATE() AS CURDATE

FROM DataHeader INNER JOIN
Data ON DataHeader.DataID = Data.DataID INNER JOIN
Object INNER JOIN
Job ON Object.ObjID = Job.MachineObjID ON
DataHeader.JobID = Job.JobID

Group By Job.JobID

But I'm getting the following error:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'Object.Name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Data.Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Job.KSName' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.


Any column that you don't want to group by needs to be enclosed in an
aggregate function (MIN or MAX for example). Your problem is obviously
a bit different to what you first asked for. The best way to post a
problem like this is to include enough code so that others can
reproduce it. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

.

Relevant Pages

  • Re: The Many Flavors of SQL - Can a SQL Server query work in MS Access?
    ... One thing is that if you have more than one join in the From clause you have to use Parentheses ... You might try building the base of the query using the query grid. ... inner join cmsopen.dbo.apm_vendor v ... in the Query Analyzer on a Windows 2000 Server box running SQL Server ...
    (microsoft.public.access.queries)
  • Re: Subquery in Where clause
    ... Your WHERE clause consists of a subquery. ... Maybe you wanted a correlated subquery with EXISTS there? ... "Expert SQL Server 2008 Encryption" ... FROM SP INNER JOIN SS ON SP.ItemNo = SS.ItemNo LEFT OUTER JOIN ...
    (microsoft.public.sqlserver.programming)
  • Re: Help on SQL syntax
    ... You can use AVGaggregate function with GROUP BY clause, ... Dejan Sarka, SQL Server MVP ... "Annette Malini" wrote in message ...
    (microsoft.public.sqlserver.server)
  • Re: Delete from a CTE that has a join
    ... The inner join On clause has 3 columns - So I ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.server)