select distinct ... order by ... 1

[Thorsten3]

I need to select all distinct "structures" of a table and order them depending on the value of column "str_order". The following two commands don't work.

select distinct structure from T_STRUCT_FEAT_M where stage='m' order by
str_order

select distinct structure from (select * from T_STRUCT_FEAT_M where stage='m' order by str_order)

What would be the proper command?

 

[MalcolmW]

How is what you want different than simply
select distinct *
from T_STRUCT_FEAT_M
where stage='m'
order by str_order Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[Thorsten3]

I only want the distinct values of the column named structure. These should be ordered depending on the value of another column named str_order. I'm using db2 by the way. My two commands mentioned above result in errormessages.

 

[Tim1]

Hiya,

The problem is that you cannot do an order by if you are not selecting that column in SQL.

All you can do is put the information into a temporary table including your sort column, the select the values from that:

SELECT DISTINCT structure,
str_order
INTO #tmp_order
FROM from T_STRUCT_FEAT_M
where stage='m'
order by str_order

SELECT DISTINCT structure
FROM #tmp_order

The only other thing you can do, of course, is to select str_order as part of your main select query.

HTH

Tim

 

[listener22]

By the way, in Oracle you can order by
some column(s) you don't select. Jim

oracle, vb