postgresql compare column value from first row only

Question

This is how my history table looks like

 start_date | end_date | name
-----------------------+------------------------+----------
2016年11月05日 02:26:00+00 | 2016年11月10日 01:55:00+00 | premium
2016年10月01日 00:40:00+00 | 2016年11月30日 01:55:00+00 | free

Records are ordered by start_date in descending order. I want to get the first row and then check if its end_date is greater then now(), if yes then return true else false.

I have written the below query by looking at some tutorials and its working fine but i don't know if this is efficient or not and how to write an optimised query for my question above.

SELECT history.end_datetime FROM history, 
(SELECT id, end_datetime from history ORDER BY start_datetime 
DESC LIMIT 1) subhistory WHERE history.id = subhistory.id AND 
subhistory.end_datetime > now();

Answer 1

You could it much more simply:

SELECT end_datetime > now() AS result 
FROM history 
ORDER BY start_datetime DESC 
LIMIT 1 ;

The query is similar to your subquery, it returns only one row, the latest start_datetime. The end_datetime > now() in the select list is a boolean expression so it retuns either TRUE or FALSE, depending on the value of end_datetime in that row. We don't need to join this result set to the table because all the information we need is there, in the one row.

You could modify the select list, to include other columns, too, or all the columns if you need them:

SELECT end_datetime > now() AS result,
 start_datatime,
 end_datetime
...

For efficiency, you need an index on (start_datetime). Your query would be efficient, too, despite the unneeded self-join.

• MA1: Thanks its working. Can you please explain how it is working?
• ypercubeᵀᴹ: Added some explanation.