Duplicated Fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Duplicated Fields

Author

Topic

marcelo73
Starting Member

14 Posts

Posted - 2006-11-22 : 14:05:30

I have a table of records, many of which are repeated or differ in 3 of the 5 fields only.
I want to show only those records on a continuous form that are unique in the first 2 fields.
That is as long as the first 2 fields are the same, I only want one to display, regardless of what is in the other 3 fields.
I can accomplish this simply be doing SELECT DISTINCT on the
first two fields only.
My problem is that the other 3 fields have to be displayed on the form as well.

How do I select only one of each record based on the first 2 fields, then also display the other 3 fields for these distinct records.

eg.
Rec F1 F2 F3 F4 F5
---------------------------------------
1 XT XS NN PP OO
2 XT XS RS LL UY
3 XT ZZ RS LL UY
2 XT ZZ RT UL WY
3 PQ OY RT YT TT

I would only expect to display the following records from the above.

1 XT XS NN PP OO
3 XT ZZ RS LL UY
3 PQ OY RT YT TT

Hope you can help me since it's urgent.
Thanks in advance, Marcelo.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-22 : 14:18:27

SELECT F1, F2, min(F3), min(F4), min(F5)
FROM table
GROUP BY F1, F2

You'll basically be getting junk in the last three columns, but you said "regardless of what is in the other 3 fields".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 14:20:55

select t.*
from mytable t
inner join (select f1, f2, min(rec) mrec from mytable group by f1, f2) q on q.f1 = t.f1 and q.f2 = t.f2 and q.mrec = t.rec

Peter Larsson
Helsingborg, Sweden

marcelo73
Starting Member

14 Posts

Posted - 2006-11-22 : 14:28:30

Sorry, I said "regardless of what is in the other 3 fields" but it's not exactly like that. I don't care about the duplicated rows but I need the data from the other 3 fields exactly as it is at the first row found. Is this possible?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 14:32:32

Yes. See my answer.

Peter Larsson
Helsingborg, Sweden

marcelo73
Starting Member

14 Posts

Posted - 2006-11-22 : 14:59:03

Yes Peter, your sentece is right but there's a little problem when I want to insert this query on another table I get the following:

Warning: Null value is eliminated by an aggregate or other SET operation.

(28844 row(s) affected)

The sentence is like this:

select t.*
from tbl2 t
inner join (select num_bol_depos2, importe_cheque2, min(fecha) mfecha, min(numero_cheque) mnumero_cheque, min(agencia) magencia
from tbl2
group by num_bol_depos2, importe_cheque2) q
on q.num_bol_depos2 = t.num_bol_depos2
and q.importe_cheque2 = t.importe_cheque2
and q.mnumero_cheque = t.numero_cheque
and q.magencia = t.agencia

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 15:05:47

What are you doing?
There is no guarantee that all the MIN functions select values from the same record!!!!

select		t.num_bol_depos2,
		t.importe_cheque2,
		t.fecha,
		t.numero_cheque,
		t.agencia
from		tbl2 t
inner join	(
			select		num_bol_depos2,
					importe_cheque2,
					min(rec) mrec
			from		tbl2
			group by	num_bol_depos2,
					importe_cheque2
		) q on q.num_bol_depos2 = t.num_bol_depos2 and q.importe_cheque2 = t.importe_cheque2 and q.mrec = t.rec

Peter Larsson
Helsingborg, Sweden

marcelo73
Starting Member

14 Posts

Posted - 2006-11-22 : 15:19:06

Peter, launching the script I get 28780 rows and if I just do a select * from tbl2 I get 29599 rows.
How is it that I get less rows? That's what I don't understand.

Thanks a lot! Marcelo.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 15:22:19

Then the REC column is not unique for the combination of num_bol_depos2 and importe_cheque2
as you posted in your sample data.

Example:

1 abc def a b c
2 abc def g r e
5 ghi ert i y j
6 ghi ert j u w
5 ghi ert l e s

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 15:24:42

Run this script select f1, f2, rec from yourtable group by f1, f2, rec having count(*) > 1
and post some of the result here...

Peter Larsson
Helsingborg, Sweden

marcelo73
Starting Member

14 Posts

Posted - 2006-11-22 : 15:45:21

I guess by REC you're saying the other 3 fields (rec1, rec2 and rec3) since NUM_BOL_DEPOS2 and IMPORTE_CHEQUE2 are not NULLs and these are the fields that I don't want to have duplicated.

Another thing is I'd like to have this query ordered by FECHA and NUM_BOL_DEPOS2 but it says it's ambiguous. Obviously I cannot use Order by into the view.

Marcelo.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 15:47:53

Yes you can if you also provice TOP 100 PERCENT
No, with REC I am referring to the column in the sample data you provided with your original post.

Peter Larsson
Helsingborg, Sweden

marcelo73
Starting Member

14 Posts

Posted - 2006-11-22 : 15:52:22

Oh, that's right, this is not unique:
1 XT XS NN PP OO
2 XT XS RS LL UY

XT+XS can repeat anywhere at the table. That's why I just need one record of these duplicated and not unique.

Another thing, how can I insert this all this query in another table? Cause if I use INTO where generally goes it won't do it.

Thanks, Marcelo.

marcelo73
Starting Member

14 Posts

Posted - 2006-11-22 : 16:14:11

When I run the following as you asked me:

select num_bol_depos2, importe_cheque2, min(fecha) mfecha, min(numero_cheque) mnumero_cheque, min(agencia) mgencia
from tbl2
group by num_bol_depos2, importe_cheque2, fecha, numero_cheque , agencia
having count( * ) > 1

I get:
Num_bol_depos Importe_cheque Fecha Numero_cheque Agencia
96395372.0 1.490.000 10/08/2005 00:00 0.0 303.0
96450076.0 4.680.000 06/02/2006 00:00 0.0 303.0
203439065.0 5.770.000 11/09/2006 00:00 0.0 364.0
5600847.0 11.820.000 03/07/2006 00:00 0.0 303.0
96143661.0 12.300.000 24/04/2006 00:00 0.0 303.0
203350576.0 10.850.000 04/07/2006 00:00 0.0 303.0
8137753.0 2.380.200 10/04/2006 00:00 0.0 391.0
4283553.0 17.010.900 29/05/2006 00:00 0.0 303.0
NULL 5.070.000 11/07/2006 00:00 0.0 370.0
203590983.0 5.970.000 15/06/2006 00:00 0.0 345.0
91602394.0 64.690.500 19/11/2004 00:00 418972.0 303.0
100235773.0 9.630.000 14/06/2005 00:00 0.0 303.0
99777440.0 8.140.000 16/05/2006 00:00 0.0 366.0
1624420.0 3.490.000 02/06/2006 00:00 0.0 391.0

marcelo73
Starting Member

14 Posts

Posted - 2006-11-22 : 17:12:31

Another thing when I run this query:
select distinct num_bol_depos, importe_cheque from tbl2_boletas_uniplus

I got 15401 rows

so I need to get exactly the same rows but with the other fields which I don't care if they're duplicated or not or what its value is.

I hope you understand me. In case not please ask me. Perhaps I'm not making myself clear enough.

Thanks, Marcelo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources