Help with Select statement 2

[cs2009]

Using SQL Server 2005.

I need help with a SQL Select statement. I have the following order numbers in a table and I want to select the lastest order number, which is the first 6 digits. The last 3 digits are the sequence numbers.

order_no
123456_00
123456_01
123456_02
987654_00
987654_01
385646_00

The result should return these order numbers:

order_no
123456_02
987654_01
385646_00

 

[RiverGuy]

SELECT d.*
FROM

YourTable d

INNER JOIN

(SELECT OrdNo, MAX(Seq) AS MaxSeq
FROM

(SELECT a.*, LEFT(order_no, 6) AS OrdNo, RIGHT(order_no, 2) AS Seq
FROM YourTable) b

GROUP BY OrdNo) c

ON c.OrdNo + '_' + c.MaxSeq = d.order_no

 

[gmmastros]

You should consider separating the order number from the sequence number in to separate columns in your table. It's very easy to concatenate them together for display purposes. If you cannot do this, then....

Select	Max(order_no)
From	YourTable
Group By Left(order_no, 6)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

Nice and simple solution George !