case statement in where clause

I would like to know that may I use case statement in where clause?
If not, are there any other solution to filter my records depends on my
field.
For example
MyType integer, MyDate datetime
MyType MyDate
1 03/24/2005
1 03/20/2005
2 03/24/2005
I want to show MyType one date before today like record 2,but I want to
everything for MyType 2
Are there any solution for this?
Any information is appreciated,
Souris,Try this
Select * from Tablename where id in (select max(id) from tablename
group by date)
Madhivanan|||You can use a case statement in a where clause, but you don't need to for
this example. If you just want to look for myDate on the previous day and
myType = 2 than you would do this:
select * from myTable
where MyDate = convert(varchar,dateadd(d,-1,getdate()),101)
and MyType = 2
If you are looking for ANY myDate prior to today and myType = 2 than:
select * from myTable
where MyDate < convert(varchar,getdate(),101)
and MyType = 2
"souris" wrote:

> I would like to know that may I use case statement in where clause?
> If not, are there any other solution to filter my records depends on my
> field.
> For example
> MyType integer, MyDate datetime
> MyType MyDate
> 1 03/24/2005
> 1 03/20/2005
> 2 03/24/2005
> I want to show MyType one date before today like record 2,but I want to
> everything for MyType 2
> Are there any solution for this?
> Any information is appreciated,
> Souris,
>
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
<<
No. This is because there is no such statement in SQL. There is a
CASE expression which returns a scalar value.
I want to
everything for MyType 2 <<
SELECT my_type, my_date
FROM Foobar
WHERE my_type = 2
OR my_date < CURRENT_TIMESTAMP;
Rows are not records, columns are not fields. You are still using
terminology from a procedural language with a file system and not
thinking in RDBMS yet.|||Thanks millions,
Souris,
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1111763824.117387.95340@.o13g2000cwo.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications.
>
> <<
> No. This is because there is no such statement in SQL. There is a
> CASE expression which returns a scalar value.
>
> I want to
> everything for MyType 2 <<
> SELECT my_type, my_date
> FROM Foobar
> WHERE my_type = 2
> OR my_date < CURRENT_TIMESTAMP;
> Rows are not records, columns are not fields. You are still using
> terminology from a procedural language with a file system and not
> thinking in RDBMS yet.
>