RRN and JOIN…
I have two files and I'd like to join and make a group-by on a xycod, in order to count the number of occurrences. This is the question: What happens on the RRN filed when i try to do group by statement? There is one-to-many relation between records and RRNs, so which is the RRN assigned to it?
Software/Hardware used:
Software/Hardware used:
ASKED:
October 13, 2008 1:27 PM
UPDATED:
August 22, 2010 10:05 PM
Answer Wiki:
I'n not sure what you are doing.
Sounds like you are doing an SQL join.
Why does the RRN matter?
If it's so you can determine the XYCOD wouldn't this statement do it?
Select count(*), a.XYCOD
from tablea a join tableb b
on a.XYCOD = b.XYCOD
group by XYCOD
To see all answers submitted to the Answer Wiki: View Answer History.
yes, i’m doing a SQL join…and ok, the role of RRN is not relevant to obtain the result of statement.
it’s a conceptual curiosity only…
infact there are many “one-to-many” relation between records and RRNs, and i’d like to know which is the RRN assigned…
for example max(RRN(<file>))
…
Well, I think they have created a temp file so the RRN’s of that file would have no relationship to either parent file.
for example max(RRN(<file>))
For that example, it would be the highest record number in <file>.
For GROUP BY operations, you can’t reasonably retrieve RRN() in the SELECT list because it’s a scalar function. Only aggregate functions may be used in a GROUP BY result set field list, e.g., SUM() and MAX() or MIN(). To use it on by itself, it would also need to be specified in the GROUP BY clause which would make the GROUP BY clause meaningless since each RRN() value will be unique.
Tom