SQL Query problem

[jballum]

I have a query currently with something like this:

select field1, field2, field3
from table
group by field1, field2, field3
having count(*) > 1

What I want to do now is return another field based on same criteria. In other words return a field4 based on the field1 and field2 and field3 that satisfied the original query.

Sample output:

Field1 Field2 Field3 Field4
AAAAAA AAAAAA AAAAAA 111111
AAAAAA AAAAAA AAAAAA 222222
AAAAAA AAAAAA AAAAAA 333333
{satisfied original query because count(*) was 3}

AAAAAA BBBBBB BBBBBB 111111
AAAAAA BBBBBB BBBBBB 444444
{satisfied original query because count(*) was 2}

BBBBBB BBBBBB CCCCCC 111111
BBBBBB BBBBBB CCCCCC 555555
BBBBBB BBBBBB CCCCCC 777777
BBBBBB BBBBBB CCCCCC 888888
{satisfied original query because count(*) was 4}

I seem to remember Oracle lets you do something like the following:

Where field1, field2, field3 in
(select field1, field2, field3 ......)

but SQL Server doesn't.

Any help would be appreciated.

JB

 

[foxdev]

Is there a primary key or unique value in this table?

Also, while some systems such as Teradata allow multiple return values in a subselect, SQL Server does not.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[jballum]

There is a unique index but it is made up of 7 concatenated fields, (not my design) so I don't know how helpful it would be.

JB

 

[jballum]

I may have found one way to solve my problem (I haven't tried it yet as they don't like running too many queries during heavy production times):

select B.field1, B.field2, B.field3, A.field4
(select distinct field1, field2, field3, field4
from table) A
inner join
(select field1, field2, field3
from table
group by field1, field2, field3
having count(*) > 1) B
on A.field1 = B.field1 and
A.field2 = B.field2 and
A.field3 = B.field3
order by
B.field1, B.field2, B.field3, A.field4

If anyone can think of any better ideas let me know.

Thanks

JB

 

[Guest_imported]

Depending on what field1, field2 and field3 data is like
could
select field4 from ...
where field1+field2+field3 in
(select field1+field2+field3 from ...)

 

[jballum]

kmitchel,

I tried that way before my posted solution and it took a really really really long time (actually stopped it).

Thanks for the suggestion.

JB