marmar
marmar

Reputation: 121

How to query distinct number of account numbers from table?

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

Answers (2)

ScaisEdge
ScaisEdge

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

Arjan
Arjan

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

Related Questions