SQL query select newest entry of multiple items

Ignition
1

image
image950×437 37.2 KB

I am trying to select all SAP results that have my Parent input value and then only show the latest entry of each.

For example, L01 has 7 machines (by SAP number). Notes will be entered on a continued basis on each machine.

This named query in bound to the dataset of my subview table component which is filtered by the main table row.

So, the query needs to return all 7 machines for L01 but only their latest Note.

2

What DB flavor is this? Postgres allows you to group and order by on the same clause, but I'm not sure about other engines.

3

DB is MS SQL Server.

Less than 8hrs self-taught so far. I will keep researching examples that I can tailor to my case.

I will look into group and ordering in mean time, thank you!

4

This solution looks pretty neat for SQL Server, or you might try some of the other ones:

It uses cross apply which I have never heard about before but looks to be similar to a join and apparently is really fast for this kind of operation.

2 Likes
5

If i understand your question correctly I believe this will give you the result you want:

SELECT
	[CommonName],
	[Note],
	[SAP],
	[Timestamp]
FROM (
	SELECT 
		[CommonName],
		[Note],
		[SAP],
		[Timestamp],
		ROW_NUMBER() OVER (PARTITION BY [SAP] ORDER BY [Timestamp] DESC) AS RN
	FROM [MachineComentTracker]
	WHERE [Parent] = :myParent
) D
WHERE [RN] = 1

Using ROW_NUMBER in an inner query to partition by the column SAP, which based on your explanation, seems like it's L01, L02, ..., L07? Then ordering by the column Timestamp

In the outer query, we're selecting RN = 1 to get the first entry for each partition in the inner query.

3 Likes
6

Looks like that is a pretty useful command if you have two tables. Unfortunately, I am working with one.

Thanks for the read though, never know when that may come up in the future!

7

You should be able to cross apply on the same table, just like you could do a self JOIN:

Anyway, the row_number() solution is pretty classic and straightforward so I'd probably try that one first and maybe check out this one if that one ends up running too slow for your data.

1 Like
8

I had no idea TOP 1 WITH TIES existed... this is really sweet!! No longer do I have to wrap my query just to get the most recent record for a partition.

2 Likes
9

Beautiful! This seems to be working as I needed!

2 Likes