PostgreSQL query issues

Question

I have a DB with multiple tables which also have multiple rows/columns with a layout similar to that shown below.

the site is in a table named sites and ID and Type are in a table labelled Site_EQ

Site  ID Type  
A0004  2 abc
A0004  3 abcd
A0004  4 abcde
A0005  2 abc
A0005  3 abcd
A0005  4 abcde 
A0005  5 abc  
A0005  6 abcd 
A0005  7 abcde

Essentially what I am trying to do is filter the results by site finding the highest ID value per site and removing the others, so if for example A0010 had ID's 1-20 I would like the result to show.

A0010 20 Bla

and ignore the

A0010 1 Bla
A0010 2 Bla

and so on, but am not sure how to go about doing so as there is no set number of ID it could be 1-3 or 1-30, essentially giving me 30 results for a single site with only 1 column different (which I would like to filter to the highest value only).

Answer 1

This should do it:

SELECT T1.Site, T1.ID, T1.Type
FROM SomeTable T1, (SELECT Site, MAX(ID) AS ID
                    FROM SomeTable
                    GROUP BY Site) T2
WHERE T2.Site = T1.Site
AND T2.ID = T1.ID

The trick is have a sub query that gives you the Site + maximum ID, you can't retrieve the Type at the same time though due to the grouping so you need to join it again with the actual table.

Here you can find the explanation on Table Alias (T1 and T2):
http://www.postgresql.org/docs/9.1/static/queries-table-expressions.html#QUERIES-TABLE-ALIASES

Answer 2

Try:

select Site, ID, Type from
(select s.*, row_number() over (partition by Site order by ID desc) rn
 from Site_EQ) q
where rn=1