Database

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-20-2007
04:37 PM

06-20-2007
04:37 PM

Hi,

I have a table which has records as follows

EmpNo DepNo

1 10

2 10

3 20

4 30

5 20

6 40

I want to select a random sample of 3 records from the table , however i should not select a department number more than once.

Example

EmpNo DepNo

1 10

2 10

3 20

In the above case i should have selected DeptNo 10 only once and the results should have been

EmpNo DepNo

1 10

3 20

4 30

Sam

I have a table which has records as follows

EmpNo DepNo

1 10

2 10

3 20

4 30

5 20

6 40

I want to select a random sample of 3 records from the table , however i should not select a department number more than once.

Example

EmpNo DepNo

1 10

2 10

3 20

In the above case i should have selected DeptNo 10 only once and the results should have been

EmpNo DepNo

1 10

3 20

4 30

Sam

12 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-21-2007
09:47 AM

06-21-2007
09:47 AM

select *

from tab

qualify rank() over (partition by deptno order by 0) = 1 -- one random row per dept

sample 3

Dieter

from tab

qualify rank() over (partition by deptno order by 0) = 1 -- one random row per dept

sample 3

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-21-2007
03:07 PM

06-21-2007
03:07 PM

Hi Dieter,

This solution you gave does not work ok.

I tried it and got these results which is not ok

EMPNO DEPNO

4 30

3 20

5 20

Deptno 20 is getting repeated.

Sam

This solution you gave does not work ok.

I tried it and got these results which is not ok

EMPNO DEPNO

4 30

3 20

5 20

Deptno 20 is getting repeated.

Sam

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-21-2007
10:10 PM

06-21-2007
10:10 PM

Use this instead

select *

from test

qualify rank() over (partition by deptno order by deptno) = 1 -- one random row per dept

sample 3

Thanks,

Vinay

select *

from test

qualify rank() over (partition by deptno order by deptno) = 1 -- one random row per dept

sample 3

Thanks,

Vinay

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-22-2007
12:44 AM

06-22-2007
12:44 AM

I think the trouble is because RANK() returns the same value for same deptno (in this case it's always 1).

So I think piggy backing on Dieter's original solution and using ROW_NUMBER instead, things might work.

SELECT *

FROM MYTABLE

QUALIFY ROW_NUMBER() OVER (partition by deptno order by 0) = 1

SAMPLE 3;

So I think piggy backing on Dieter's original solution and using ROW_NUMBER instead, things might work.

SELECT *

FROM MYTABLE

QUALIFY ROW_NUMBER() OVER (partition by deptno order by 0) = 1

SAMPLE 3;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-22-2007
03:26 AM

06-22-2007
03:26 AM

Hi Joe,

of course you're right, i tested it with a row_number, but i couldn't sent an email from that PC, so i keyed it in again with a RANK ;-)

Dieter

of course you're right, i tested it with a row_number, but i couldn't sent an email from that PC, so i keyed it in again with a RANK ;-)

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-29-2007
04:20 PM

06-29-2007
04:20 PM

Thanks a lot guys!That did work perfectly as required.

Sorry for the late reply

Sam

Sorry for the late reply

Sam

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-10-2007
01:00 AM

07-10-2007
01:00 AM

Suppose when I run

select *

from test

qualify rank() over (partition by deptno order by deptno) = 1

where my table looks like

deptno x y

10 1 2

10 2 3

10 3 4

20 1 2

20 2 3

30 1 2

I am getting all the records from my table. Is this scenario correct? If yes what should be done to get unique deptno records by using partition and not unique command.

select *

from test

qualify rank() over (partition by deptno order by deptno) = 1

where my table looks like

deptno x y

10 1 2

10 2 3

10 3 4

20 1 2

20 2 3

30 1 2

I am getting all the records from my table. Is this scenario correct? If yes what should be done to get unique deptno records by using partition and not unique command.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-10-2007
01:08 AM

07-10-2007
01:08 AM

if your partitioning and ordering expressions are the same, then your rank output will always be 1.

you can use row_number() instead of rank as mentioned in the above posts.

you can use row_number() instead of rank as mentioned in the above posts.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-10-2007
01:52 AM

07-10-2007
01:52 AM

WOW

It worked Joed. Thanks a lot.

Can you brief me what made it work? ie diff btw ROW_NUMBER and RANK in this case. And in what criteria it selects the records when deptno being the same (as we are partitioning by deptno).

It worked Joed. Thanks a lot.

Can you brief me what made it work? ie diff btw ROW_NUMBER and RANK in this case. And in what criteria it selects the records when deptno being the same (as we are partitioning by deptno).