Return records with the latest date

blackbox

Hi all,

I have a table which contains multiple records for the same room, it contains history for each time the room is audited. Each time the room is audited the date is written to the "CompStamp" field in the format "2016-03-01". A filed named SeqID1307 is alos written to with the temperature of water, SeqId1303 is a decimal type field. So it could contain 20, 24.3 or any variant of a decimal number.

Now I am trying to extract records from the table where the variable "CompStamp" has the latest date.

I have tried the following:
Query 1.

SELECT UniqueID, SeqID1307, Room,  CompStamp
FROM ROSE_Hist
WHERE SeqID1307 > 20 AND CompStamp in (SELECT MAX(CompStamp) from ROSE_Hist GROUP BY Room

This returns 3 records.

If I use Navicat and run:

Query 2.

SELECT  UniqueID, Room,  MAX(CompStamp)  

FROM ROSE_Hist
WHERE  SeqID1307 > 20 GROUP BY Room ORDER BY Room ASC

This returns 303 records.

When I look at the 303 records the SeqID1307 fields they contain the number 20 or more.

I think my question is, which Query should i be using to reurn the correct records, Query 1 or Query 2 or am I doing this completely wrong.

Many thanks in advance for your time.

Cheers.

NogDog

Sounds like the first one is what you're looking for, as it will only return records with the latest date. In the second one, you're using the max() of that date as an aggregate function, which is not the same as limiting which rows gets selected via the where clause.

blackbox

NogDog;11054491 wrote:
Sounds like the first one is what you're looking for, as it will only return records with the latest date. In the second one, you're using the max() of that date as an aggregate function, which is not the same as limiting which rows gets selected via the where clause.

Hi Nogdog,

Thats what I thought but Query 1 returns only three records when I know there are a lot more than that. This is a little odd.

Query 2 retruns 303 which is around what I would expect. I need to take a harder look at this in the morning.

I will let you know.

Many thanks again.

Cheers.

NogDog

It could also mean I misunderstand the requirement. 🙂

If you want the latest record for each Room, regardless of when that latest record is, then:

SELECT UniqueID, SeqID1307, Room,  CompStamp
FROM ROSE_Hist AS rh
WHERE SeqID1307 > 20 AND CompStamp = (
  SELECT MAX(CompStamp) from ROSE_Hist where Room =  rh.Room
)

blackbox

Hi Nogdog,

for what ever reason I am having issues with this query.

If I use Navicat to do a simple filter on the data:

Query 1

SELECT * FROM `ROSE_Hist` WHERE `SeqID1307` > '20' AND CompStamp > '2015-0101' ORDER BY `Room` DESC

This returns 126 records, I have attached an extract to show what is returned.

[ATTACH]5315[/ATTACH]

If I use:

Query 2

SELECT UniqueID, SeqID1307, Room,  CompStamp
FROM ROSE_Hist AS rh
WHERE SeqID1307 > 20 AND CompStamp = (
  SELECT MAX(CompStamp) from ROSE_Hist where Room =  rh.Room
)

The result returns 3 records.

If Query 1 returns 126 should not Query 2 return more than 3 records.

Sorry to be a pain but I need to get this working, (1) for the script and (2) so I can understand how to do the same for future work.

Again, many thanks for your time.

Cheers.

Rose_Hist.png

NogDog

Question: what is the data type of the CompStamp column? Also, just in case it matters, what DBMS are you using?

Oh, and just to clarify, do you want only the latest row for each qualifying room number (e.g. query 2) or all rows for each room that are greater than the date in the where clause?

blackbox

NogDog;11054503 wrote:
Question: what is the data type of the CompStamp column? Also, just in case it matters, what DBMS are you using?

Oh, and just to clarify, do you want only the latest row for each qualifying room number (e.g. query 2) or all rows for each room that are greater than the date in the where clause?

Hi,

The DBMS is MySQL, the CompStamp column is "date" only in its default format, example 2016-01-03.

If ROSE_HIST has records where the Room = 1234, all with different CompStamp dates. I want to return the record with the latest date, Example:

RecordId 1, Room 1234 CompStamp 2015-09-03
RecordId 2, Room 1234 CompStamp 2015-10-17
RecordId 3, Room 1234 CompStamp 2016-02-10

Return record 3.

But only if SeqID1307 > 20

Thanks again.

NogDog

Ooh...wait...think I see the problem: if you want the latest row for each room where the SeqID1307 is > 20, then you'd need that check in the sub-query, too:

SELECT UniqueID, SeqID1307, Room,  CompStamp
FROM ROSE_Hist AS rh
WHERE SeqID1307 > 20 AND CompStamp = (
  SELECT MAX(CompStamp) from ROSE_Hist
  WHERE Room =  rh.Room AND SeqID1307 > 20
)

(Though I feel like there should be a more efficient way to do that. :queasy: )

blackbox

NogDog;11054507 wrote:
Ooh...wait...think I see the problem: if you want the latest row for each room where the SeqID1307 is > 20, then you'd need that check in the sub-query, too:
SELECT UniqueID, SeqID1307, Room,  CompStamp
FROM ROSE_Hist AS rh
WHERE SeqID1307 > 20 AND CompStamp = (
  SELECT MAX(CompStamp) from ROSE_Hist
  WHERE Room =  rh.Room AND SeqID1307 > 20
)
(Though I feel like there should be a more efficient way to do that. :queasy: )

Hi Nogdog,

Many thanks for all your help, I think this now works.

Cheers