CODEWITHSUNDEEP
sqloracle-databaseselect
Jett
Jett

Reputation: 880

Order by only one, of two distinct columns

I am selecting ColA and ColB in the same statement:

select distinct ColA, ColB, EDITABLE from TableA;

I would like to use ORDER BY lower(ColA), because I would like to sort ColA alphabetically, without taking capitalization into consideration. ColB does not need to be affect in this sorting and I would like to only sort ColA (which needs to be distinct because there are many instances of the same value in ColA).

I tried

select distinct ColA, ColB, EDITABLE
from TableA
ORDER BY lower(ColA)

and also saw this question concerning distinct and order by but when I tried 

select distinct ColA, ColB, lower(ColA), EDITABLE
from TableA
GROUP BY ColA
ORDER BY lower(ColA) ASC, ColA

I was unable to run the statement above. I am very new at SQL and would love some tips on why this didn't work and help on what I can improve this statement on

Upvotes: 2

Views: 226

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Your referenced question does not apply to your question. In that question, there is another column used for ordering, one not in the original select distinct. In your case, the order by is using a function on one of the original columns. There should be no need to repeat the expression in the SELECT.

You can also use group by:

select ColA, ColB, EDITABLE
from TableA
group by ColA, ColB, EDITABLE
order by lower(ColA);

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

This is your query:

select distinct ColA, ColB, lower(ColA), EDITABLE
from TableA
GROUP BY ColA
ORDER BY lower(ColA) ASC, ColA

And this is what it does:

1.   FROM clause: select rows from TableA.
2.   GROUP BY clause: Aggregate rows so as to get one row per ColA.
3.   SELECT clause:
3.1.   Show ColA. This is okay, ColA is what you group by.
3.2.   Show ColB. Which? There can be diferent ColB per ColA. This produces an error.
3.3.   Show lower(ColA). That is okay. You group by ColA, so lower(ColA) is also known.
3.4.   Show EDITABLE. Again: which? There can be diferent EDITABLE per ColA.
3.5.   DISTINCT: remove duplicate rows. This is superfluous, because there cannot be
       duplicate rows. They all have different ColA.
4.   ORDER BY clause:
4.1.   Sort by lower(ColA), so you have 'John' and 'john' together.
4.2.   Sort by ColA. This tells the DBMS whther to put 'John' or 'john' first.

I hope this explains how a query gets executed, what GROUP BY does and what is allowed in the SELECT clause. DISTINCT is very often a sign for a badly written query. Here it's merely superfluous, but quite often it is used as some defense against poor joins leading to duplicate rows. Whenever you see SELECT DISTINCT ask yourself what makes it necessary.

Upvotes: 2

SU7
SU7

Reputation: 1756

Mention all the columns in the Group By that you are Selecting

In SQLServer, it is like:

select distinct ColA, ColB, lower(ColA)
from TableA
GROUP BY ColA, ColB, lower(ColA)
ORDER BY lower(ColA) ASC

Upvotes: 7

Related Questions