CODEWITHSUNDEEP
sqlms-accessvbamax
Vinny
Vinny

Reputation: 134

Getting largest reference in sql query with cast

I have a little sql problem in my vba project that I can't solve. I have a table that besides his ID also has another unique reference. This column is named "Ref_Q" and is Text: "13/45". With 13 from the year were in and another unique number. Now I wan't to get the largest Ref_Q from my table. First I got this:

SELECT * FROM Reports WHERE Ref_Q = (SELECT MAX(Ref_Q) FROM Reports);

Everything went fine untill I got a Ref_Q larger then 99. So I had "13/102" and he always takes Ref_Q "13/99". So I tried something like this:

SELECT CAST(SUBSTRING(MAX(Ref_Q),4) AS Int) FROM Reports

But now I keep getting error:

Syntax error (missing operator) in query expression 'CAST(SUBSTRING(MAX(Ref_Q),4) AS Int)'

Upvotes: 0

Views: 172

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

Try this query (using SQL Server syntax):

SELECT *
FROM Reports
WHERE Ref_Q = (SELECT top 1 Ref_Q
               FROM Reports r
               ORDER BY left(Ref_Q, 2) desc,
                        cast(substring(Ref_Q, 4, len(Ref_Q) - 3) as int) desc
              );

This uses order by instead of max() to fetch the largest value according to your rules. By the way, you could fix this by zero-padding the numbers, using '13\0102' and '13\0099'.

By the way, if you only want want row, you can just use the subquery:

               SELECT top 1 *
               FROM Reports r
               ORDER BY left(Ref_Q, 2) desc,
                        cast(substring(Ref_Q, 4, len(Ref_Q) - 3) as int) desc

EDIT:

Here is a SQL Fiddle showing them in operation.

Now I see it is labelled access. Oops. I think this might work:

SELECT *
FROM Reports
WHERE Ref_Q = (SELECT top 1 Ref_Q
               FROM Reports r
               ORDER BY left(Ref_Q, 2) desc,
                        cint(mid(Ref_Q, 4, len(Ref_Q) - 3)) desc
              );

(I do note, however, that your original query uses cast() which is not in Access as far as I know.)

Upvotes: 1

Related Questions