OLAP functions

Analytics
Enthusiast

OLAP functions

I am trying to utilize the olap functions to speed up a large query. In particular, a person can show up more than one time in a day across several days.

It looks like this:

day1 abc
day1 abc
day1 edf
day 2 abc
day2 edf

I want to keep only instance of each person by day and keep the first instance in the sorted order. they can show up every day but I only want the earliest instance.

My code look something like this.

select xxxxx

ROW_NUMBER() OVER(PARTITION BY date_field
ORDER BY
customer, date field
) AS rownum

At the conclusion, I use the qualify statement which is qualify rownum=1.

Is this the correct syntax based on what I am trying to do?

1 REPLY
Enthusiast

Re: OLAP functions

I figured it out by doing some testing. the one that works is

row_num() over(partition by customer id order by customer id, date field) as rownum
at the end of the query you include....
qualify rownum =1