Re: Tricky Order By in SQL?

Try select grade_req, max(grade_cd)

      from ref_grade
     where grade_cd > 'F5' and grade_cd < 'Q5'
     group by grade_cd

However, you also might want to consider min(grade_cd) instead of max. This depends on your application and requirements.

This is closest to what you are asking for.

Atul

In article <49dciu$jmu_at_netope.harvard.edu>, "Beth J. Katcher" <katcher_at_a1.tch.harvard.edu> writes:
|> "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 Wed Nov 29 1995 - 00:00:00 CET