-1

UPDATED

I have the following data in my database, I would like to retrieve data in highlight red border (to get the latest maintenance data of each equipment).

Data:

SQL Data Picture (I have updated the picture)

How should I construct the query?

restrict07
  • 1
  • 3
  • I guess you don't want something like....select * from table where convert(varchar, maintance_time, 112) = '23122019' * – Nissus Jul 22 '20 at 13:52
  • 1
    Does this answer your question? [Selecting first row per group](https://stackoverflow.com/questions/10930347/selecting-first-row-per-group) – SMor Jul 22 '20 at 13:54
  • @Nissus, I want to get latest maintenance date time data only... for all equipment – restrict07 Jul 22 '20 at 13:59
  • When you ask 'for each equipment' you mean group by name ?group by type ? or both? – Nissus Jul 22 '20 at 14:27
  • So, did you try anything beforehand? Seems a bit odd... however, you could perform a subquery such as `SELECT Name, Type, MAX(MaintenanceTime) AS MaxTime FROM ... GROUP BY Name, Type` and perform a join to your initial table such as `... JOIN ... on a.Name = b.name and a.Type = b.Type and a.MaintenanceTime = b.MaxTime` – Tyron78 Jul 22 '20 at 14:29
  • @Nissus by name only.... – restrict07 Jul 22 '20 at 14:31

3 Answers3

1

Something simple like that?

   select name, max(maintenance_time) 
    from table 
    group by name
Nissus
  • 306
  • 3
  • 13
0

Edited Answer:

This query below should work. Do update table name before running the query.

SELECT [Equipment], [Type], MAX(CONVERT(datetime, [MaintenanceTime], 131)) AS [Most_Recent_Maintenance]
FROM [TABLE_NAME]
GROUP BY [Equipment], [Type]

The additional bit of code I added was around datetime conversion of your MaintenanceTime column. SQL only does MIN/MAX filtering commands accurately when it's the right data type.

Based on what I see in your table, there's a potential it may not be (See link). I added an additional query to the fiddle for you to see the conversion.

Old Answer:

Based on the picture provided, it seems like each set of maintenance data is posted in 'batches'. Assuming the batch data is all posted using the same date/time, you can use the following query to obtain all records on the latest posting date:

SELECT * 
FROM [INSERT_TABLE_NAME]
WHERE DAY([Maintenance Time]) = (SELECT DAY(MAX([Maintenance Time])) FROM [INSERT_TABLE_NAME])

Or if your equipment is not always maintained together in the same batch timings, here's a query to figure out the most recent time each piece of equipment was maintained:

SELECT [Name], [Type], MAX([Maintenance Time]) AS [Most_Recent_Maintenance]
FROM [INSERT_TABLE_NAME]
GROUP BY [Name], [Type]

Be sure to update [INSERT_TABLE_NAME] to reflect your table naming. I've also created a fiddle for you to view what the query (with your dataset) will look like.

MTay
  • 139
  • 11
  • Hi MTay, I have updated my data, hope you understand... actually the maintenance will not the same for each equipment.. What I need to query is the latest maintenance data of each equipment sset – restrict07 Jul 23 '20 at 09:52
  • Hey there, I've updated my answer to reflect the new data / requirement. Let me know if you encounter any errors. – MTay Jul 23 '20 at 12:12
0

It appears that all your desired data arrives at the same time:

select *
from T
where maintenance_time = (select max(maintenance_time) from T);
shawnt00
  • 16,443
  • 3
  • 17
  • 22