Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle PL/SQL Query Order By issue with Distinct

Tags:

sql

oracle

Does anyone know what is wrong with this query?

 SELECT DISTINCT c.CN as ClaimNumber, 
         a.ItemDate as BillReceivedDate, c.DTN as
 DocTrackNumber
         FROM ItemData a,
         ItemDataPage b,
         KeyGroupData c
         WHERE a.ItemTypeNum in (112, 113, 116, 172, 189)
         AND a.ItemNum = b.ItemNum
         AND b.ItemNum = c.ItemNum
         ORDER BY a.DateStored DESC;

I have done T-Sql most of my career and this looks correct to me, however this query is for an Oracle database and Toad just places the cursor on the a.DateStored in the Order By section. I'm sure this is elementary for anyone doing PL/SQL.

Thanks!

[EDIT] For future reference, the error given by SQL*Plus was: "ORA-01791: not a SELECTed expression"

like image 355
Chris Conway Avatar asked Oct 28 '08 14:10

Chris Conway


People also ask

How to use Oracle SELECT DISTINCT?

Oracle SELECT DISTINCT. Summary: in this tutorial, you will learn how to use the Oracle SELECT DISTINCT statement to query distinct data from tables. The DISTINCT clause is used in a SELECT statement to filter duplicate rows in the result set. It ensures that rows returned are unique for the column or columns specified in the SELECT clause.

Why can't I use order by with a distinct column?

The problem is that the columns used in the ORDER BY aren't specified in the DISTINCT. To do this, you need to use an aggregate function to sort on, and use a GROUP BY to make the DISTINCT work. Show activity on this post.

Should we add the distinct operation with extended sort key column CreationDate?

If we add the DISTINCT operation, it would be added between SELECT and ORDER BY: But now, with the extended sort key column CreationDate, the semantics of the DISTINCT operation has been changed, so the result will no longer be the same. This is not what we want, so both the SQL standard, and all reasonable databases forbid this usage.

How many null values can a SELECT DISTINCT query include?

If you use the SELECT DISTINCT statement to query data from a column that has many NULL values, the result set will include only one NULL value. See the locations table in the sample database.


2 Answers

You will need to modify the query as such:

SELECT DISTINCT c.CN as ClaimNumber, 
         a.ItemDate as BillReceivedDate, c.DTN as
 DocTrackNumber, a.DateStored
         FROM ItemData a,
         ItemDataPage b,
         KeyGroupData c
         WHERE a.ItemTypeNum in (112, 113, 116, 172, 189)
         AND a.ItemNum = b.ItemNum
         AND b.ItemNum = c.ItemNum
         ORDER BY a.DateStored DESC;

When doing a DISTINCT your order by needs to be one of the selected columns.

like image 59
Brian Schmitt Avatar answered Oct 29 '22 04:10

Brian Schmitt


Nevermind, executing in SQL Plus gave me a more informative answer. The DateStored needs to be in the select statement so this works:

    SELECT DISTINCT c.CN as ClaimNumber,          
a.ItemDate as BillReceivedDate, 
c.DTN as DocTrackNumber, 
a.DateStored         
FROM ItemData a,         
ItemDataPage b,         
KeyGroupData c         
WHERE a.ItemTypeNum in (112, 113, 116, 172, 189)         
AND a.ItemNum = b.ItemNum         
AND b.ItemNum = c.ItemNum         
ORDER BY a.DateStored DESC;
like image 2
Chris Conway Avatar answered Oct 29 '22 03:10

Chris Conway