Need help with the ON part of an INNER JOIN 1

[VickyC]

hello to all

I have some SQL that works perfectly, but believe I am using bad coding practice. I'd like to see if there is a better way...

Consider tblX shown below. It is ORDERed BY B, then C, then A. I want to produce a query output that has an ID column with values 1, 2, 3, 4, 5... etc, as well as all of the records in tblX. The desired output is shown below as qryX.

[b]
tblX                                    qryX
  A       B       C                      ID    A       B       C   [/b]  
  14      1       2                       1    14      1       2
  11      2       2                       2    11      2       2
 100      2       6                       3   100      2       6
 106      3       1                       4   106      3       1
  12      3       2                       5    12      3       2
  19      3       2                       6    19      3       2
 111      4       1                       7   111      4       1

As I've learned from this forum, this can be done by INNER JOINing qryX to itself, then using a GROUP BY, and finally using SELECT COUNT(*) AS ID, ...etc.

My problem is the ON part of the INNER JOIN. Here's what I currently do...

SELECT COUNT(*) AS ID, Q1.A, Q1.B, Q1.C 
FROM qryX AS Q1  INNER JOIN   qryX AS Q2
   [b]ON 10000*(Q1.B) + 100*(Q1.C) + Q1.A >= 10000*(Q2.B) + 100*(Q2.C) + Q2.A[/b]
GROUP BY Q1.A, Q1.B, Q1.C
ORDER BY COUNT(*);

Because I know the small range of values for each of A, B and C, I know that this will always work for now, but things could change some day resulting in my ON statement producing a false output.

Any thoughts about a more robust way to code the ON statement?
Thanks for any help.

 

[VickyC]

I made a few mistypes on my posting. Here is the correct version

hello to all

I have some SQL that works perfectly, but believe I am using bad coding practice. I'd like to see if there is a better way...

Consider qryX shown below. It is ORDERed BY B, then C, then A. I want to produce a query output that has an ID column with values 1, 2, 3, 4, 5... etc, as well as all of the records in qryX. The desired output is shown below as qryY.

 [b]

qryX                                    qryY  [/b]
  A       B       C                      ID    A       B       C     
  14      1       2                       1    14      1       2
  11      2       2                       2    11      2       2
 100      2       6                       3   100      2       6
 106      3       1                       4   106      3       1
  12      3       2                       5    12      3       2
  19      3       2                       6    19      3       2
 111      4       1                       7   111      4       1

As I've learned from this forum, this can be done by INNER JOINing qryX to itself, then using a GROUP BY, and finally using SELECT COUNT(*) AS ID, ...etc.

My problem is the ON part of the INNER JOIN. Here's what I currently do...

SELECT COUNT(*) AS ID, Q1.A, Q1.B, Q1.C 
FROM qryX AS Q1  INNER JOIN   qryX AS Q2
   [b]ON 10000*(Q1.B) + 100*(Q1.C) + Q1.A >= 10000*(Q2.B) + 100*(Q2.C) + Q2.A [/b]
GROUP BY Q1.A, Q1.B, Q1.C
ORDER BY COUNT(*);

Because I know the small range of values for each of A, B and C, I know that this will always work for now, but things could change some day resulting in my ON statement producing a false output.

Any thoughts about a more robust way to code the ON statement?
Thanks for any help.

 

[PHV]

Perhaps this:

SELECT COUNT(*) AS ID, Q1.A, Q1.B, Q1.C 
FROM qryX Q1 INNER JOIN qryX Q2 ON Q1.B >= Q2.B
WHERE (Q1.B>Q2.B)
   OR (Q1.B=Q2.B AND Q1.C>Q2.C)
   OR (Q1.B=Q2.B AND Q1.C=Q2.C AND Q1.A>=Q2.A) 
GROUP BY Q1.A, Q1.B, Q1.C
ORDER BY 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

thanks PHV. That's a really interesting WHERE clause. Your solution also can easily be extended for ranking by any number of columns.