Neelam Sharma Neelam Sharma - 1 year ago 57
SQL Question

Get distinct result from ordered result of subquery in oracle

We are working with oracle database. I need result in sorted order. So I have created a query that sort on id first then I have applied query over subquery result to get distinct para.

Query is :

select distinct(para)
from (
select para, id
from book
where title='2'
and chapter_id='2'
and subchap='1'
and para is not null
order by id)
order by para


This return list as :

a
aa
aaa
bb
c
d
dd


But I want this list as :

a
c
d
aa
bb
dd
aaa


I want list of distinct para list? How can I achieve this using oracle query?

Answer Source

If you want to get distinct paras ordered by their ids. You can try the following query:

SELECT para
FROM book 
WHERE title='2' 
  and chapter_id='2' 
  and subchap='1' 
  and para is not null 
GROUP BY para
ORDER BY MIN(id)

Notice, that it sorts on MIN(id) so if para has multiple ids it will be sorted by the smallest one.

If para, id combination is unique, you can group by both fields:

SELECT para
FROM book 
WHERE title='2' 
  and chapter_id='2' 
  and subchap='1' 
  and para is not null 
GROUP BY para, id
ORDER BY id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download