Sql-server – Use GROUP BY and return more than 2 columns

greatest-n-per-groupsql serversql-server-2008

I am trying to find the latest unit_status, and the timestamp of that status (cdts), for every unid I have in a table.

This is what I have come up with so far:

with cte as 
(
    select * 
    from un_hi 
    where ag_id = 'bfd' and cdts > '201708' and
    unit_status != 'uc'
)
select distinct(unid), max(cdts2) Last_GPS_Report_Date
from cte
where unid in ('C06','C07','D01','D03','D04','D06')
group by unid
order by unid

It correctly returns every unid and the time of their latest status (max(cdts)), but I want to see what the unit_status was.

I have tried taking this query and joining it to the UN_HI table (which is where this data is coming from), but I couldn't get that to work. Any other suggestions?

Best Answer

You can use ROW_NUMBER:

with cte as 
(
    select  *,
            RN = ROW_NUMBER() OVER(PARTITION BY unid ORDER BY cdts2 DESC)
    from un_hi 
    where ag_id = 'bfd' 
    and cdts > '201708' 
    and unit_status != 'uc'
)
select *
from cte
where RN = 1;

Related Solutions

Sql-server – Select MaxDate where hh:mm:ss makes the difference

In the OPENQUERY, you could get the max timestamp for all the dogs at once, and then join to the original PETS table to get the other columns, like so:

SELECT *
FROM [MyDatabase].[dbo].[DogTags] t1
JOIN OPENQUERY(ROPSA,
    'SELECT [TimeStamp], [Name], [Owner], [Address]
     FROM [CustomerDB].[PETS] t3
     JOIN (SELECT [Name], MAX(TimeStamp) AS TimeStamp
           FROM [CustomerDB].[PETS]
           GROUP BY [Name]) t4
     ON t3.[Name]=t4.[Name] AND t3.TimeStamp=t4.TimeStamp') t2
ON t1.TAGS collate DATABASE_DEFAULT = t2.Name collate DATABASE_DEFAULT

EDIT: Needed to add AS TimeStamp in the subquery.

EDIT: Needed to specify the database everytime in OPENQUERY.

Postgresql – Consolidating large table

I want to group records that have the same combination of class, ip_address, and hostname, and keep the highest timestamp for each day from each group.

Not using the column name timestamp (like you shouldn't either). It's a reserved word in SQL and a basic type name in Postgres. Using ts instead.

The query is surprisingly simple with DISTINCT ON:

SELECT DISTINCT ON (class, ip_address, hostname, ts::date) *
FROM   agent_log
WHERE  ts < now() - interval '7 days'
ORDER  BY class, ip_address, hostname, ts::date, ts DESC;

Detailed explanation:
Select first row in each GROUP BY group?

Best Answer

Related Solutions

Sql-server – Select MaxDate where hh:mm:ss makes the difference

Postgresql – Consolidating large table

Related Question