As you know `union`

removes duplicated rows. I thought it removes from the latter ones, but it doesn't.

I found out that **if there's no order by clause, Oracle sorts the merged dataset by the first column** and removes the duplicates.

```
select 4,5,6 from dual
union
select 1,2,3 from dual
union
select 4,5,6 from dual
```

It results :

```
1 2 3
4 5 6
```

Whereas I expect :

```
4 5 6
1 2 3
```

I failed many times with this, and make the problem simple like the example above.

How do I keep the union sequence without `order by`

?

In my real case, the first `select`

is full match(`=`

) result and the second is partial match(`like`

) result.

If I search for **roma** for example.

Full match result : **roma**, partial match result : **aroma**.

And of course full match result should come first. As I said, it seems `order by`

the first column of `union`

result.

But, as you see:

```
select 'roma' from dual -- result comes out anyway..
union select 'aroma' from dual;
```

Result:

```
aroma
roma
```

**aroma** is alphabetically ahead of **roma**, it comes first.

Maybe I should try `union all`

and remove the duplicates?

## Best Answer

As has already been mentioned more than once, you cannot expect rows to be in a certain order without specifying that order explicitly using the ORDER BY clause.

For the problem described in your question, you actually do not need a UNION at all. Use only the LIKE condition to cover both full and partial matches:

Then use a conditional expression to rank the rows based on whether the match is full or partial, using the DECODE function, for an example:

or a CASE expression, for another: