Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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
share|improve this question
3  
If you have an index on the date column (which btw. is a horrible name for a column), then Postgres will not "get all the data". You could do something like where date = (select max(date) from calendar) but that won't be more efficient. –  a_horse_with_no_name 2 days ago
4  
how is the table structured? I see that you are using where year=2013 so i presume you are not using a timestamp or date column? –  Fabrizio Mazzoni 2 days ago
 
You have "date" and "year" columns - wha?! Bizarre schema. Tip: When posting questions here, show your schema (CREATE TABLE statements) and preferably some sample data. Your PostgreSQL version should also be in every single question. –  Craig Ringer 21 hours ago
add comment

2 Answers

        -- 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)
share|improve this answer
add comment

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'
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.