CODEWITHSUNDEEP
sqlteradata
xiaodai
xiaodai

Reputation: 16064

How to keep the first row of a certain group based on some condition on Teradata SQL?

I have table in Teradata that looks like this

ID  | Date     | Values
------------------------
abc | 1Jan2015 | 1
abc | 1Dec2015 | 0
def | 2Feb2015 | 0
def | 2Jul2015 | 0

I want to write a piece of SQL that keeps only the earliest date of each ID. So the result I wanted is 

ID  | Date     | Values
------------------------
abc | 1Jan2015 | 1
def | 2Feb2015 | 0

I know there is top n syntax but it only seems to work on the whole table not within groups.

Basically how do I do a top n within groups?

Upvotes: 3

Views: 4985

Answers (2)

dnoeth
dnoeth

Reputation: 60482

TOP can be easily rewritten using ROW_NUMBER:

select *
from tab
qualify
   row_number() over (partition by id order by date) = 1

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can do this using row_number():

select t.*
from (select t.*,
             row_number() over (partition by id order by date) as seqnum
      from table t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions