0

I have the following code:

query = "SELECT ResultDate, Result FROM ASPECTRESULTS WHERE EUPN = '" & StudentEUPN & "' AND SubjectCode = '" & SearchClass & "' AND ACCYEAR = '" & AccademicYear & "' ORDER BY RESULTDATE"

I want to get the latest result based on the date of the result. I tried using SELECT MAX(ResultDate) but with no luck.

Any help appreciated.

Regards Graham

Graham Jones
  • 165
  • 4
  • 19

1 Answers1

0

You could write (assuming that ResultDate is a Date type column in your table)

query = "SELECT TOP 1 ResultDate FROM ASPECTRESULTS WHERE ..... " & _
        "ORDER BY RESULTDATE DESC"

This will return the highest date in the ResultDate column

Said that, you should really study how a parameterized query works and why you should ALWAYS use parameters instead of string concatenations

EDIT
From your comment above (ResultDate is a Char(10)), then getting this field correctly ordered is real pain, in particular if you store the date as dd/MM/yyyy. With this format in a char ordering the value '11/01/2013' follows the value '01/01/2014'. This is one of the many reasons to ALWAYS store datetime values in datetime fields. The database will know how to correctly handle a date without considering how is represented by a string.

You have two options to overcome from this hurdle.

  • Change the datatype of the field ResultDate to a DateTime field (recommended)
  • Store your values using a 'sortable' char format (yyyy/MM/dd with always two digits for months and days (not recommended)
Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Could I also get the Result by using: "SELECT TOP 1 ResultDate, Result..." or will that get the top 1 result? – Graham Jones Dec 12 '14 at 23:06
  • The key point is __ORDER BY ResultDate DESC__. This will order the records with the highest date on top and taking just the first record. You could add all the fields you want before or after the ResultDate. Just have the resultset ordered by ResultDate descending – Steve Dec 12 '14 at 23:11
  • I'll change the structure and see how I get on, thanks. – Graham Jones Dec 12 '14 at 23:23