Latest item with highest identity 2

[mflancour]

I need 1 record for each part. This record must have max(date) and max(identity#) and type must = "a" or "u".
Example:
ident# Part quantity type date
1 1234 460 a 3/12/2007 13:35
2 1234 10 u 3/13/2007 13:35
3 1234 50 1 3/14/2007 13:35
4 5421 46 a 3/14/2007 13:35
5 1234 460 9 3/14/2007 13:35
6 1234 460 1 3/14/2007 13:35
7 5421 0 u 3/15/2007 13:35
8 1234 50 u 3/14/2007 13:35
9 1234 12 1 3/14/2007 13:35


Results should be

7 5421 0 u 3/15/2007 13:35
8 1234 50 u 3/14/2007 13:35

 

[bborissov]

Can you have smaller date when you have bigger Identity field?
i.e.

1    1234    460        a      3/12/2007 13:35
2    1234    10        u       2/10/2007 13:35

?
I ask this becuase if you can't have that situation the query wil be more easy


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

How about this?

select a.ident#, a.Part, a.Quantity, a.Type, a.[Date]
from myTable a
inner join
(
select max(ident#), Part
from myTable
where a.Type in ('a','u')
group by Part
) b
on a.Part = b.Part
and a.ident# = b.ident#

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[mflancour]

You are correct, the date will never be smaller, it will however quite often be EXACTLY the same as several others with the same part number

 

[AlexCuse]

Whew, my assumption that you would only need the max(ident#) was right ;-)

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

Alex, I shouldn't ask the question, I should post the answer immediately
(just joking)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Hey, it worked for the sample data ;-)

Ignorance of certain subjects is a great part of wisdom

 

[mflancour]

It's all good, you both get a star

 

[bborissov]

Thank you, but I don't think I deserved the star here

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Boris, don't think less of yourself just because you don't have my amazing mind reading powers

I am sure that you had the exact same thing in mind when you asked the question.

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

Yep,
but my crystal ball malfunctioning today

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Maybe because it use Denis' favorite software

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

maybe

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP