How to group data in PostgreSQL?

Question

This PostgreSQL query basically displays a list of data that is older for 12hours.

SELECT * FROM listing_websites 
WHERE ( ( NOW() - last_visited ) > INTERVAL '12 hour'  OR last_visited IS NULL )
GROUP BY url,pkey ORDER by pkey ASC

Displayed data will look like these. pkey is the "primary key"

pkey |  url   | last_visited
12   | link1  | 2012-11-08 17:06:49.553515  
13   | link2  | 2012-11-07 05:36:55.270243
14   | link1  | 2012-11-09 08:54:33.51958
15   | link3  | 2012-11-03 16:29:17.20889
17   | link1  | 2012-11-08 05:54:33.51958

What I want to achieve is to group the url column for example:

pkey |  url   | last_visited
12   | link1  | 2012-11-08 17:06:49.553515  
13   | link2  | 2012-11-07 05:36:55.270243
15   | link3  | 2012-11-03 16:29:17.20889

Thanks in advance whoever can figure this out. I'm still learning postgresql anyway especially in the GROUP BY function. TIA

score 4 · Accepted Answer · 2012-11-09 19:48:25Z

4

Grouping by the primary key doesn't make sense. Because it is - by definition - unique, you will always get one group per primary key.

Apparently you want to see each url only once. In that case you have to decide which pkey and last_visited value you want to see with that.

The following selects the first last_visited and the lowest pkey value for each unique url value:

select min(pkey), url, min(last_visited)
from listing_websites
where ( ( NOW() - last_visited ) > INTERVAL '12 hour'  OR last_visited IS NULL )
group by url;

It gives the example output, but I'm not sure if that is what you want.

See the SQLFiddle demo here: http://sqlfiddle.com/#!12/2cbf7/1

If you need more columns, you need a different approach (and you should have mentioned that from the beginning):

select * 
from (
    select pkey, url, last_visited, url2, url3,
           row_number() over (partition by url order by pkey) as rn
    from listing_websites
    where ( ( NOW() - last_visited ) > INTERVAL '12 hour'  OR last_visited IS NULL )
) t
where rn = 1;

See my updated SQLFiddle example: http://sqlfiddle.com/#!12/6236f/1

Next time please include all requirements in your initial question and don't feed them to us bit by bit.

edited Nov 9, 2012 at 19:48

answered Nov 9, 2012 at 9:56

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Kenneth P. Over a year ago

this one works but not if you include other columns like select min(pkey), url, min(last_visited), url2, url3 for example

user330315 Over a year ago

@KennethPalaganas: so where does url2 suddenly come from. Don't you think you should have mentioned that in your original question? See my edit for a solution to that.

Bruno Simões · Accepted Answer · 2012-11-09 12:28:40Z

2

You can use DISTINCT ON:

SELECT DISTINCT ON (url) pkey,url,last_visited FROM listing_websites WHERE ( ( NOW() - last_visited ) > INTERVAL '12 hour' OR last_visited IS NULL ) ORDER BY url ASC;

edited Nov 9, 2012 at 12:28

answered Nov 9, 2012 at 9:48

Bruno Simões

7215 silver badges12 bronze badges

2 Comments

Craig Ringer Over a year ago

Note that DISTINCT ON isn't necessarily deterministic without a full ORDER BY clause.

Kenneth P. Over a year ago

I think I already explained what I'm trying to do. I even put the output I want lol. anyway thanks

Collectives™ on Stack Overflow

How to group data in PostgreSQL?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related