[RESOLVED] How to get Max Record

[nUflAvOrS]

I contains a lot of reference number

The list of number is

PC/120608-1
PC/120608-2
PC/190408-1


How do I return the value is PC/120608-2 by using SQL Query and Visual basic sorting ?

[leinad31]

What do you really want to accomplish? Return just one record using WHERE condition column = 'PC/120608-2'? Or sort the records?

[nUflAvOrS]

the record returned from query is sort of records.

THANKS

[iPrank]

Take a look at ORDER BY clause.

[MarkT]

A SQL query like this should return what you want.

SELECT     TOP 1 *
FROM         YourTable
ORDER BY [reference number] DESC

[nUflAvOrS]

I had tried to use order by clause initially.

The return would be asc = PC/120608-1
DESC = PC/190408-1

PC/ Is a fixed, 120608 is date, -1 is counter of reference number of that date..

So the largest value i would like to return is PC/160608-2

thanks for help

[iPrank]

I don't think you can do that with SQL. How 'bout adding another field for ReferenceNumber ? Or you could loop through recordset and use Mid()/Right() to get the RefNumber.

[nUflAvOrS]

I had tried to use substring and convert method to sperate date function with counter by using subquery. I think the logic can be done but very complicated. I will try to consider to change my reference number to
PC/'date'-seq

the date is follow current date , the seq number will keep counting...

it is the good method ?

or i try ur method to add another new field as counter ..

Thanks Iprank ... L-)

[iPrank]

What is your DB ?

[nUflAvOrS]

sqlexpress 2005 :-)

[iPrank]

Here you can do this in Access like:

SELECT
  Right([RefNumbers],Len([RefNumbers])-InStrRev([RefNumbers],"-")) AS OriginalString, 
  IIf(IsNumeric(OriginalString),CLng(OriginalString),0) AS NumericRefNumber,
  *
FROM MyTable
ORDER BY 2 DESC;

Test data:

RefNumbers
-----------
PC/120608-1
PC/120608-2
PC/190408-1
PC/190408-111
PC/190408-12
PC/190408-22
PC/190408asdd

I don't have SQL 2005 running, But I'm sure it can be done in similar way by using alternate functions: http://www.sql-ref.com/.

[nUflAvOrS]

Thanks IPrank ,

The way you were provided is useful for me.
Thanks and fully appreciate ...