Eliminate duplicates in ONE field 2

[rccline]

How do you elimite duplicates in just one field?

I have a table:

No. Fruit Vegetables
1 Apples Carrots
1 Apples Beets
2 Cherries Carrots
3 Banannas Beets
3 Banannas Beets
4 Oranges Broccoli

Now, I only want one No. I don't care which of the remaining records is either not displayed in the query or is deleted. I just don't want duplicates in Field "No."

No. Fruit Vegetables
1 Apples Carrots
2 Cherries Carrots
3 Banannas Beets
4 Oranges Broccoli

I've tried SELECT DISTINCT. That won't work. I've tried UNION SELECT, that won't work.

Any Solution?

Thanks

Robert

 

[PHV]

SELECT No, Fruit, Max(Vegetables) AS Vegetable
FROM yourTable
GROUP BY No, Fruit

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rccline]

The query runs, but doen't work. I can't see that it perfomrs any operation. In this case, the tblTEMP.InstrumentID is "No."

SELECT tblTEMP.InstrumentID, tblTEMP.Volume, tblTEMP.Page, tblTEMP.Vol_VCT, tblTEMP.Page_VCT, tblTEMP.GrantorName, MAX(tblTEMP.GranteeName) AS GranteesName
FROM tblTEMP
GROUP BY tblTEMP.InstrumentID, tblTEMP.Volume, tblTEMP.Page, tblTEMP.Vol_VCT, tblTEMP.Page_VCT, tblTEMP.GrantorName, tblTEMP.GranteeName;

Robert

 

[lespaul]

you need to remove the tblTEMP.GranteeName field from the GROUP BY:

SELECT tblTEMP.InstrumentID, tblTEMP.Volume, tblTEMP.Page, tblTEMP.Vol_VCT, tblTEMP.Page_VCT, tblTEMP.GrantorName, MAX(tblTEMP.GranteeName) AS GranteesName
FROM tblTEMP
GROUP BY tblTEMP.InstrumentID, tblTEMP.Volume, tblTEMP.Page, tblTEMP.Vol_VCT, tblTEMP.Page_VCT, tblTEMP.GrantorName;


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[rccline]

Thanks for the reference AND the tips.

I removed the field you suggested. It still doesn't work.

SELECT tblTEMP.InstrumentID, tblTEMP.Volume, tblTEMP.Page, tblTEMP.Vol_VCT, tblTEMP.Page_VCT, tblTEMP.GrantorName, MAX(tblTEMP.GranteeName) AS GranteesName
FROM tblTEMP
GROUP BY tblTEMP.InstrumentID, tblTEMP.Volume, tblTEMP.Page, tblTEMP.Vol_VCT, tblTEMP.Page_VCT, tblTEMP.GrantorName;

Thanks.

robert

 

[PHV]

My suggestion should work with the sample data YOU posted.
Why not explaining your issue with REAL thingies ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rccline]

Thanks PH. I thought the example would explain what I was doing with the real data. It seemed analagous to me, but for some reason I don't understand, it wasn't. I just don't understand grouping; that's clear enough.

In the "Real thingies," I've got a field called InstrumentID which contain duplicates of the ID numbers.

I want only one case of InstumentID The other data doesn't matter, but I need at least one record of each InstrumentID.

 

[Melagan]

For a better understanding of the GROUP BY clause, check out this page:

http://www.devguru.com/Technologies/jetsql/quickref/group_by.html

Pretty good explination, and spelled out in pretty plain English.

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Further regarding that page, I realized something they didn't explain clearly. The HAVING clause comes into play when using a GROUP BY clause. I believe the pages says the HAVING clause is similar to the WHERE clause.
Indeed it is, but the HAVING clause should be used only to restrict aggregate functions in your SELECT clause, whereas the WHERE clause should be used for restricting all other data.

Example:

SELECT t.[No]
, t.Fruits
, Max(t.Vegetables) AS MaxVeggies
, Count(t.Vegetables) AS VeggieCount

FROM tblYourTable AS t

WHERE t.Fruits<>"Apples"

GROUP BY t.[No]
, t.Fruits

HAVING Count(t.Vegetables) < 3

Hypothetical data and results aside, that's my basic understanding of how the WHERE, GROUP BY, and HAVING clauses work together. An expert might have corrections or better explinations, and I'm always up for furthering my knowledge =) Otherwise, hope this helps!

~Melagan
______
"It's never too late to become what you might have been.

 

[lespaul]

Then you want the MAX InstrumentID:

SELECT Max(tblTEMP.InstrumentID) As MaxInstrument, tblTEMP.Volume, tblTEMP.Page, tblTEMP.Vol_VCT, tblTEMP.Page_VCT, tblTEMP.GrantorName, tblTEMP.GranteeName
FROM tblTEMP
GROUP BY tblTEMP.Volume, tblTEMP.Page, tblTEMP.Vol_VCT, tblTEMP.Page_VCT, tblTEMP.GrantorName, tblTEMP.GranteeName;

 

[rccline]

Then you want the MAX InstrumentID:"

I thank all who offered help... Especially to Lespaul who explained grouping!! That helps enourmously.

Robert

 

[rccline]

Special thanks to Melagan. Thanks for getting me on the road to understanding and for the references. As you could tell by my post, I was completely clueless about grouping.

Cheers,

Robert