0

In my SQL query I'm trying to get data from database that has the newest combination of Date and Time for each Project No.

My formula is:

select [Project], [Machine], [Category], [Details], MAX(date  + ' ' + time) as [DateTime] 
from [SQL].[dbo].[ZMAC] group by [Project No], [Machine], [Repair Category], [Details]

Here is a part of the search result

| Project | Machine | Category |   Details   |         DateTime         |
------------------------------------------------------------------------
| M00004  |   A09   |    QC    | CENTER OVER |  2020-01-21 19:15:00.000 |
| M00004  |   A09   |    QC    |  FIRST CUT  |  2020-01-21 19:00:00.000 |
| M00006  |   C03   |    QC    |  PUSHER UP  |  2020-01-21 17:30:00.000 |

The query ends up giving me 2 rows of M00004 because Details is different. What I really wanted is for the table to return me 1 row for each Project.

| Project | Machine | Category |   Details   |         DateTime         |
------------------------------------------------------------------------
| M00004  |   A09   |    QC    | CENTER OVER |  2020-01-21 19:15:00.000 |
| M00006  |   C03   |    QC    |  PUSHER UP  |  2020-01-21 17:30:00.000 |

How do I tell the formula to only show me the latest entry only?

hjh93
  • 570
  • 11
  • 27

3 Answers3

1

You can use ROW_NUMBER() to generate an ordered list of the values for each Project according to Datetime descending, and then select only the rows which have row number = 1:

WITH CTE AS (
  SELECT [Project], [Machine], [Category], 
         [Details], date  + ' ' + time as [DateTime] 
         ROW_NUMBER() OVER (PARTITION BY [Project] ORDER BY (date  + ' ' + time) DESC) AS rn
  FROM [SQL].[dbo].[ZMAC]
)
SELECT [Project], [Machine], [Category], [Details],  [DateTime] 
FROM CTE
WHERE rn = 1
Nick
  • 138,499
  • 22
  • 57
  • 95
0

Use where not exists

select [Project], [Machine], [Category], [Details],  [DateTime] 
from [SQL].[dbo].[ZMAC] a
where not exists(
select 1 
from [SQL].[dbo].[ZMAC] b where a.Machine  = b.Machine  and a.[DateTime] < b.[DateTime] 
)
zip
  • 3,938
  • 2
  • 11
  • 19
0

You can find a related answer with the link below for the MAX(DATE)....

SQL Server: SELECT only the rows with MAX(DATE)

AndrewDev
  • 51
  • 8