0

I have been trying to solve a question where I am ask to fetch 50 % of record from table

My Table :

Column names

WORKER_ID
FIRST_NAME
LAST_NAME
SALARY
JOINING_DATE
DEPARTMENT

My Query :

select * from worker_details where rownum <= (select round(count(rownum)/2) from worker_details);

Even though I got the output but then also not satisfied

As I research over internet and got one article where the author mention like the Their is no guarantee of order when using rownum

Can someone elaborate and explain me in detail how to achieve the 50 % record from table and why no guarantee of order for rownum ?

yyy62103
  • 81
  • 5
  • Which column(s) provide the ordering you want here? – Tim Biegeleisen Aug 31 '22 at 05:05
  • I've explained how `ROWNUM` works in my answer. `ROWNUM` is something we don't need any longer. It was used to give numbers to rows, before Oracle featured the standard `ROW_NUMBER` function and it was used for top n queries, before Oracle featured the standard `FETCH FIRST ROW(S)` clause. – Thorsten Kettner Aug 31 '22 at 05:23

1 Answers1

2

To select 50% from the table:

select * from mytable fetch first 50 percent rows only;

This selects 50% of the table's rows arbitrarily. If you want to have a particular half, use an ORDER BY clause to tell the DBMS how to determine the rows to return, i.e. by which column order to give preference to one row over another.

As to your second question: ROWNUM is just the sequence number for the row in the order Oracle happens to find it for you. For instance:

select *
from mytable
where <some conditions>
and rownum <= 5
order by id;

will have Oracle look for table rows that match <some conditions>. The first row it happens to find, will get #1. the next #2, and so on until it has found five rows. These five arbitrary rows of the dataset that matches <some conditions> then get ordered by ID.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • how can you get the NEXT 50 PERCENT of the table after doing this FIRST 50 PERCENT if there's no ID to reverse the list, sorry for asking out of OP Topic. – Æthenwulf Nov 15 '22 at 01:47
  • @Æthenwulf: If the table doesn't change and there is no ID and no other unique key you can order your results by, then `order by rowid` asc/desc. – Thorsten Kettner Nov 15 '22 at 07:48