SQL - unique

[manpaul]

Is there a unique option in SQL

I would like to create a table with a custno and name from another table but only add the record to the new table if the custno is not in the new table already?

Only one record for custno in the new table

Paul

 

[StewartUK]

Paul,

You could use the DISTINCT keyword:

SELECT DISTINCT Custno, name FROM Table1 INTO TABLE Table2

or you can use GROUP BY:

SELECT Custno, name FROM Table1 INTO TABLE Table2 GROUP BY 1,2

If there a very large number of records, I think the SQL will run faster using GROUP BY because I think it works on the results rather than the original table.

Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[mkrausnick]

The GROUP BY clause requires an AGGREGATE function, such as COUNT or SUM, at least in VFP8 and above.

The DISTINCT clause is the correct way to get unique records.

Mike Krausnick
Dublin, California

 

[StewartUK]

Er, I don't think that's actually correct Mike. When I saw what you wrote, I tested it out in VFP8 and grouping without an aggregate works fine.

Stewart

 

[Mike Lewis]

Stewart,

I agree with you.

In VFP 8.0 and above, the rule is: if you do grouping, the expressions in the SELECT list must be either the fields that take part in the grouping or aggregate functions. So, you can do grouping without aggregates, provided you are grouping on all the columns in the result set.

As for the difference in performance between DISTINCT and GROUP BY, I would guess that they would be pretty similar. VFP is clever enough to optimise both forms in the most efficient way. But that's only my guess.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[manpaul]

Thanks

THe distinct work fine, I have to wait to learn the group by command

Paul

 

[mkrausnick]

My mistake - I was going from memory and I could have sworn I read that somewhere. Another misconception obliterated!

I'd still use DISTINCT though, because that's what it's for.

Mike Krausnick
Dublin, California

 

[Mike Lewis]

Mike,

I'd still use DISTINCT though, because that's what it's for.

I agree with that as well. In this context, DISTINCT is much more obvious than GROUP BY.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)