I am in the process of learning SQL, and I figured that SQlite would be
a good way to get started. SQL is interesting in that its simplicity is
deceptive. I can do simple things with it, but there is a lot more
power here that I do not comprehend yet.
I was hoping someone could tell me if SQL has the ability to do
dependent ranks. Here is what I mean:
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 would want to be able to do this again with C groups and D groups,
etc. I am trying to keep my question simple.)
non-SQL pseudo code to describe this better is not hard:
sort(A);
for i in 1 to count(A) {
groupA[i] = "A"+truncate(i/64);
}
foreach A from groupA {
restrict to set A only
sort(B);
for i in 1 to 64 {
groupB[i] = "B"+truncate(i/4);
fullid[i] = groupA[i] + groupB[i]
}
}
Is this reasonably easy to do with SQL?
One way to do this would be to map each numerical value into a character
string of equal length and with equal alpha sort order; and then use
string concatenation at each step.
Another way to do this would be to go to a programming language. This
would be easy to do recursively.
/iaw
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
