SQLite - Choosing the last Query result?

[SYRAU3]

Hello big-minds:

I have a database with single table (Main):

Customer | Visit_Date | Num

--------------------------------

Customer1| 1/1/2012|3

Customer2| 1/1/2012|1

Customer2| 2/1/2012|1

For example: I want the last visit date of Customer2 to be shown, so i'm trying:

_SQLite_Query(-1, "SELECT ROWID,* FROM Main WHERE Customer='Customer2' AND Num='1';", $hQuery)

When showing $hQuery[0] in a MessageBox it gives me the first result (1/1/2012), even when using _SQLite_FetchData, so how can i get the last result in the column (2/1/2012) ?

Thanx in advanced.

[DicatoroftheUSA]

Try "SELECT ROWID,* FROM Main WHERE Customer='Customer2' order by Visit_Date limit 1"

However it may not work well with how you formatted your dates, usually the date is better entered as yyyymmddhhss......

Edited June 26, 2012 by DicatoroftheUSA

[DicatoroftheUSA]

If you can't convert the database format, then I think you will have to use use _SQLite_FetchData and use autoit to analyse the date to give you the greatest value.

[SYRAU3]

Ah, Thank you DicatoroftheUSA, It really didn't work, still giving me the same result, so I used _SQLite_FetchData and it worked perfectly. thank you.

[DicatoroftheUSA]

You might want to consider revising your database anyways. If it grows to any size, it can sort much quicker in the yyyymmdd... format. Then convert it afterwords to a more common human readable form.

Edited June 26, 2012 by DicatoroftheUSA

[Spiff59]

You might look at SQL's MAX() function...