Temporary column is not sorting

[krotha]

I have two select statements
select top 5 t.field1, flag = case when f2='a' then 1 else 0 end
order by flag asc

which gives me
1 0
2 0
3 0
4 1
5 1

select top 2 t.field1, flag = case when f2='a' then 1 else 0 end
order by flag asc

which gives me different results
8 1
10 0

Basically it is not sorting on the flag column.

 

[amrita418]

Are you sure this query does not give you any errors? I do not see a from tablename clause. I am guessing that is a typo.

Try something like this:

select     top 2 
           t.field1, 
           case when t.f2 = 'a' then 1 else 0 end 'flag'
from       Table1 t
order by   flag asc

Regards,
AA

 

[krotha]

There was a from tablename clause, when I rewrite I forgot to add. I didn't get error.

select top 5 t.field1, flag = case when f2='a' then 1 else 0 end from table t
order by flag asc

 

[amrita418]

Did you try to code that is posted? What was the result?

Also, your query gives me the right results. Can you post the query with results again?

Regards,
AA

 

[krotha]

Thanks amrita418 for your responses but I still get the same results

select top 5 t.field1, case when t.f2='a' then 1 else 0 end 'flag' from table t
order by flag asc

which gives me
1 0
2 0
3 0
4 1
5 1

select top 2 t.field1, case when t.f2='a' then 1 else 0 end 'flag' from table t
order by flag asc

which gives me different results
8 1
10 0

 

[amrita418]

I am not sure why the discrepancy but 2 questions for you.

1) Is this your complete query? The reason I ask this is because if the the first query gave you top 5 then the next query should give you first two of the 5. I am not sure why the id's are different too.

2) Can you replace the fieldname in the order by clause with the actual case stmt and see how it effects the results (I am positive it should not).

Regards,
AA