Re: sql statement, urgent!

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 10/29/04 

Date: Thu, 28 Oct 2004 22:20:46 -0400

SELECT vinNumber, MAX(dateCreated), MIN(dealerID)
    FROM vinNumbers
    GROUP BY vinNumber

Or just leave dealerID out, since it seems meaningless in this query (and
could be misleading to users, who will think the dealerID and dateCreated
might be related, when in fact they're not). 

-- 
http://www.aspfaq.com/
(Reverse address to reply.)
"Natalit" <Natalit@discussions.microsoft.com> wrote in message
news:24758CB5-0FAD-4D1E-9F8C-47275640185C@microsoft.com...
> Either one would be fine for now as long as there is one record for each
vin.
>
> "Aaron [SQL Server MVP]" wrote:
>
> > Well, for vinNumber = 100 and datecreated = 1969-10-10, do you want
dealerid
> > = 9 or dealerid = 12?
> >
> > -- 
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "natalit" <natalit@discussions.microsoft.com> wrote in message
> > news:EC7A0AD3-17E5-4AB1-9C71-A80F86297B88@microsoft.com...
> > > I have this statement to retrieve unique records with a maximum date
value
> > > for each 'vinNumber'.
> > > It seem to work fine, but... If you noticed there are some records
with
> > same
> > > 'vinNumber' and same 'dateCreated'.
> > > How do I make it to show only one record for each vinNumber? even
though
> > > dates and 'vinNumber' are the same?
> >
> --------------------------------------------------------------------------
> > --
> > > Here's my select statement:
> > > select
> > > t1.vinNumber, t1.dateCreated, t1.DealerId
> > > from vinNumbers as t1
> > > where t1.dateCreated = (select MAX(t2.dateCreated)
> > > from vinNumbers as t2
> > > where t2.vinNumber = t1.vinNumber)
> >
> --------------------------------------------------------------------------
> > --
> > >
> > > table vinNumbers:
> > > vinNumber dateCreated DealerId
> > > 100       10/10/1965 5:00:00 PM 1
> > > 100       10/10/1966 5:00:00 PM 3
> > > 100       10/10/1967 5:00:00 PM 5
> > > 100       10/10/1968 5:00:00 PM 7
> > > 100       10/10/1969 5:00:00 PM 9
> > > 200       10/10/1965 5:00:00 PM 2
> > > 200       10/10/1966 5:00:00 PM 4
> > > 200       10/10/1967 5:00:00 PM 6
> > > 200       10/10/1968 5:00:00 PM 8
> > > 200       10/10/1969 5:00:00 PM 10
> > > 100       10/10/1969 5:00:00 PM 12
> > >
> > >
> >
> >
> >
Loading