if else

Hi all,

what is the best possible query to achieve something like this, if these are the columns.

S.no Name Join Date post Sal

1 Abraham 12-03-2007 E.O 2500 $

2 Abrham Null E.O 2500$

3 John Null Trainee 500$

the o/p

S.no Name Join Date post Sal

1 Abraham 12-03-2007 E.O 2500 $

3 John Null Trainee 500$

in short if there is a date value get the latest results, if no date is there get all possible distinct values ?

What you are seeking is not clear.

Easy to understand the part about returning the rows where there is a [Join Date] value.

Not sure what you desire for the non-qualifying rows. (Why is s.no 3 in your results instead of s.no 2?)

|||

This particular thing is what the fourth normal form is all about. If E.O in the different rows actually indicates the same thing, then you really ought to have multiple tables to indicate the Join Date and then the the post and the Salary.

Your schema makes no sense at all. What does a NULL join date mean? Can you explain what the meaning of the first two rows actually are?

I think you are asking if you can have a distinct operation that ignores NULL, and depending on what else your data can do, it might be possible, but can there be three rows? What if two of the rows have distinctly different values, but one has null? If that is the case, you will probably need a cursor to work with your schema....

|||

Sorry if the post wa not clear. Agreed that the schema is in total bad shape, but now we have a requirement wherein we have to select the record which is the latest, base don date and if there is no date then get the other details only.

say like if

A b (date) C

1 today a

1 yestrerday a

1 day bfore sday a

2 Null b

3 NUll b

I have to get the o/p like

1 today a

2 Null b

3 NUll b

Sorry if I had confused in the earlier post. Thanks for the replies...

|||

Would this work for you?

select a, max(b) as b, c
from #x
where b is not null
group by a, c
union
select a, b, c
from #x
where b is null

/Kenneth