I have a table in Postgres called calendar and I am trying to query the first date value stored where year=2013.
I have written the following query which works, however I am wondering if I can query the first date instead of just getting all data and limiting just to one?
SELECT date FROM calendar WHERE year=2013 ORDER BY date ASC LIMIT 1
-- the schema
DROP SCHEMA lutser CASCADE;
CREATE SCHEMA lutser;
SET search_path='lutser';
-- the table
CREATE TABLE years
( zdate DATE NOT NULL PRIMARY KEY
-- omitting a separate "year" field
-- , since it can be derived from a date.
-- ... ...
);
-- the data
INSERT INTO years(zdate)
SELECT gs
FROM generate_series( '2012-1-1' , '2014-12-31', '7 days'::interval) gs
;
-- the query
SELECT zdate
FROM years yy
WHERE date_trunc( 'year' , yy.zdate) = '2014-01-01'::date
AND NOT EXISTS (
SELECT *
FROM years nx
WHERE date_trunc( 'year' , nx.zdate) = '2014-01-01'::date
AND nx.zdate < yy.zdate
)
;
-- the same query using a CTE
WITH this AS (
SELECT zdate
FROM years yy
WHERE date_trunc( 'year' , yy.zdate) = '2014-01-01'::date
)
SELECT th.zdate
FROM this th
WHERE NOT EXISTS (
SELECT *
FROM this nx
WHERE nx.zdate < th.zdate
)
;
\q
-- the result
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 157
zdate
------------
2014-01-05
(1 row)
Have you tried simply something like this?
Case if you have a year field:
select MIN(date)
from calendar
where year = 2013
Case if you haven't a year field:
select MIN(date)
from calendar
where date >= '01-01-2013'
datecolumn (which btw. is a horrible name for a column), then Postgres will not "get all the data". You could do something likewhere date = (select max(date) from calendar)but that won't be more efficient.where year=2013so i presume you are not using a timestamp or date column?CREATE TABLEstatements) and preferably some sample data. Your PostgreSQL version should also be in every single question.