SQL grouping Question

[rawger]

SQL QUERY-
select d.row_wid, d.x_hhold_num, deposits_end_bal
from w_household_d d, wc_hhold_balhstry_f f
where d.row_wid = f.household_wid
order by deposits_end_bal desc

QUESTION
The above query generates a list of all the customers in the descending order of amount they have in their accounts.

It generates a list of roughly 300000 customers.

How do I group this list which comes up in descending order, into groups of 30000( 30 k) each??

Thanks in advance

 

[cofibreak]

In SQL Server 2005, you could use the new ROW_NUMBER() function.

Something along these lines should work...

select t1.ROWID/ 30000 as grpid, t1.deposits_end_bal from
(
select row_number() OVER ( ORDERBY deposits_end_bal DESC ) AS ROWID, * from wc_hhold_balhstry_f
) t1
orderby t1.deposits_end_bal DESC

 

[rawger]

How would you populate the list after separating the values???

 

[cofibreak]

You can refer to any column on this line:
select t1.ROWID/ 30000 as grpid, t1.deposits_end_bal, <*here*> from
that is included in the inner table/query t1.

Exactly what are you trying to accomplish with the list?
If the problem space is detailed, there will be that much less going back and forth....

 

[emil]

You have to add a record counter and use it to create groups of 30k records:

First, create a counter field in a Query on w_household_d table, assuming row_wid is unique:

myQuery:

SELECT (SELECT COUNT(row_wid) FROM w_household_d AS a1 WHERE a1.row_wid<= a2.row_wid) AS [counter], a2.*
FROM w_household_d AS a2
ORDER BY a2.row_wid;

and modify your query:

SELECT INT(d.counter/30000) AS groupBy, d.counter,d.row_wid, d.x_hhold_num, d.deposits_end_bal, f.test
FROM myQuery: AS d, wc_hhold_balhstry_f AS f
WHERE (((d.row_wid)=f.household_wid))
GROUP BY d.counter,d.row_wid, d.x_hhold_num, d.deposits_end_bal, f.test, d.counter
ORDER BY d.counter,d.deposits_end_bal DESC;

You should group the out of the last query by groupBy.

 

[rawger]

My final objective is -

After I get this list of 300000 users and their account information ....I will arrange them in the descending order of the amount they have in their accounts.

After this I have to break this list ( which is already in descending order) into groups of 30000 users each . This will give me a rough lot of about 10 groups.

Then I have to make comparisons between these groups using various factors for investment/banking strategy purposes.

That is the reason that I need SQL query which can
- Get the Account information
-Break down the information into 10 groups of 30000 users each
- Populate a page with the results of the above query.

I just started working at this bank and kinda new to SQL , so I might be overlooking a lot of things. You r help is appreciated.

-Regards