SELECT - SQL Question

[spayne]

I have a database table in which I want to select all fields, but I would like to use the DISTINCT clause on only two fields. Is there any way I can do this, or is there some way I can utilize the FILTER clause here?

Thanks,
Steve

 

[jimstarr]

Hi Steve,

Not exactly sure what result you're after. How about

SELECT * FROM mytable GROUP BY field1, field2

Jim

 

[rgbean]

Steve,
Note that Jim's answer (specificly the * syntax) won't work in VFP 8.0+, as you need to specify only fields that are grouped or aggregate funtions on fields or expressions (e.g. SUM(), COUNT(), MAX(), MIN(), AVG(), etc.) in the field list.

Rick

 

[baltman]

Rick,

Can't you set the SQL engine to be VFP 7 and earlier compatible with a SET() command?

I agree a more involved solution would be better in reducing unintended consequences....

Brian

 

[Mike Lewis]

Steve,

I'd agree with Jim's solution, provided you execute
SET ENGINEBEHAVIOR 70
just before running it (if you are using VFP 8.0 or above).

However, you should realise that the values in the other fields (the non-distinct ones) will be pretty arbitrary. Since you will only get one occurrence of each combination of the distinct fields, VFP has no way of knowing which records you want to use to populate the other fields.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[rgbean]

Brian,
Just remember that using SET ENGINEBEHAVIOR 70 in 8.0 & 9.0 will impact performance and as Mike suggests, it gives "bad" (questionable, ambiguous?) results, and just doesn't match with SQL standards.

Rick

 

[spayne]

Thanks for all the replies. I am using version 6.0 so that shouldn't be a problem. Unfortunately, a few new fires popped up that I need to put out before I can look into this issue any further. Hopefully I can get to it sometime tomorrow or Friday. It looks like Jim's suggestion is what I'm after, though.

Thanks again, and I'll let you all know.

Steve