Re: Tricky Order By in SQL?

"Kathy M. Davis" <kmd_at_ssds.com> wrote:
>SELECT UNIQUE GRADE_REQ, GRADE_CD
>FROM REF_GRADE
>WHERE GRADE_CD > 'F5' AND GRADE_CD < 'Q5'
>ORDER BY GRADE_CD;
>
>
>I have a table REF_GRADE with several columns. I only need two of them
>for this application: GRADE_REQ and GRADE_CD. GRADE_CD is the key to the
>table.
>
>Sample Data:
>
>GRADE_REQ GRADE_CD
>LTS H5
>LTS I5
>MAJ E5
>
>I want the query to return unique GRADE_REQs and ORDER BY GRADE_CD:
>MAJ
>LTS
>
>The problem is when I include the GRADE_CD in the SELECT statement, in
>order to do the ORDER BY, Oracle sees the GRADE_CD as unique, and it
>returns both LTS H5 and LTS I5.
>
>Any ideas?? Thanks in advance :)
>
>Kathy Davis
>kmd_at_ssds.com
>GRC International, Inc.
>Fayetteville, NC
>
>
>

Maybe I'm missing something obvious, but it seems to me that you are asking for something that is impossible, or that the grade code has some business rules around it that you're not telling us.

You can't have only one row returned for a unique GRADE_REQ and then sort by GRADE_CD if there are multiple values for the GRADE_CD. How would it sort? What if in the example above the first LTS GRADE_CD was A5? Would it appear before MAJ (since A5 < E5) or after MAJ as I5 > E5?

Perhaps there's a business rule hidden here? Perhaps grade code for different GRADE_REQs are consecutive? So you know that the GRADE_CD for LTS will be within a certain range, always greater than the GRADE_CD for MAJ? If so, then you could include a DECODE statement so that all GRADE_CD values within a certain range are evaluated as equal. Then the SELECT UNIQUE won't pull the multiple rows.

Or perhaps you can decide that you will sort on the MAX (or MIN) GRADE_CD for each GRADE_REQ. Then you can write the query as a GROUP BY and again you will only get one row for each GRADE_REQ.

Beth Received on Mon Nov 27 1995 - 00:00:00 CET