All Forums Analytics
sheridany 12 posts Joined 04/06
11 May 2010
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

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?

sheridany 12 posts Joined 04/06
12 May 2010

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

You must sign in to leave a comment.