Given the following table archived
:
CREATE TABLE archived (
sid serial -- row id
showurl text -- can be: btg, tmwwt, gcaf, emda
title text
reference text -- title id number
);
Sample Input:
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
Desired Output:
1st selection: showurl: 'btg'
2nd selection: reference: '01'
3rd and final selection: title: '01-Preface'
I am trying to determine from table archived
which 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 title
.
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 showurl
, title
, reference
, and 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.
showurl
do not match the ones forreference
. How is the result defined exactly? Are we dealing with completely duplicate rows, except forsid
? – Erwin Brandstetter Dec 26 '13 at 16:21