Reputation: 121
I have a query that gives me usage for a customer in a period of time. I want to create a subquery in the "and" statement that asks for account number.
where 1=1
and acct.client_co_account_id in
(
select client_co_account_id
from account
where 1=1
and rownum <= 50000
)
I want usage stats for 50,000 distinct customer accounts. However, I am actually just getting 50,000 rows but not 50,000 ACCOUNTS. This query is going to give me 96 usage for each day, for one week, for one account. So it's a lot of information. There should be 672 entries for each account out of 50,000.
It's driving me nuts! By the way, the above is just a snapshot of the query. There's a lot more, I'm just working on the subquery.
THANKS!
Upvotes: 0
Views: 450
Reputation: 133380
you could use distinct and mysql don't have rownum but limit
where acct.client_co_account_id in
(
select distinct client_co_account_id
from account
order by client_co_account_id limit 50000
)
and you don't need where 1=1
but for this is better use a join
from mytable inner join (
select distinct client_co_account_id
from account
order by client_co_account_id limit 50000
) t on t.client_co_account_id = mytable.acct.client_co_account_id
the inner join is more efficent and permit of avoid the limitation related to max dimension of the result of a subselect in a IN clause
Upvotes: 2
Reputation: 9884
If you want each client_co_account_id
to appear once, the distinct
keyword is your friend. And if you want 50,000 results, you'll need to limit the number of results and not check the row numbers
select distinct client_co_account_id
from account
where 1=1
limit 50000
Upvotes: 0