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 recently asked the same question about MySQL, which has since been migrated to Postgres. Old question.

To sum it up:

I have a table with an id (type UUID), a timestamp, and some text (row3). How do I retain only one entry per hour for each distinct value of row3?

What I tried is this:

INSERT INTO log_table
SELECT * FROM table1
WHERE id IN (
    SELECT DISTINCT ON(id) id
    FROM table1
    GROUP BY row3, EXTRACT(HOUR FROM "time"), id
);
TRUNCATE table1;

But this, unfortunately, inserts every row from table1.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

Say you want to retain the values belonging the max(id) for every hour and for every distinct row3 value (but see the EDIT section below). The IDs of these you get with a query like

SELECT max(id)
FROM table1
GROUP BY row3, EXTRACT(hour FROM "time")
;

(This will omit the date from your data, which is what you may want—or not. In the latter case you may want to use date_trunc('hour', "time") instead.)

Then you can use the above query to populate a table just as you did in your question, or delete everything else from table1 like

DELETE FROM table1
WHERE id NOT IN (
    [the query above comes here]
)
;

EDIT

Now that we know the truth about UUID :), what about this one?

SELECT DISTINCT first_value(id) OVER (PARTITION BY row3, EXTRACT(hour FROM "time"))
FROM table1
;

This one applies a window function, and the grouping is achieved by the PARTITION BY clause and DISTINCT.

(Acknowledgment: thanks for the idea for AndriyM!)

share|improve this answer
    
There is just one problem: "function max(uuid) does not exist". I already tried something like this... –  icefex Dec 5 '12 at 11:18
    
@icefex Ugh. So your id is of what type? UUID? Please edit the questino with this information. –  dezso Dec 5 '12 at 11:39
    
Already had that, but made it bold so it does not get overlooked ^^ –  icefex Dec 5 '12 at 11:42
    
Now another problem is this: "table1.id" must appear in the GROUP BY clause or be used in an aggregate function –  icefex Dec 5 '12 at 12:07
    
Thank you! It finally works :) –  icefex Dec 5 '12 at 13:08
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.