"ivo welch" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> Let's presume that I have columns (A,B) of numerical random data and
> NR rows. I want to sort them into groups of, say, 64 each based on A.
> Then within each of these A-based groups, I want to sort them into
> groups of 16 each based on B. (Possibly repeat again for C.)
>
> For example, if NR=2056, I would sort them by A and place data into 32
> groups (let me call them A1, A2, ..., A32) of 64 rows each. I now
> want to sort each of these 32 A groups in themselves according to
> their B value. That is, rows in each A group should be assigned an
> index (rank) from 1 to 64 to each of them, based on B's numeric
> value. Because 4 rows go into each group, I would have 16 B groups
> (B1,B2,...,B16) within each A group.
I don't quite see how all this complexity is substantially different
from just sorting by A then by B:
select A, B from myTable
order by A, B;
If you insist, here's a rather inefficient SQL query that assigns a
sequential rank to each record:
select t1.A, t1.B, count(*) as rank
from myTable t1 join myTable t2 on
(t2.A < t1.A or (t2.A = t1.A and t2.B < t1.B))
group by t1.A, t1.B
order by t1.A, t1.B;
or equivalently
select A, B,
(select count(*) from myTable t2
where t2.A < t1.A or (t2.A = t1.A and t2.B < t1.B)) as rank
from myTable t1
order by A, B;
Your group ranks can then be derived from overall rank: groupA =
rank/64, groupB=(rank%64)/16. Working this into a SQL statement is left
as an exercise for the reader.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
