Easy one

fcharron · August 26th, 2003, 06:05 PM

Hello all !

This ought to be an easy one.

I have made a query that returns ordered (by specific criteria)results. Now I am only interested in the last row returned by the query.

** Details **
I have a text field that contains strings that have letters and numbers. I then extract the numbers with mid() and sort the results of the query with these numbers.

TextField samples:
"Showing002-2003" refers to the 2nd item of 2003
"Showing021-2002" refers to the 21st item of 2002

SELECT
  TextField,
  mid(TextField, 8,3) as number,
  mid(TextField, 12, 4) as year
FROM Mytable
ORDER BY mid(TextField, 12, 4), mid(TextField, 8,3)

This query returns me all the items in order. Now I just want to have the last entry; Showing002-2003 if these were the only two entries in the table.

I have tried using the last() function, but can't get it to work.

Anybody has an idea?

Thanks,
FranyK

Jonax · August 26th, 2003, 06:15 PM

I guess

Code:

SELECT TOP 1
  TextField,
  mid(TextField, 8,3) as number,
  mid(TextField, 12, 4) as year
FROM Mytable
ORDER BY mid(TextField, 12, 4) DESC, mid(TextField, 8,3) DESC

oughta do it...

Jonax · August 26th, 2003, 06:17 PM

... actually I think you want:

ORDER BY mid(TextField, 8,3) DESC, mid(TextField, 12, 4) DESC

fcharron · August 26th, 2003, 06:56 PM

yup, thanks ...

But your first answer was correct.

I want to sort by year first, then sort by number, instead of sorting by number, and then by year