return the last row that meets a condition in sql

Question

I have two tables:

Meter

ID         SerialNumber
=======================
1          ABC1
2          ABC2
3          ABC3
4          ABC4
5          ABC5
6          ABC6

RegisterLevelInformation

ID         MeterID    ReadValue    Consumption  PreviousReadDate    ReadType
============================================================================
1          1          250          250          1 jan 2015          EST
2          1          550          300          1 feb 2015          ACT
3          1          1000         450          1 apr 2015          EST
4          2          350          350          1 jan 2015          EST
5          2          850          500          1 feb 2015          ACT
6          2          1000         150          1 apr 2015          ACT
7          3          1500         1500         1 jan 2015          EST
8          3          2500         1000         1 mar 2015          EST
9          3          5000         2500         4 apr 2015          EST
10         4          250          250          1 jan 2015          EST
11         4          550          300          1 feb 2015          ACT
12         4          1000         450          1 apr 2015          EST
13         5          350          350          1 jan 2015          ACT
14         5          850          500          1 feb 2015          ACT
15         5          1000         150          1 apr 2015          ACT
16         6          1500         1500         1 jan 2015          EST
17         6          2500         1000         1 mar 2015          EST
18         6          5000         2500         4 apr 2015          EST

I am trying to group by meter serial and return the last actual read date for each of the meters but I am unsure as to how to accomplish this. Here is the sql I have thus far:

select a.SerialNumber, ReadTypeCode, MAX(PreviousReadDate) from Meter as a
left join RegisterLevelInformation as b on a.MeterID = b.MeterID
where ReadType = 'ACT'
group by a.SerialNumber,b.ReadTypeCode, PreviousReadDate
order by a.SerialNumber

I can't seem to get the MAX function to take effect in returning only the latest actual reading row and it returns all dates and the same meter serial is displayed several times.

If I use the following sql:

select a.SerialNumber, count(*) from Meter as a
left join RegisterLevelInformation as b on a.MeterID = b.MeterID
group by a.SerialNumber
order by a.SerialNumber

then each serial is shown only once. Any help would be greatly appreciated.

It's returning all of the read dates because you have included that column in your `GROUP BY` clause. Take it out, and see what you get. — Paul Griffin, Apr 15 '15 at 20:16

Kamil Gosciminski · Accepted Answer · 2015-04-16T07:14:35.640

Like @PaulGriffin said in his comment you need to remove PreviousReadDate column from your GROUP BY clause.

Why are you experiencing this behaviour?

Basically the partition you have chosen - (SerialNumber,ReadTypeCode,PreviousReadDate) for each distinct pair of those values prints you SerialNumber, ReadTypeCode, MAX(PreviousReadDate). Since you are applying a MAX() function to each row of the partition that includes this column you are simply using an aggregate function on one value - so the output of MAX() will be equal to the one without it.

What you wanted to achieve

Get MAX value of PreviousReadDate for every pair of (SerialNumber,ReadTypeCode). So this is what your GROUP BY clause should include.

select a.SerialNumber, ReadTypeCode, MAX(PreviousReadDate) from Meter as a
left join RegisterLevelInformation as b on a.MeterID = b.MeterID
where ReadType = 'ACT'
group by a.SerialNumber,b.ReadTypeCode
order by a.SerialNumber

Is the correct SQL query for what you want.

Difference example

ID         MeterID    ReadValue    Consumption  PreviousReadDate    ReadType
============================================================================
1          1          250          250          1 jan 2015          EST
2          1          550          300          1 feb 2015          ACT
3          1          1000         450          1 apr 2015          EST

Here if you apply the query with grouping by 3 columns you would get result:

SerialNumber | ReadTypeCode | PreviousReadDate
  ABC1       |    EST       | 1 jan 2015 -- which is MAX of 1 value (1 jan 2015)
  ABC1       |    ACT       | 1 feb 2015
  ABC1       |    EST       | 1 apr 2015

But instead when you only group by SerialNumber,ReadTypeCode it would yield result (considering the sample data that I posted):

SerialNumber | ReadTypeCode | PreviousReadDate
  ABC1       |    EST       | 1 apr 2015 -- which is MAX of 2 values (1 jan 2015, 1 apr 2015)
  ABC1       |    ACT       | 1 feb 2015 -- which is MAX of 1 value (because ReadTypeCode is different from the row above

Explanation of your second query

In this query - you are right indeed - each serial is shown only once.

select a.SerialNumber, count(*) from Meter as a
left join RegisterLevelInformation as b on a.MeterID = b.MeterID
group by a.SerialNumber
order by a.SerialNumber

But this query would produce you odd results you don't expect if you add grouping by more columns (which you have done in your first query - try it yourself).

Thank you for the very clear answer it is greatly appreciated I now understand what the group by function actually does!!! :) I have tried furthering this by declaring MAX(PreviousReadDate) as readdate and using this in a where function so I could check those who have had a read between today and 6 months ago — Jay, Apr 15 '15 at 22:49
Glad I could help with basic understanding - the rest is yours to go :) You can also accept the answer if you feel like it's exactly what you needed. — Kamil Gosciminski, Apr 16 '15 at 07:16

score 1 · Answer 2 · edited May 23 '17 at 12:14

You need to remove PreviousReadDate from your Group By clause. This is what your query should look like:

select a.SerialNumber, ReadTypeCode, MAX(PreviousReadDate) from Meter as a
left join RegisterLevelInformation as b on a.MeterID = b.MeterID
where ReadType = 'ACT'
group by a.SerialNumber,b.ReadTypeCode
order by a.SerialNumber

To understand how the group by clause works when you mention multiple columns, follow this link: Using group by on multiple columns

You will understand what was wrong with your query and why it returns all dates and the same meter serial is displayed several times. Good luck! Kudos! :)

return the last row that meets a condition in sql

2 Answers2