SQL query?

spark · June 15th, 2005, 01:41 PM

I have a table as following,
Field1 Field2 Field3
1 a 1
2 a 2
3 a 3
4 b 4
5 c 5
I would like to create a query only retrieve records which Value of field2 is unique.
results will be:
Field1 Field2 Field3
1 b 4
1 c 5

Thanks

spark

gbianchi · June 15th, 2005, 01:50 PM

hi there

Code:

 
SELECT Last(field1), Last(field2) , Last(field3) 
FROM table1
GROUP BY field2
HAVING Count(field2)=1

.

HTH

Gonzalo

spark · June 15th, 2005, 02:20 PM

I know it works for access. But I am using showcase query now. It does not have last(),First(). Thanks for this idea.

spark

gbianchi · June 15th, 2005, 02:24 PM

a replace with max or min will do the same trick since you are always retriving unique rows..

HTH

Gonzalo

Jeff Mason · June 15th, 2005, 08:39 PM

Quote:

quote:
I would like to create a query only retrieve records which Value of field2 is unique.
results will be:
Field1 Field2 Field3
1 b 4
1 c 5

I do not understand how you arrive at the values of 'Field1' in your resultset. For example, where does the value of '1' for Field1 come from for the row in the resultset where the Field2 value is 'b'?

If you want the rows in the table where Field2 is unique, i.e. those rows where a given value of Field2 is present in only one row then:

Code:

SELECT * FROM yourtable WHERE Field2 IN
    (SELECT Field2 FROM yourtable GROUP BY Field2 HAVING COUNT(*)=1)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com