Re: newbie question, unique index || double entries

Marc Nussbaumer (nussbaumer_at_130.92.4.10) wrote:
: Hi,
: I would like to create a unique index on two numerical fields in
: a table which has about 20'000 records.
: I did a
: SELECT COUNT(NO1||'.'||NO2)
: FROM ...
: WHERE ...
: and a
: SELECT COUNT(UNIQUE(NO1||'.'||NO2))
: FROM ...
: WHERE ...
: Now the first SELECT reports 20'000
: and the second reports 19'999
: which means that I have _one_ DOUBLE ENTRY and my Index won't work!
: My question is, how do I spot the double entry?
: BIG thanks for your answer
: Ma
:
:

Try...

 SELECT NO1||'.'||NO2, COUNT(NO1||'.'||NO2)  FROM ...
 WHERE ...
 GROUP BY NO1||'.'||NO2
 HAVING COUNT(NO1||'.'||NO2) > 1 This should give you a list of any duplicated rows in your table. (The HAVING clause works like the WHERE clause, but allows you to specify aggregate columns like COUNT, SUM etc)

Keith

--
Keith Everitt                               Telephone: +44 344 850873
D9 Consultancy Group                        Facsimile: +44 344 850556
Siemens Nixdorf Information Systems         E-mail   : K.Everitt_at_sni.co.uk