Return highest two values in a group

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

General SQL Server Forums

New to SQL Server Programming

Return highest two values in a group

Author

Topic

Christinas29
Starting Member

2 Posts

Posted - 2011-06-13 : 08:43:02

Hi there,

My name is Christina, and I'm (practically) brand new to this business! I'm currently building a report in BIDS (SSRS 2008) to act as a quarterly energy usage statement.

I have a number of properties, each of which have x number of energy readings. I am currently able to return all of these readings for each property, but I would like to firstly return the latest (and therefore highest) two readings and secondly, calculate the difference between the two rows.

Would anyone be able to advise the best method to do this?

Many, many thanks,
Christina

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-06-13 : 08:58:59

post the query which you have written till now...

Christinas29
Starting Member

2 Posts

Posted - 2011-06-13 : 12:08:48

Hi Ahmed,

Please see the query below. I'm trying to compare the latest two readings for 'LoggedEnergy'.

Many thanks,
Christina

SELECT VW_CurrentTenantsGasReading.PlaceRef, VW_CurrentTenantsGasReading.[TENANCY-REF], VW_CurrentTenantsGasReading.ADDRESS,
VW_CurrentTenantsGasReading.[POST-CODE], VW_CurrentTenantsGasReading.MeterNo, VW_CurrentTenantsGasReading.ReadoutTime,
VW_CurrentTenantsGasReading.InfoCode, VW_CurrentTenantsGasReading.LoggedEnergy, VW_CurrentTenantsGasReading.LoggedVolume,
VW_CurrentTenantsGasReading.HourCounter, VW_CurrentTenantsGasReading.Temp1, VW_CurrentTenantsGasReading.Temp2,
VW_CurrentTenantsGasReading.Power1, VW_CurrentTenantsGasReading.Flow1, VW_CurrentTenantsGasReading.T1R1LastMonth,
VW_CurrentTenantsGasReading.PeakPowerFlow, VW_CurrentTenantsGasReading.E8m3xT1, VW_CurrentTenantsGasReading.E9m3xT2,
VW_CurrentTenantsGasReading.[TNCY-STATUS], [ARREARS-SNAPSHOT].[ARREARS-OFCR], [ARREARS-SNAPSHOT].[TENANCY-REF] AS Expr1
FROM VW_CurrentTenantsGasReading INNER JOIN
[ARREARS-SNAPSHOT] ON VW_CurrentTenantsGasReading.[TENANCY-REF] = [ARREARS-SNAPSHOT].[TENANCY-REF] AND
VW_CurrentTenantsGasReading.PlaceRef = [ARREARS-SNAPSHOT].[PLACE-REF]
WHERE (VW_CurrentTenantsGasReading.[TNCY-STATUS] = N'CUR')
ORDER BY VW_CurrentTenantsGasReading.[TENANCY-REF], VW_CurrentTenantsGasReading.ReadoutTime DESC, VW_CurrentTenantsGasReading.ADDRESS,
VW_CurrentTenantsGasReading.MeterNo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources