select distinct

[MrDontKnowNothing]

sorry for that one, but i need some inspiration...

if you do a "select distinct a,b,c from sometable", you get rid of the dublicates. so far, so good. but if you only want to get rid of the duplicates in a, but you also want to retrieve the corresponing b and c, what are you supposed to do?

cheers

alex

 

[SqueakinSweep]

The simplest way is to make use of the GROUP clause. Im assuming you are pulling data from a Fox Table.

Simply

select a,b,c from MyTable group by a,b,c

or to get a count of the duplicates too

select sum(1) as count, a, b, c from MyTable group by a,b,c

Sweep
...if it works dont mess with it

 

[Mike Lewis]

Sqeakinsweep,

select a,b,c from MyTable group by a,b,c

In fact, I think that will give you the same as SELECT DISTINCT a, b, c

To get rid of duplicates in a but not in b or c, you would need:

select a,b,c from MyTable group by a

Alex,

Keep in mind that the contents of cols b and c would be indertimate (that is, you don't know which of a's records they come from), which makes me wonder why you want to do this.

Note also that in VFP 8.0, you would need SET ENGINEBEHVIOR 70 for the above code to be acceptable.

Mike


Mike Lewis
Edinburgh, Scotland

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

 

[zhavic]

Mike,

Keep in mind that the contents of cols b and c would be indertimate (that is, you don't know which of a's records they come from), which makes me wonder why you want to do this.

It can be avoided ( better say, it must be avoided ). Everyone who wants to get rid of the duplicates in a, have to know, which of the records he want in the result.

For example:

Have table :    a   b   c
                1   5   7
                1   2   3
                1   4   1
                2   5   5
                2   3   4

So, if someone wants to group it by the column 'a', he needs
to specify, which value to get from column 'b' and 'c' by a aggregate function.
If someone does not matter which values get from 'b' and 'c', he also needs to use aggregate functions.

For example:
select a, MAX(b), MIN(c) from MyTable group by a

Note, that this statement select a,b,c from MyTable group by a
will work only in VFP, because VFP has simplified SQL engine, but in SQL server you have to specify aggregate function
for each column from select list, that not appear in the group by clause.

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Mike Lewis]

Zhavic,

You are correct. My point was that VFP (7.0 and below) does not force you either to include the aggregate functions or to specify all the selected fields in the grouping.

You said:
Note, that this statement select a,b,c from MyTable group by a will work only in VFP, because VFP has simplified SQL engine, but in SQL server you have to specify aggregate function for each column from select list, that not appear in the group by clause.

That's different in VFP 8.0. By default, 8.0 enforces ANSI 92 SQL in that respect (but that can be overriden with SET ENGINEBEHAVIOR).

Mike


Mike Lewis
Edinburgh, Scotland

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

 

[zhavic]

Thanks, Mike.

I am using only VFP 7, and I am happy that VFP8 now enforces ANSI 92 SQL., because there are more peoples, that want ot use VFP as front application and SQL server as backend, but they are using VFP syntax to write queries on SQL server, and they have problems with it.
Unless GROPUP BY statement, they have problems for example with INTO and FROM clauses, which must be in dedicated order in contrast to VFP.

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.