How do I get the max date row?

Database

How do I get the max date row?

Data looks like this

Row Num    Loc Num    Loc Name         DTTM

1                 1234         Philadelphia       05/21/2016  23:58:00

2                 1234         Philadelphia       02/17/2015  23:58:00

3                 1234         Philadelphia       01/12/2013  23:58:00

4                 1234         Philadelphia       11/07/2001  00:00:00

In this example I need to write a statement to get only row 1 because it has the most recent DTTM.  It's a tempural table so since the location is closed my query looks like this:

NONSEQUENCED VALIDTIME

Select

*

FROM

LOC 

WHERE

Loc_Num = 1234

2 REPLIES
Enthusiast

Re: How do I get the max date row?

You can use qualify as shown below:

drop table loc;

create table LOC
(
row_num integer,
Loc_Num integer,
Loc_Name varchar(50),
DTTM timestamp
);

insert into loc values (1,1234, 'Philadelphia', '2016-05-21 23:58:00');
insert into loc values (2,1234, 'Philadelphia', '2015-02-17 23:58:00');
insert into loc values (3,1234, 'Philadelphia', '2013-01-12 23:58:00');
insert into loc values (4,1234, 'Philadelphia', '2001-11-07 00:00:00');

SELECT *
FROM
LOC
WHERE
LOC_NUM = 1234
QUALIFY (ROW_NUMBER() OVER(PARTITION BY LOC_NUM ORDER BY DTTM DESC)=1);

Hope this helps!

Re: How do I get the max date row?

Thanks Sakthi!  This worked