Given the following table
CREATE TABLE archived ( sid serial -- row id showurl text -- can be: btg, tmwwt, gcaf, emda title text reference text -- title id number );
sid | showurl | title | reference ----+---------+-------------+----------- 1 | btg | 01-Preface | 01 2 | btg | 01-Preface | 01 3 | emda | 26-Friction | 26 4 | tmwwt | 13-Helper | 13 5 | btg | 05-Finale | 05 6 | gcaf | 07-Help | 07
3rd and final selection:
I am trying to determine from table
showurl has the greatest number of rows.
Then, for that 'showurl', I need to identify which
reference has the greatest number of rows. Obviously, that 'reference' will be duplicated, but the 'sid' associated with each duplicated 'reference' will be unique.
Once that is determined, I wish to select/display its
My initial attempt (using php, postgresql 9.2: queried each
showurl, then using max to identify the
showurl with the greatest number of hits.):
$query = "SELECT * FROM archived WHERE showurl = 'btg' order by sid ASC"; $query = "SELECT * FROM archived WHERE showurl = 'tmwwt' order by sid ASC"; $query = "SELECT * FROM archived WHERE showurl = 'gcaf' order by sid ASC"; $query = "SELECT * FROM archived WHERE showurl = 'emda' order by sid ASC"; $max = max($numrows1,$numrows2,$numrows3,$numrows4);
Using the sample data above,
$max would be 3, but
sid are unavailable for further processing.
I have tried using
pg_numrows() but I cannot find a way to refer back either to 'title' or 'reference' for the max rows.
I gather some form of
UNION would be in order, but have no idea how to implement this.