[RESOLVED] How to get Max Record

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 ?

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

the record returned from query is sort of records.

THANKS

Take a look at ORDER BY clause.

A SQL query like this should return what you want.

Code:

---

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

---

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

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.

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-)

What is your DB ?

sqlexpress 2005 :-)

Here you can do this in Access like:

Code:

---

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:

Code:

---

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/.

Thanks IPrank ,

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