CODEWITHSUNDEEP
sqloracle-database
Tomas
Tomas

Reputation: 85

Oracle ordering by several same meaning columns

I have to make sortable table like this:

Sortable table:

building_id | building_age | title              |
-------------------------------------------------
1           | 100          | New york buil      |
2           | 50           | House 1            |
3           | 50           | House 10           |

From these tables:

Building Table:

building_id | building_age | building_type_1_FK |  building_type_2_FK
---------------------------------------------------------
1           | 100          | null               |        1
2           | 50           | 1                  |        null
3           | 50           | 2                  |        null

building_type_1:

type_id     | title        | diff1              |
-------------------------------------------------
1           | New york buil| blablabla          |

building_type_2:

building_id | title        |
----------------------------
1           | House 1      |
2           | House 10     |
3           | House 500    |

While joining these tables I get several title columns where one of them is not null. Is there any way to sort by title and select top 10 results without fetching all the data and then sorting in the app?

p.s.. I know that in general this architecture is not good, but I can't change it.

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Yes. You want to do a left outer join to the two tables, and then bring the results together:

select b.building_id, b.building_age, coalesce(bt1.title, bt2.title) as title
from building b left outer join
     building_type_1 bt1
     on b.building_type_1_FK = bt1.type_id left outer join
     building_type_2 bt2
     on b.building_type_2_FK = bt2.building_id;

To get the top 10 results in Oracle:

select *
from (select b.building_id, b.building_age, coalesce(bt1.title, bt2.title) as title
      from building b left outer join
           building_type_1 bt1
           on b.building_type_1_FK = bt1.type_id left outer join
           building_type_2 bt2
           on b.building_type_2_FK = bt2.building_id
      order by title
     ) b
where rownum <= 10;

Upvotes: 1

Related Questions