How to query last report ID from multiple results

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 2005 Forums

Transact-SQL (2005)

How to query last report ID from multiple results

Author

Topic

pee2002
Starting Member

16 Posts

Posted - 2010-06-08 : 07:15:25

Hi there!

I´m with some difficult to do this query.

I have this:


SELECT     IDReport AS [ID Report], IDHardware AS [ID Hardware], DataHoraReport AS [Último Report], Versao AS Versão, DataInstalacao AS Instalação, 
                      DataUltimaActualizacao AS [Última Actualização]
FROM         Report

in which result this:

How can i query those results to obtain this:

Please, be aware that i would like the last report ID, not the last date.

Can anyone help me out?

Best Regards

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-08 : 07:22:59

SELECT IDReport AS [ID Report], IDHardware AS [ID Hardware], DataHoraReport AS [Último Report], Versao AS Versão, DataInstalacao AS Instalação,DataUltimaActualizacao AS [Última Actualização]
FROM Report r inner join
(Select max(IDReport) as IDReport,IDHardware from Report group by IDHardware )r1
on r.IDReport =r1.IDReport

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

pee2002
Starting Member

16 Posts

Posted - 2010-06-08 : 07:31:33

That solution gives me the follow error:


Msg 8120, Level 16, State 1, Line 7
Column 'Report.IDHardware' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'IDReport'.
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'IDHardware'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources