Need help on SQL using date

[Spike]

Hi to all the sifu,
i have this table that have "Type" and "Date".
Data are store as
Type Date
CP 20 Mar 2003
CF 22 Mar 2003
EX 20 Mar 2003
CP 19 Mar 2003
EX 18 Mar 2003

im trying to create a query for data which is Date <= 22 Mar 2003
and is distinct by Type and Date
the result that i want is

Type Date
CP 20 Mar 2003
CF 22 Mar 2003
EX 20 Mar 2003

The nearest i can get is to do a query distinct by Type
then loop one by one on this query
"Select top 1 Type, Date from tbl
where Type = 'loop of the distinct type'
order by date desc"

any sifu can help ?
thanks

[Bob Barrows]

This was almost a very well-posed question. The only information missing is:
What is the type and version of the database you are using?
What is the datatype of the "Date" column? The format is irrelevant unless
it's a character field.

Bob Barrows

Spike wrote:

> Hi to all the sifu,
> i have this table that have "Type" and "Date".
> Data are store as
> Type Date
> CP 20 Mar 2003
> CF 22 Mar 2003
> EX 20 Mar 2003
> CP 19 Mar 2003
> EX 18 Mar 2003
>
> im trying to create a query for data which is Date <= 22 Mar 2003
> and is distinct by Type and Date
> the result that i want is
>
> Type Date
> CP 20 Mar 2003
> CF 22 Mar 2003
> EX 20 Mar 2003
>
> The nearest i can get is to do a query distinct by Type
> then loop one by one on this query
> "Select top 1 Type, Date from tbl
> where Type = 'loop of the distinct type'
> order by date desc"
>
> any sifu can help ?
> thanks

[Spike]

Hi Bob,
Im using MSSQL 7.
The "Date" is a datetime format which is 3/20/2003,
i put in 20 march 2003 is for easy understand.
will this info help?
thanks

"Bob Barrows" <reb_01501@yahoo.com> wrote in message news:<eorEt$DfDHA.616@TK2MSFTNGP11.phx.gbl>...

> This was almost a very well-posed question. The only information missing is:
> What is the type and version of the database you are using?
> What is the datatype of the "Date" column? The format is irrelevant unless
> it's a character field.
>
> Bob Barrows
>
> Spike wrote:

> > Hi to all the sifu,
> > i have this table that have "Type" and "Date".
> > Data are store as
> > Type Date
> > CP 20 Mar 2003
> > CF 22 Mar 2003
> > EX 20 Mar 2003
> > CP 19 Mar 2003
> > EX 18 Mar 2003
> >
> > im trying to create a query for data which is Date <= 22 Mar 2003
> > and is distinct by Type and Date
> > the result that i want is
> >
> > Type Date
> > CP 20 Mar 2003
> > CF 22 Mar 2003
> > EX 20 Mar 2003
> >
> > The nearest i can get is to do a query distinct by Type
> > then loop one by one on this query
> > "Select top 1 Type, Date from tbl
> > where Type = 'loop of the distinct type'
> > order by date desc"
> >
> > any sifu can help ?
> > thanks

[Bob Barrows]

Yes, this information helps. Unfortunately, there are a few more questions I
now realize I should have asked now that I've reread your question.

You have two "CP" records in your sample data. In your desired result, you
indicate that you want the 20 Mar 2003 date returned. How did you decide
that this was the date you wanted to be returned. Is it because this is the
later of the two dates? If so, that's easy to do:
select Type, max([Date]) from table group by Type

Or is it (as I suspect from your loop suggestion) because it's the "first"
date you showed in your sample data? If so, you are not going to be able to
achieve this unless you have another column in your table that provides the
order of entry. Don't forget, in a relational database, a table is defined
as a "set of unordered rows of data". This means: just because the row with
20 Mar 2003 may have been entered into the table before the row with 19 Mar
2003, this does not mean that the data is stored in that order on the disk,
or that the data will be returned in that order by a select statement
without an ORDER BY clause. So even if you did attempt a cursor-based
(looping) solution, there is no guarantee that the process would give you
your desired results, unless you can order the rows being processed by a
third column that indicates the order of entry.

So, lets' assume you have an IDENTITY column, or better, a datetime column
that contains the date and time of the row's entry. Here is how you would
utilize that column:

Select t.Type,[Date] From table t inner join
(Select Type, min(EntryOrderColumn) As FirstEntry
From table
Group By Type) As q
on t.Type = q.Type AND t.EntryOrderColumn = q.FirstEntry

HTH,
Bob Barrows



Spike wrote:

> Hi Bob,
> Im using MSSQL 7.
> The "Date" is a datetime format which is 3/20/2003,
> i put in 20 march 2003 is for easy understand.
> will this info help?
> thanks
>
> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:<eorEt$DfDHA.616@TK2MSFTNGP11.phx.gbl>...

>> This was almost a very well-posed question. The only information
>> missing is:
>> What is the type and version of the database you are using?
>> What is the datatype of the "Date" column? The format is irrelevant
>> unless
>> it's a character field.
>>
>> Bob Barrows
>>
>> Spike wrote:

>>> Hi to all the sifu,
>>> i have this table that have "Type" and "Date".
>>> Data are store as
>>> Type Date
>>> CP 20 Mar 2003
>>> CF 22 Mar 2003
>>> EX 20 Mar 2003
>>> CP 19 Mar 2003
>>> EX 18 Mar 2003
>>>
>>> im trying to create a query for data which is Date <= 22 Mar 2003
>>> and is distinct by Type and Date
>>> the result that i want is
>>>
>>> Type Date
>>> CP 20 Mar 2003
>>> CF 22 Mar 2003
>>> EX 20 Mar 2003
>>>
>>> The nearest i can get is to do a query distinct by Type
>>> then loop one by one on this query
>>> "Select top 1 Type, Date from tbl
>>> where Type = 'loop of the distinct type'
>>> order by date desc"
>>>
>>> any sifu can help ?
>>> thanks