sql question

protos

Hey,

This is probably a really simple SQL question, but anyway ........... I've always just kind of hacked results out of DBs with a mixture of SQL and whatever language I was using (php / java) - This time I'd like to do the sql properly.

Right - so I have a table of comments. looks something like

comment_id
comment_type_id
comment_text
comment_date

Comment_type_id is a foriegn_key pointing to a table which holds a list of comment types.

I'd like to sort this list by comment type, then by date and then get the most recent comment of each type.

select * from comments order by comment_type_id, comment_date desc;

sorts the list as I want, but how do I just get the most recent comment of each type out ???

I'm using postgresql 8.0

Cheers
Colm
Ch

Kai

Hi,

This is one way to do it, this works in MS SQL Server and im pretty sure it works in Postgre:

Select * 

FROM comments CT1

Where CT1.comment_date = (Select Max(comment_date) FROM comments CT2 WHERE CT2.comment_type_id=CT1.comment_type_id)

order by comment_type_id, comment_date desc;

Basically your querying the same table 2 times and treating them as 2 different tables. You do this by referencing the table under a different name "CT1" and "CT2". So you then have 2 tables right, this is a nested query where the outer query passes in the comment_type_id its currently looking for and the inner query passes back the maximum comment date for that type. In our outer query we then just pull out the details where the date is the is the same as the date passed out by the inner query.

This is a really useful way of working with data, its a bit of a headwreacker when you first look at it but once you get the hang of them they can be really useful.

democrates

protos wrote:

Hey,

This is probably a really simple SQL question, but anyway ........... I've always just kind of hacked results out of DBs with a mixture of SQL and whatever language I was using (php / java) - This time I'd like to do the sql properly.

Right - so I have a table of comments. looks something like

comment_id
comment_type_id
comment_text
comment_date

Comment_type_id is a foriegn_key pointing to a table which holds a list of comment types.

I'd like to sort this list by comment type, then by date and then get the most recent comment of each type.

select * from comments order by comment_type_id, comment_date desc;

sorts the list as I want, but how do I just get the most recent comment of each type out ???

I'm using postgresql 8.0

Cheers
Colm
Ch

SELECT DISTINCT ON (comment_type_id, comment_date) comment_id, comment_type_id, comment_text, comment_date ORDER BY comment_type_id, comment_date DESC

is made for this, it's postgresql specific though.