Thread

1. Quick SQL question . . .

   Peter E. Chen <pchen3@jhmi.edu> — 2002-05-09T19:39:18Z

   Can anyone tell me what is the easiest way for me to tell if a column is
   unique or not?  I tried using DISTINCT ON and COUNT together in a SELECT
   statement, but I can't seem to get the query to work:
   
   SELECT DISTINCT ON (identno) count(identno) FROM some_table;
   
   I was trying to figure out if the # of unique entries for a particular
   column is equal to the # of total entries for that column.
   
   Any suggestions?
   
   Peter
   
   
   
   
   

2. Re: Quick SQL question . . .

   Fran Fabrizio <ffabrizio@mmrd.com> — 2002-05-09T19:54:33Z

   Perhaps a little on the hacky side but....
   
   select count(1) from some_table group by identno order by 1 desc limit 1;
   
   If the result is anything other than 1, it's not unique.
   
   Sorry for the hacky nature, this is the "thought about it for 10 
   seconds" version.  :-)
   
   -Fran
   
   Peter E. Chen wrote:
   
   >Can anyone tell me what is the easiest way for me to tell if a column is
   >unique or not?  I tried using DISTINCT ON and COUNT together in a SELECT
   >statement, but I can't seem to get the query to work:
   >
   >SELECT DISTINCT ON (identno) count(identno) FROM some_table;
   >
   >I was trying to figure out if the # of unique entries for a particular
   >column is equal to the # of total entries for that column.
   >
   >Any suggestions?
   >
   >Peter
   >
   >
   >
   >
   >---------------------------(end of broadcast)---------------------------
   >TIP 4: Don't 'kill -9' the postmaster
   >  
   >
   
   
   
   
   

3. Re: Quick SQL question . . .

   Marie G. Tuite <marie.tuite@edisonaffiliates.com> — 2002-05-09T20:04:33Z

   Try
   
   select identno, count(identno) from some_table group by identno having
   count(identno) >=1;
   
   
   -----Original Message-----
   From: pgsql-general-owner@postgresql.org
   [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Peter E. Chen
   Sent: Thursday, May 09, 2002 2:39 PM
   To: pgsql-general@postgresql.org
   Subject: [GENERAL] Quick SQL question . . .
   
   
   Can anyone tell me what is the easiest way for me to tell if a column is
   unique or not?  I tried using DISTINCT ON and COUNT together in a SELECT
   statement, but I can't seem to get the query to work:
   
   SELECT DISTINCT ON (identno) count(identno) FROM some_table;
   
   I was trying to figure out if the # of unique entries for a particular
   column is equal to the # of total entries for that column.
   
   Any suggestions?
   
   Peter
   
   
   
   
   ---------------------------(end of broadcast)---------------------------
   TIP 4: Don't 'kill -9' the postmaster
   
   
   

4. Re: Quick SQL question . . .

   Nigel J. Andrews <nandrews@investsystems.co.uk> — 2002-05-09T23:50:05Z

   Is it just me or do the suggestion made look wrong to anyone else?
   
   Anyway, this is the closest to my mind and I think what I think is the mistake
   is just a typo. So I would use.
   
   SELECT identno, count(identno)
      FROM some_table
      GROUP BY identno
      HAVING count(identno) > 1
   
   or even
   
   SELECT count(1) FROM (
      SELECT count(identno)
         FROM some_table
         GROUP BY identno
         HAVING count(identno) > 1) a
   
   either one of which will return one of more rows if Peter's uniqueness test
   fails.
   
   Right, now someone can correct me :)
   
   
   On Thu, 9 May 2002, Marie G. Tuite wrote:
   > Try
   > 
   > select identno, count(identno) from some_table group by identno having
   > count(identno) >=1;
   > 
   > 
   > -----Original Message-----
   > From: pgsql-general-owner@postgresql.org
   > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Peter E. Chen
   > Sent: Thursday, May 09, 2002 2:39 PM
   > To: pgsql-general@postgresql.org
   > Subject: [GENERAL] Quick SQL question . . .
   > 
   > 
   > Can anyone tell me what is the easiest way for me to tell if a column is
   > unique or not?  I tried using DISTINCT ON and COUNT together in a SELECT
   > statement, but I can't seem to get the query to work:
   > 
   > SELECT DISTINCT ON (identno) count(identno) FROM some_table;
   > 
   > I was trying to figure out if the # of unique entries for a particular
   > column is equal to the # of total entries for that column.
   > 
   > Any suggestions?
   > 
   > Peter
   > 
   
   -- 
   Nigel J. Andrews
   Director
   
   ---
   Logictree Systems Limited
   Computer Consultants