Re: [SQL] SELECT DISTINCT ON ... ORDER BY ...

Thomas Metz wrote:
  >I have the following problem:
  >
  >Assuming the table TEST as follows:
  >
  >ID     NAME
  >-----------------
  >1      Alex
  >2      Oliver
  >1      Thomas
  >2      Fenella
...
  >SELECT DISTINCT ON id, name FROM test ORDER BY name;
  >produces:
  >
  >ID     NAME
  >-----------------
  >1      Alex
  >2      Fenella
  >1      Thomas
  >
  >I would have expected only two rows in both queries. I don't care which
  >names actually appear in the output as long as they are sorted, but
  >there should no longer be duplicate id's.

I looked at the documentation for SELECT and found that DISTINCT ON is
mentioned but not explained; there are also some other parts of that
documentation that need expanding or correcting, so I'm doing that at
the moment.

Is there any intended difference between DISTINCT ON and GROUP BY? (I
realise from this thread that there is an actual difference!)

Am I correct in saying that DISTINCT ON is not part of SQL92?


--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Many are the afflictions of the righteous; but the
      LORD delivereth him out of them all."
                                        Psalm 34:19