user2924127 user2924127 - 5 months ago 9
SQL Question

Query - Sort this query by a column which returns Distinct rows

I have the query:

SELECT DISTINCT
"id",
"fn" || ' ' || "ln" || '/' || "bi" || '('|| TRUNC((months_between(sysdate, "bi") /12)) || ')/' || "pol" || '/ ' || "pol2" AS "INFO",
"cn" ,
"ci",
TO_CHAR("di" , 'DD-MON-YYYY') AS "DIST",
"cif" AS "Cand. Indp." AS "CIF FLAG",
TO_CHAR("ecd", 'DD-MON-YYYY') AS "ECD DATE",
trim(both '/' from ("DSD") ) AS "DSD DATE",
trim(both '/' from ("TSD" )) AS "TSD DATE",
"com" AS "COMMENTS",
"SMF" AS "SM Flag"
FROM "MY_TABLE"
ORDER BY "ln";


I get the error ORA-01791: Not a selected expression.

I googled the error and the problem seems to be the distinct and order by don't work together. I tried to group by instead of distinct and use max("ln"), but with the concatenated columns I have not been able to figure out how to group them. I am looking essentially how to sort the results on ln?

Answer

You are not select by ORDER BY "ln"

you can only select by selected column then or add in select

SELECT DISTINCT
    "id",
     "fn" || ' ' || "ln" || '/' ||  "bi" || '('|| TRUNC((months_between(sysdate, "bi") /12)) || ')/' || "pol" || '/ ' || "pol2" AS "INFO",
     "ln",
    "cn" ,
    "ci",
    TO_CHAR("di" , 'DD-MON-YYYY')  AS "DIST",
    "cif" AS  "Cand. Indp." AS "CIF FLAG",
    TO_CHAR("ecd", 'DD-MON-YYYY')   AS "ECD DATE",
     trim(both '/' from ("DSD") ) AS  "DSD DATE",
    trim(both '/' from ("TSD"   ))  AS "TSD DATE",
    "com"  AS "COMMENTS",
    "SMF" AS "SM Flag"
FROM "MY_TABLE"
ORDER BY "ln";

or change order by

SELECT DISTINCT
    "id",
     "fn" || ' ' || "ln" || '/' ||  "bi" || '('|| TRUNC((months_between(sysdate, "bi") /12)) || ')/' || "pol" || '/ ' || "pol2" AS "INFO",
    "cn" ,
    "ci",
    TO_CHAR("di" , 'DD-MON-YYYY')  AS "DIST",
    "cif" AS  "Cand. Indp." AS "CIF FLAG",
    TO_CHAR("ecd", 'DD-MON-YYYY')   AS "ECD DATE",
     trim(both '/' from ("DSD") ) AS  "DSD DATE",
    trim(both '/' from ("TSD"   ))  AS "TSD DATE",
    "com"  AS "COMMENTS",
    "SMF" AS "SM Flag"
FROM "MY_TABLE"
ORDER BY 2;