Create Table #t (Cat nvarchar(2),Value int)
Insert into #t
Select 'A', 1 union all
Select 'B', 1 union all
Select 'A', 0 union all
Select 'C', 3 Union all
Select 'B', 2

Select T.Cat,T.Value,
Case When T.Cat=tt.Cat
Then tt.min2
Else T.min2
End
from Select *,0 As min2
From #t as T
Left outer join
(Select Cat, Min(Value) As Value, 1 As min2
From #t as tt
Group by Cat )
On T.CAt=tt.cat and T.value=tt.value

About code can't run in sybase, any idea?

What error do you get?

Hi Martijnvs,

I am using ase 12.5

I found that I can't use left outer join in this version.

any idea to change it not to use left outer join

thank.

What are you trying to accomplish? What is the select-statement supposed to return?

Table #t
Cat ,Value
'A', 1
'B', 1
'A', 0
'C', 3
'B', 2

I want to select the min Value by Cat (the Cat data is not limit to A, B, C) and use 1 to indicate that it is min in the alias column Min_Value

expect output

Cat, Value, Min_Value
'A', 1, 0
'B', 1, 1
'A', 0, 1
'C', 3, 1
'B', 2, 0

Create Table #t (Cat nvarchar(2),Value int)
Insert into #t
Select 'A', 1 union all
Select 'B', 1 union all
Select 'A', 0 union all
Select 'C', 3 Union all
Select 'B', 2


select *,
case
when (select min(#t.Value) from #t as #t2 where #t2.Cat = #t.Cat) = #t.Value)
then 0
else 1
end as min_Value
from #t


Can I use another select in the case when statement?
but i got the following error
Incorrect syntax near ')'.

You are missing a (. The are 2 ('s and 3 )'s.

Martijnvs, thank you