Clicky

I have a table that has 7 million unique ID's.
I'm looking for 24k that are coming off a spreadsheet.

I have read only access to the tables, so I don't think I can create
a temp table to join off of for filtering.

What would be the best way to return the ID's I want, and their
corresponding fields, given that IN only allows 1,000 arguments (I think)?

asked 10/03/2011 02:13

JustinW's gravatar image

JustinW ♦♦


4 Answers:
select * from yourtable where yourid in (1,2,3,....1000)
or yourid in (1001,1002,...., 2000)
or yourid in (2001, ...., 3000)

etc

change the inlists to be your actual values.

link
sdstuber's gravatar image

sdstuber

Is this the fastest way?
Is there not like a CTE thing you can do in oracle, like in SQLServer?
link
JustinW's gravatar image

JustinW

sure you could but it's unlikely to be faster
probably not slower either  I'd expect them to be about the same

if you like cte better, try this...

with cte as (select 1 n from dual union all select 2 from dual union all select 3 from dual
union all .....
select 24000 from dual)
select * from yourtable where yourid in (select n from cte)
link
sdstuber's gravatar image

sdstuber

thanks for doing both!
link
JustinW's gravatar image

JustinW

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 10/03/2011 02:13

Seen: 271 times

Last updated: 10/03/2011 07:52