0

Possible Duplicate:
postgresql view with max min with id

I have the following table:

CREATE TABLE trans (
    id SERIAL PRIMARY KEY,
    trans_date date,
    trans_time time        
);

I want to have the following view

CREATE OR REPLACE VIEW daily_trans AS
SELECT trans_date,
    max(trans_time) as first, 
    min(trans_time) as last,
    calculate_status(min(trans_time), max(trans_time)) as status 
GROUP BY trans_date 

with columns that specify the ids of the max and min trans_time.

How do I do that?

7
  • 1
    Notice that this appears to be a programming question only, has been asked (and well answered) on Stack Overflow at stackoverflow.com/questions/8648355/… and should be closed here. Any agreement on these thoughts?
    – jcolebrand
    Commented Dec 27, 2011 at 22:04
  • Yes, it's been answered already. This can be closed as a duplicate. Commented Dec 27, 2011 at 22:11
  • I hope the dupe gets migrated here - we are not just an 'administration' site despite the name and anything involving windowing functions is on-topic here. Will check with SO if they want to keep this or are willing to migrate. Commented Dec 28, 2011 at 6:20
  • Hey guys thank you for migrating my question. I was divided where to post it.
    – Random Joe
    Commented Dec 28, 2011 at 9:23
  • @RandomJoe I think it is better here, and it looks like SO were happy to send it over. btw, it is not usually a good idea to post exact duplicates across sites - sometimes you might legitimately want a programmers perspective and a db expert perspective on the same problem, but then I'd expect the questions to be tailored a little for the site - does that sound reasonable? Commented Dec 28, 2011 at 11:16

0

Browse other questions tagged or ask your own question.