Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list.

Unfortunately, the gap from the given timestamp to the first value timestamp for one foreign key may be many times this gap for another FK. I've had a go doing something like this, but it only gives me one row, rather than one row per fk.

SELECT *
FROM   data_item
WHERE  fk_fc_id IN (35246,35247) 
AND    di_timestamp > '2013-11-01 00:00:00'
ORDER  BY di_timestamp ASC
LIMIT  1; 

It's like I need to take the limit on a per FK basis, but can't work out how to do that.

share|improve this question

2 Answers 2

You can use window functions to achieve your goal. lag() and lead() are ones which can help you in a query like

SELECT lag(di_timestamp) OVER ordering, lead(di_timestamp) OVER ordering
  FROM data_item
 WHERE fk_fc_id IN (35246,35247)
WINDOW ordering AS (ORDER BY di_timestamp);

This will return the previous and the next timestamps, if there is any.

share|improve this answer
    
No that won't work. I've realised I wrote the query down wrongly, it should have been this, SELECT * FROM data_item WHERE fk_fc_id IN (35246,35247) and di_timestamp > '2013-11-01 00:00:00' order by di_timestamp asc limit 1; but it still won't work. –  James Feb 26 at 16:00
    
Probably I did not understand you correctly. Do you want to follow the FK to the referenced table? Or what is the problem? –  dezso Feb 26 at 16:52
    
The problem is I want the first record after a given date for a list of individual foreign keys. This should return the same number of rows as foreign keys. –  James Feb 26 at 17:02
2  
@James If the query is wrong, edit the original question to fix it. (I think you need to add some sample data and expected results to better explain what you want, too). –  Craig Ringer Feb 26 at 23:18

To get one row for each fk_fc_id listed that comes after the given timestamp and is closest to it .. best use DISTINCT ON, a Postgres-specific extension of the standard SQL DISTINCT:

SELECT DISTINCT ON (fk_fc_id)
       *
FROM   data_item
WHERE  fk_fc_id IN (35246,35247)
AND    di_timestamp > '2013-11-01 00:00:00' 
ORDER  BY fk_fc_id, di_timestamp;

If you need the result sorted by di_timestamp, wrap it in a subquery:

SELECT *
FROM  (
   SELECT DISTINCT ON (fk_fc_id)
          *
   FROM   data_item
   WHERE  fk_fc_id IN (35246,35247)
   AND    di_timestamp > '2013-11-01 00:00:00' 
   ORDER  BY fk_fc_id, di_timestamp
   ) sub
ORDER  BY di_timestamp;

Or you could use the window function row_number() (probably slower, standard SQL):

SELECT *
FROM  (
   SELECT *, row_number() OVER (PARTITION BY fk_fc_id
                                ORDER BY di_timestamp) AS rn
   FROM   data_item
   WHERE  fk_fc_id IN (35246,35247)
   AND    di_timestamp > '2013-11-01 00:00:00' 
   ORDER  BY fk_fc_id, di_timestamp;
  ) sub
WHERE  rn = 1
ORDER  BY di_timestamp;

There are more ways. More details in this related answer:
How do I efficiently get "the most recent corresponding row"?

Even more under this related question on SO:
Select first row in each GROUP BY group?

share|improve this answer

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.