Re: complex duplicate row

adrian.baceanu@gmail.com said: > > I have the following table > > create table "contav".f103 > ( > codc char(5), > codfisc char(20), > nrprezi integer, > nrprezf integer, > buc integer, > taxa float, > fel char(1), > numeex char(50), > adrex char(50), > locex char(30), > judex char(2), > data date, > timp datetime year to second, > nrord serial not null constraint "contav".n308_126, > regl char(1) > ); > > > It's there an SQL query to select the rows that are identical in all > the columns, excepting codfisc (and nrord eventually, 'cause its > serial) ? DISCLAIMER: I'm seriously drunk. And I don't have an instance to play with. SELECT codc , nrprezi , nrprezf , buc , taxa , fel , numeex , adrex , locex , judex , data , timp , regl FROM f103 GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13 HAVING COUNT(*) > 1 ORDER BY 1,2,3,4,5,6,7,8,9,10,11,12,13 -- Bye now,Obnoxio "C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com sending to informix-list

But how to select duplicate rows (identical in all the columns, except codfisc) and in the same time the result also to include this column, codfisc?

In article <1134835142.102427.138450@g47g2000cwa.googlegroups.com>, "adrian.baceanu@gmail.com" <adrian.baceanu@gmail.com> wrote: > But how to select duplicate rows (identical in all the columns, except > codfisc) and in the same time the result also to include this column, > codfisc? You're talking nonsense. A B 1 A B 2 You want: A B ?? What goes in ??, 1 or 2? If you just want "Pick one", try select ...., MAX(codfisc) from .. group by ... MIN works fine too. Karl

adrian.baceanu@gmail.com wrote: > But how to select duplicate rows (identical in all the columns, except > codfisc) and in the same time the result also to include this column, > codfisc? > select * from contav a where exists ( select codc, nrprezi, ...., count(*) from contav b where a.codc = b.copdc and a.nrprezi = b.nrprezi and ... -- for all columns you want dup matching on group by 1, 2, ... having count(*) > 1 ); Then sit back and knit a sweater. Art S. Kagel