Sample without duplicates

Database
Enthusiast

Re: Sample without duplicates

Simple explanation is this.

for rank() function, if two records belong to the same partitioning expression value and also have same values for the order by columns, they get the same rank.

for row_number() , it gives a unique value for each record which has the same value for partitioning expression, so even if the order by column(s) have duplicate values, one will get a value and it will be incremented for the next one... and it's random AFAIK as to which record gets what value (between duplicate records).

You can refer functions & operators manual for more details.
Not applicable

Re: Sample without duplicates

Hi everyone, I need asap to get a sample of lets say 100 customers for each country.

Lets Suppose this scenario:

customer_no country_desc

1   argentina

2   argentina

3   argentina

4   ireland

5   ireland

6   ireland

7   usa

8   usa

And i need to get 2 customers for each Country.

Could you help me with this?

Thanks!

Enthusiast

Re: Sample without duplicates

try this

SELECT *
FROM TBL
QUALIFY ROW_NUMBER() OVER (PARTITION BY COUNTRY_DESC ORDER BY COUNTRY_DESC) = 1
SAMPLE 2