CODEWITHSUNDEEP
sqloracle-database
Sriram
Sriram

Reputation: 23

oracle in clause along with rownum

I am not an oracle expert so I am hoping someone could help me with this question..

I have a scenario where I need to query the top 1000 records from a table and my query already has a IN clause for the where section. For example, lets say there is a table called employee which has 4 columns,

and I want to query for first 1000 records where state is in(MA or NY or NH), how do i do that?

select name, salary
from Employee
where state IN ('MA','NY','NH')

Now the problem is that if I use rownum < 1000 at the end, it is going to just find all results and show only the top 1000. But what I want to see if top 1000 of each State value in the IN Clause... Can someone please suggest the right way to do this...

Thanks in advance...

Upvotes: 2

Views: 117

Answers (2)

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

Use this:

Select name, salary
from Employee
where state IN ('MA','NY','NH')
Group by state
HAVING Count(*) <=1000
Order by state

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You want to use row_number():

select e.name, e.salary
from (select e.*, row_number() over (partition by state order by state) as seqnum
      from employee e
      where state in ('MA', 'NY', 'NH')
     ) e
where seqnum <= 1000;

I'm not sure what you mean by the "top 1000 of each state". You have no order by or suggestion of ordering in your question. And, SQL tables are inherently unordered. This returns 1000 values for each state from indeterminate rows.

Upvotes: 3

Related Questions