Basic Query Help

[jchastai]

For some reason I cannot put this together this morning.

I have two rows in a SQL 2000 database that contain exactly the same information with the exeption of one numeric field. I would like to pull one of those records - the one with the larger field value.

Help?

-- Thanks

 

[tlbroadbent]

Select

-- all columns except the
-- column that is different
Col1, Col2, ... ,

-- Max of column that is different
Max(ColN) As ColN

From TableA

-- optional where clause
Where <your criteria, if any>

-- Group by all columns but one
Group By Col1, Col2, ...

-- Select only rows with
-- more than one occurrence
Having Count(*) > 1 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jchastai]

Thanks, I was missing the Having clause.

 

[jchastai]

Okay, to change the question a little bit.

Now I have two records with five fields. Field 1, 2, and 3 are the exact same between the two records. Field 4 is numeric, different between the two records, and I don't really care about it. Field 5 is numeric and different between the two records.

I want to put the record that has the greater value in Field 5 and display all of the other fields for that record including Field 4 which is not the same betwen the two records.

If this is not clear enough - I have two &quot;duplicate&quot; records in the database. Each record has a unique record ID number and another single field that may or may not be different. All of the other fields are exact duplicates of each other. I want to get a list of all of the record ID numbers for the duplicate record pairs in which this other field is greater (i.e max).

Thanks for the help

 

[tlbroadbent]

How about using a sub-query like the following? The drawback is that this will return multiple records if field5 is also duplicated.

Select *
From Table t1
Inner Join
(Select field2, field3,
Max(field5) As Max5
From table
Group By field2, field3
Having Count(*) > 1) q1
On t1.field2 = q1..field2
And t1.field3 = q1..field3
And t1.field5 = q1.Max5 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.