Sql to find oldest created accounts and tag it across all acounts

Database
Fan

Sql to find oldest created accounts and tag it across all acounts

Hi,

My input:

c1 c2 c3
-- -- ------
A1 a 23-03-2014
A1 b 07-07-2015
A1 c 14-01-2013
A1 d 27-02-2016

My Desired output:

c1 c2 c3 c4
-- -- ------ ---
A1 a 23-03-2014 c
A1 b 07-07-2015 c
A1 c 14-01-2013 c
A1 d 27-02-2016 c

The new column(c4) is to be derived from c2 with oldest date(c3)

Can anybody help achieving this in a query.

Thanks.
2 REPLIES
Junior Contributor

Re: Sql to find oldest created accounts and tag it across all acounts

What's your Teradata release?

In 14.10 there's FIRST_VALUE:

FIRST_VALUE(c2) 
OVER (PARTITION BY c1??
ORDER BY c4)
Fan

Re: Sql to find oldest created accounts and tag it across all acounts

Its TD 14.10 and the suggestion given worked like a charm.

I was thinking in complicated ways and its achieved in a single step now.

Thanks a lot Dieter.