0

This is related to: Why is selecting specified columns, and all, wrong in Oracle SQL?

The query:

select is_parent, animals.* from animals order by is_parent

throws the error:

[Error] ORA-00960: ambiguous column naming in select list

Which makes sense as is_parent is duplicated.

Can anyone tell me a simple fix for this, as the original query select is_parent, animals.* from animals is very nice and quick to return all other columns. I looked at this link but the technique to remove the ambiguity does not seem to apply.

Community
  • 1
  • 1
Alex
  • 15,186
  • 15
  • 73
  • 127

6 Answers6

2

Something you can try:

1) Use the table name (or its alias) in the ORDER BY clause:

SQL> select is_parent, animals.*
  2  from animals
  3  order by animals.is_parent;

no rows selected

2) Write your ordering clause based on the position of fields in your select list:

SQL> select is_parent, animals.*
  2  from animals
  3  order by 1;

no rows selected

3) Use an alias for explicitly written columns:

SQL> select is_parent as parent, animals.*
  2  from animals
  3  order by is_parent;

no rows selected

Each of these solutions may be more or less readable; the second is the easiest one, and seems to match you need to order by the "most important" fields. However, I would not recommend any of these in writing an application, using them only for one-shot queries.

Aleksej
  • 22,443
  • 5
  • 33
  • 38
  • This seems to be the most complete answer. I ended up using option 2 as it is much easier in my case to use column numbering in the order clause instead of adding extra text. – Alex Apr 06 '16 at 23:39
1

Use a alias name. Like:

select is_parent as check_is_parent, animals.* from animals order by is_parent
SamGhatak
  • 1,487
  • 1
  • 16
  • 27
1

you get is_parent column for two times, so its create ambiguity. Use alias Like :

select is_parent as is_parent1, animals.* from animals order by is_parent1

Mahesh Bhosale
  • 112
  • 11
1

As there are 2 column names with the same name, so Oracle throws the error. Use an alias for the column name as 

select is_parent as PARENT, animals.* from animals order by is_parent

Work for me.

Vishal5364
  • 293
  • 1
  • 4
  • 21
0

I have managed to come up with a neat solution thanks to the very helpful comments and answers that say that I will confuse the compiler if I don't use an alias.

I find having to rename the columns cumbersome, for example, if I had selected more columns:

select is_parent, age, animals.* from animals order by is_parent, age

The quick and dirty solution is to tell Oracle to order using column numbers:

select is_parent, age, animals.* from animals order by 1, 2

This removes the ambiguity in column names.

Alex
  • 15,186
  • 15
  • 73
  • 127
-1

Why are you duplicating the column name as * will return all columns?

Either use this

select animals.* from animals order by is_parent

or explicitly type out all the columns

Madhivanan
  • 13,470
  • 1
  • 24
  • 29
  • because the table contains hundreds of columns, I want to see the important ones out first, followed by the rest to do diagnostics. The linked question provides an example of why this query structure is very convenient. – Alex Apr 06 '16 at 07:16
  • @Alex then you would have to alias them, else you would confuse the compiler and get that error – Mihai-Daniel Virna Apr 06 '16 at 07:17