Choosing the Latest project number by the tail

[John1Chr]

I have table A in which I want to select the latest project number which is based of the tail.

0395279-00
0395279-01
0395279-02

I want to select the 0395279-02 and ignore the other project. I also have projects that have letters involved like L00P001-03. The tail is usually numeric and looks like -01,-02,-03. It sounds easy but for some reason I’m stumped.

 

[lespaul]

a starting point (replace the field and table name):

SELECT FieldName, RIGHT(FieldName, 2) FROM TableName

will return
[tt]
0395279-00 00
0395279-01 01
0395279-02 02[/tt]

you can use this in conjunction with the MAX function to just get the one you want:

SELECT FieldName, Max(RIGHT(FieldName, 2)) FROM TableName GROUP BY FieldName

HTH



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[SkipVought]

Hi,

Where [Project number] Like '*00'

Skip,

_[red][/red]

 

[r937]

leslie, your query will return

fieldname   max(right(fieldname,2)) 
0395279-00     00
0395279-01     01
0395279-02     02

can you see why? each "tail" is its own max for the entire fieldname



r937.com | rudy.ca

 

[SkipVought]

I think I'd change mine to ...

Where Right([Project number],2) = '00'

Skip,

_[red][/red]

 

[lespaul]

yea, you're right....too early in the morning. But I think the OP is looking for the MAX tail of the field....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

SELECT Max([Project Number]) AS LatestProject
FROM [Table A]
GROUP BY Left([Project Number],7)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[John1Chr]

Thanks PHV! That last one worked perfectly. Everyone had great input.