Extract the max date values for 4 different items from a list of thousands

Question

I have a query as follows

SELECT *
  FROM [VNPoller].[DBA].[uccpu1mUTMStats]
    WHERE resid in (SELECT resid
            FROM [VNPoller].[DBA].[ResourceView]
            WHERE Dataset in 
                (SELECT dataset     
                FROM [VNPoller].[DBA].[DatasetTable]
                WHERE datasetDescription LIKE '%CPU%'
                 )
             AND devID = '1157') 
    order by dttm desc

I have a list of devices that are polled every 5 mins, each poll adds a new row to the table with latest values, Im this case I am looking at CPU. However a device may have multiple CPU's so I may have 4 values for CPU over the last 5 mins, so 4 new entries in the DB. Each CPU has a unique resID. I need to see the Max (latest dttm value for each of the 4 resides. My query returns all the rows for the entire day. Or I can get is to display just one value using the max(dttm) but I need the other 3 values. Maybe a picture can help explain. Many Thanks for anyone who can offer some help here

screeshot

Im only interested in seeing the top 4 items( the max (dttm) for each of the different resides. the rest are duplicated but for early time stamps

Sample data from uccpu1mUTMStats table which actually contains thousands of rows:

dttm                    resID   cpmCPUTotalMonIntervalValue
2018-09-28 22:10:00.000 294324  0
2018-09-28 22:10:00.000 294325  0
2018-09-28 22:10:00.000 294432  1
2018-09-28 22:10:00.000 294482  0
2018-09-28 22:10:01.000 294415  0
2018-09-28 22:10:01.000 294433  1
2018-09-28 22:10:01.000 294669  0
2018-09-28 22:10:02.000 294396  0
2018-09-28 22:10:02.000 294397  0
2018-09-28 22:10:02.000 294416  0
2018-09-28 22:10:03.000 294417  0
2018-09-28 22:10:03.000 294434  1
2018-09-28 22:10:03.000 294435  1
2018-09-28 22:10:04.000 294398  0
2018-09-28 22:10:04.000 294399  0
2018-09-28 22:10:04.000 294418  0
2018-09-28 22:10:05.000 294400  0
2018-09-28 22:10:05.000 294419  0

The expected results are:

dttm                    resID   cpmCPUTotalMonIntervalValue
2018-10-05 15:21:37.000 294100  21
2018-10-05 15:21:24.000 294099  23
2018-10-05 15:20:53.000 294098  19
2018-10-05 15:20:16.000 294097  23

Add some sample table data and the expected result - as formatted text, not images. Take a look at https://stackoverflow.com/help/mcve as well. — jarlh, Oct 05 '18 at 13:49
Possible duplicate of [How to select top N from a table](https://stackoverflow.com/questions/3353446/how-to-select-top-n-from-a-table) — PM 77-1, Oct 05 '18 at 13:53

score 1 · Accepted Answer · answered Oct 05 '18 at 15:50

1

This uses the RANK() function to partition the results by resID, ranked by time. The TOP 1 WITH TIES limits the result set to just the most recent values.

SELECT TOP 1 WITH TIES
  *
  FROM [VNPoller].[DBA].[uccpu1mUTMStats]
    WHERE resid in (SELECT resid
            FROM [VNPoller].[DBA].[ResourceView]
            WHERE Dataset in 
                (SELECT dataset     
                FROM [VNPoller].[DBA].[DatasetTable]
                WHERE datasetDescription LIKE '%CPU%'
                 )
             AND devID = '1157') 
    ORDER BY RANK() OVER (PARTITION BY resID ORDER BY dttm DESC)

answered Oct 05 '18 at 15:50

Eric Brandt

7,886
3
18
35

Eric, how do you do it ? It works perfectly. Never even knew about the RANK function. Ill do some reading up to better understand it but it gives me exactly what I need. I may need to chop out some of the columns so rather than using * I will perhaps select specific columns. Ill give it a go. Thanks – James Bampton Oct 05 '18 at 16:04
It's one of the `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()` family of window(ing) functions. Also `LEAD`, `LAG`, and any number of interesting applications of the `OVER()` clause. Happy Googling! Glad it helped. – Eric Brandt Oct 05 '18 at 16:22

Isitar · Answer 2 · 2018-10-05T15:50:27.420

0

split into two queries it's easier to read:

the max id query:

SELECT resid, MAX(dttm)
FROM [VNPoller].[DBA].[uccpu1mUTMstats] s
GROUP BY s.resid

and now you can join this query to the other

SELECT *
FROM [VNPoller].[DBA].[uccpu1mUTMStats] AS r
JOIN (SELECT resid AS resid, MAX(dttm) as dttm
         FROM [VNPoller].[DBA].[uccpu1mUTMstats] s
         GROUP BY s.resid) AS sq ON sq.dttm = r.dttm
WHERE r.resid in (SELECT resid
        FROM [VNPoller].[DBA].[ResourceView]
        WHERE Dataset in 
            (SELECT dataset     
            FROM [VNPoller].[DBA].[DatasetTable]
            WHERE datasetDescription LIKE '%CPU%'
             )
         AND devID = '1157')

edited Oct 05 '18 at 15:50

answered Oct 05 '18 at 14:38

Isitar

1,286
12
29

Hi Isitar Just trying this to see if it works. How should I best join these two queries together ? – James Bampton Oct 05 '18 at 15:30
Ah, yes I see it now. Only problem is that the dttm value I need to select is from the uccpu1mUTMstats table and not the resourceView. – James Bampton Oct 05 '18 at 15:39
Top part works first - the scornd part I am seeing errors on line – James Bampton Oct 05 '18 at 15:48
GROUP BY s.resid) AS sq ON sq.dttm = r.dttm (sp with no column specified and dttm is invalid column name. Any ideas > – James Bampton Oct 05 '18 at 15:49
Almost there but I now have 11 rows and not 4, the first resID columns is showing duplicates, the second resID column has some new resides that I was not expecting. Ill double check the DB table to be sure. – James Bampton Oct 05 '18 at 16:06

Extract the max date values for 4 different items from a list of thousands

2 Answers2