Selecting all columns using distinct against one specific column

Question

I appreciate that this question has been asked before but I am struggling to find an answer that will even run within Oracle 10g (10.2.0.5.0)

I have a table called BASIC which contains approximately 70 columns. Currently, I return a specified number of rows using the following code (as an example) - the result being the first 20 members who have a MEMBNO after 5000

SELECT * FROM BASIC WHERE MEMBNO>5000 AND ROWNUM <=20 ORDER BY MEMBNO;

Within the 20 rows returned, several of the rows have the same value in the NINO column

I would like to modify my SELECT statement to return the next 20 rows with distinct/unique NINO values

Simply wrapping a DISTINCT around the * gives me an ORA-00936: missing expression error, plus it would not be as precise as I would like.

with dis as (select distinct membno, nino from basic where membno > 5000 and rownum <= 20) select * from basic b, dis where dis.membno = b.membno and dis.nino = b.nino order by b.memno ...but the join should be on the primary key — wxyz, Nov 06 '13 at 09:08
Apologies for that @a_horse_with_no_name - an oversight on my part. I will endeavour to remove it — Mike, Nov 06 '13 at 09:50

score 2 · Answer 1 · answered Nov 06 '13 at 09:09

2

Can you try the code below:- I have used analytical query concept to fetch only distinct nino values.

     select * from 
        (SELECT b.*,row_number() over (partition by nino order by MEMBNO  ) rn 
FROM BASIC b WHERE MEMBNO>5000)
 where rn =1 AND ROWNUM <=20 ORDER BY MEMBNO;

Let me know in case you encounter any issues.

answered Nov 06 '13 at 09:09

user2342436

492
3
17

Thanks @user2342436 - the code you posted seems to run fine. I will need to examine the actual results to ensure it is returning what I want. I did find another solution which I'm posting as an answer – Mike Nov 06 '13 at 09:30

score 1 · Accepted Answer · answered Nov 06 '13 at 09:37

I think I have found a solution via another source

This shows the rows where there are duplicates...

select * from basic where rowid not in (select min(rowid) from basic group by nino)

This shows the rows with the duplicate rows removed...

select * from basic where rowid in (select min(rowid) from basic group by nino)

Then I can add my row count and membno filters for the final result...

select * from basic where rowid in (select min(rowid) from basic where membno>6615 group by NINO) and rownum <=20 order by membno;

Selecting all columns using distinct against one specific column

2 Answers2