# SQL query select newest entry of multiple items **URL:** \ **Category:** Ignition\ **Created:** [November 8, 2023, 7:09pm UTC](https://forum.inductiveautomation.com/t/sql-query-select-newest-entry-of-multiple-items/81677 "2023-11-08T19:09:54Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![dcan87](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dcan87/32/54071_2.png) [@dcan87](https://forum.inductiveautomation.com/u/dcan87)\ **Post date:** [November 8, 2023, 7:09pm UTC](https://forum.inductiveautomation.com/t/sql-query-select-newest-entry-of-multiple-items/81677/1 "2023-11-08T19:09:54Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/9/f938f7e2e0b3243ee28653756c8a5f61250946b2.png) 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. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 8, 2023, 7:27pm UTC](https://forum.inductiveautomation.com/t/sql-query-select-newest-entry-of-multiple-items/81677/2 "2023-11-08T19:27:38Z")
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. ---
**Author:** ![dcan87](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dcan87/32/54071_2.png) [@dcan87](https://forum.inductiveautomation.com/u/dcan87)\ **Post date:** [November 8, 2023, 7:34pm UTC](https://forum.inductiveautomation.com/t/sql-query-select-newest-entry-of-multiple-items/81677/3 "2023-11-08T19:34:50Z")
> [@paul-griffith](#): > > Postgres 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! ---
**Author:** ![Felipe\_CRM](https://avatars.discourse-cdn.com/v4/letter/f/dfb087/32.png) [@Felipe\_CRM](https://forum.inductiveautomation.com/u/Felipe_CRM)\ **Post date:** [November 8, 2023, 7:35pm UTC](https://forum.inductiveautomation.com/t/sql-query-select-newest-entry-of-multiple-items/81677/4 "2023-11-08T19:35:34Z")
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. ---
**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\ **Post date:** [November 8, 2023, 7:41pm UTC](https://forum.inductiveautomation.com/t/sql-query-select-newest-entry-of-multiple-items/81677/5 "2023-11-08T19:41:34Z")
If i understand your question correctly I believe this will give you the result you want: ```auto 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. ---
**Author:** ![dcan87](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dcan87/32/54071_2.png) [@dcan87](https://forum.inductiveautomation.com/u/dcan87)\ **Post date:** [November 8, 2023, 7:48pm UTC](https://forum.inductiveautomation.com/t/sql-query-select-newest-entry-of-multiple-items/81677/6 "2023-11-08T19:48:45Z")
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! ---
**Author:** ![Felipe\_CRM](https://avatars.discourse-cdn.com/v4/letter/f/dfb087/32.png) [@Felipe\_CRM](https://forum.inductiveautomation.com/u/Felipe_CRM)\ **Post date:** [November 8, 2023, 7:53pm UTC](https://forum.inductiveautomation.com/t/sql-query-select-newest-entry-of-multiple-items/81677/7 "2023-11-08T19:53:38Z")
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. ---
**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\ **Post date:** [November 8, 2023, 8:01pm UTC](https://forum.inductiveautomation.com/t/sql-query-select-newest-entry-of-multiple-items/81677/8 "2023-11-08T20:01:37Z")
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. ---
**Author:** ![dcan87](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dcan87/32/54071_2.png) [@dcan87](https://forum.inductiveautomation.com/u/dcan87)\ **Post date:** [November 8, 2023, 8:15pm UTC](https://forum.inductiveautomation.com/t/sql-query-select-newest-entry-of-multiple-items/81677/9 "2023-11-08T20:15:45Z")
Beautiful! This seems to be working as I needed!