|
|
Posted by dale.zjc on 04/10/06 23:27
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.
David Portas wrote:
> dale....@gmail.com wrote:
> > I've got the following table data:
> >
> > 11652 5.99
> > 11652 0.14
> > 12996 5.03
> > 12996 0.12
> > 12996 7.00
> >
> > And I need to write a query to return only rows 2 and 4, since the
> > remaining rows have duplicate IDs. I've tried the Group By, but am
> > having no luck.
> >
> > Thanks!
>
> What do you mean by "rows 2 and 4"? Those numbers refer to positions in
> the list of values you posted, but SQL Server knows nothing about that
> because tables in SQL have no logical order at all. In other words you
> haven't given enough information to answer your question.
>
> If these are the only two columns you have then probably the best you
> can do is:
>
> SELECT col1, MIN(col2) AS col2
> FROM your_table
> GROUP BY col1 ;
>
> or:
>
> SELECT col1, MAX(col2) AS col2
> FROM your_table
> GROUP BY col1 ;
>
> --
> 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
> --
Navigation:
[Reply to this message]
|