On 2016/07/12 1:25 AM, Keith Christian wrote:
A table has a column of dates and times that look like this: 2015-10-02 07:55:02 2015-10-02 07:55:02 2015-10-02 10:00:03 2015-10-02 10:05:02 2015-10-02 10:10:02 Schema: CREATE TABLE general ( id integer primary key autoincrement, server text, date_time_stamp text); Would like to get the latest two dates and times, kept in ascending order, e.g. the query should return these two values: 2015-10-02 10:05:02 2015-10-02 10:10:02 Is there a way to store the number of values in the date_time_stamp column and use the count minus N to get the largest N values in the column?
Yes, there is. Here are two pure SQL methods which will work in near any DB engine. Both are basically the same idea, but the first one expects non-duplicate entries (which your example data isn't, but possibly a copy-paste mistake, so I added it anyway), and the second will handle duplicate entries (assuming your data duplication is expected/correct and you don't want to see the duplicates). The number "2" in the HAVING clauses can be any N and will show the latest N values. here we show only dates, but you can of course select any of the other fields with it:
SELECT * FROM general; -- id | server | date_time_stamp -- ------------ | ------ | --------------------- -- 1 | A | 2015-10-02 07:55:02 -- 2 | A | 2015-10-02 07:55:02 -- 3 | A | 2015-10-02 10:00:03 -- 4 | A | 2015-10-02 10:05:02 -- 5 | A | 2015-10-02 10:10:02 SELECT a.date_time_stamp FROM general AS a LEFT JOIN general AS b ON b.date_time_stamp > a.date_time_stamp GROUP BY a.date_time_stamp HAVING count(b.date_time_stamp) < 2 ORDER BY a.date_time_stamp; -- date_time_stamp -- --------------------- -- 2015-10-02 10:05:02 -- 2015-10-02 10:10:02 WITH g(date_time_stamp) AS (SELECT DISTINCT date_time_stamp FROM general) SELECT a.date_time_stamp FROM g AS a LEFT JOIN g AS b ON b.date_time_stamp > a.date_time_stamp GROUP BY a.date_time_stamp HAVING count(b.date_time_stamp) < 2 ORDER BY a.date_time_stamp; -- date_time_stamp -- --------------------- -- 2015-10-02 10:05:02 -- 2015-10-02 10:10:02 Cheers, Ryan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
