PDA

View Full Version : Example: SQLite, dates and years



bule
10-10-2007, 06:53 AM
Here is a little neat thing I've just realised:

For example, you have a table 'mytable' with columns 'name' and 'date':
- 'name' is a string
- 'date' is an integer, in a form YYYYMMDD

So, you have a table filled with a lot of records for many years.

What if you want to have a ComboBox with Year selection, that would
only show the years for the dates that are already in the database?
Selecting all rows using 'DISTINCT date' and then parsing using Lua would
work, but it would not be that optimised since many rows would be returned.
Instead, you could try this:

SELECT DISTINCT substr(date,1,4) FROM mytable;

This will only return one row per year! Now, is this super-neat or what?

BTW, the table index for year column in this case would be:
mytable.Data[count]["substr(date,1,4)"]
(assuming you would use 'mytable' as a return value, and 'count' as a for iterator)

RizlaUK
10-14-2007, 07:42 AM
yeah thats pretty neat,

i have always used "combo.find" and compared the results to the current row in the loop and if no match then add the item, it makes sense to do it all within the sqlite santax,

Thanks :yes

bule
10-14-2007, 09:51 AM
You could do the query like this as well:

SELECT DISTINCT substr(date,1,4) AS year FROM mytable;

Then, you could reference to it like this:
mytable.Data[count].year

Intrigued
10-14-2007, 06:08 PM
More useful tips, thanks guys.