CODEWITHSUNDEEP
sqloracle-database
user15277765
user15277765

Reputation:

How can I write the query showing the first half of the data in ORACLE SQL?

Suppose we have a table. For this table how can I write the query showing the first half of the data in ORACLE SQL?

Example table is below:

Jobs Salary
A 15000
B 12500

I want to get the following table (half of the table above) as a result of the query.

Jobs Salary
A 15000

Upvotes: 0

Views: 2114

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

If the DB version is 12c+, then ORDER BY followed by FETCH clause might be used such as

SELECT *
  FROM job_list  
 ORDER BY Salary DESC
 FETCH FIRST 50 PERCENT ROWS ONLY

presuming you need the descendingly ordered salaries. But, you should notice that query returns half of (total number of rows + 1) / 2 whenever the table has odd-numbered rows of data. Btw, if also ties (equal salary values) should be included within the first half for the ordered fetched rows, then replace ONLY keyword with WITH TIES

Demo

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

If you just want half of your result you can use below query to get only even number of rows:

with cte (jobs,salary,rn) as (
select jobs,salary, row_number()over(order by userid)  from table  )
select jobs,salary from cte where mod(rn,2)=0

Upvotes: 0

ekochergin
ekochergin

Reputation: 4129

Though I really don't see any reason why someone would need that, here how I'd solve it

select jobs, salary
  from (select jobs, salary, count(1) over() cnt
          from some_table)
 where rownum <= cnt/2; -- this condition is to cut the half of result entries

Perhaps you might need to sort it first using an "order by" clause within the subquery

Upvotes: 0

Related Questions