selecting rows ina query where only the 1st field is distinct 1

[spleenboy]

I'm an Oracle developer who is trying to get to grips with Access!
I need to select records in a multi table query where it only checks that one field is unique i.e.

col1 col2
10 apple
10 orange
20 grapes
20 pears

I need it to return just 2 records, 1 for value for 10 and 1 for key 20. It doesn't matter which one of the rows it returns as long as it returns just one distinct value for values in col1. Because the composite values in the column are unique, access returns all rows. This is very simple to do in Oracle, but I can't get Access to play ball.
Any ideas??

 

[mordja]

spleenboy,

You just want the distinct set of values in Col1 ??

SELECT DISTINCT col1 FROM YourTable

Mordja

 

[spleenboy]

Kind of. I need the distinct values from col1, and any value from col2 i.e.

10 orange
20 grapes

I am happy with any combination as long as I only get 1 distinct value for col1.
Also, both columns are coming from diferent tables. I dont know if this complicates things in access.

 

[mordja]

spleenboy,

Provide a little more information about your two tables, ie their relationship and some sample data.

Mordja

 

[PHV]

SELECT col1, Min(col2) As AnyCol2
FROM yourTable
GROUP BY col1;

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

 

[spleenboy]

Thanks PHV. That is very useful.Out of interest, I am use to working with standard SQL within an Oracle database. What does Access use? It seems to be a strange flavour of SQL.
Also, what does AnyCol2 do in the example you gave?

 

[PHV]

what does AnyCol2 do in the example you gave
Its an alias for the calculated column.
The SQL code I posted is pure standard ...

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

 

[JonFer]

In Access 2000 and XP, I think you can change a database to use the ANSI-92 SQL syntax. It's not a full implementation but closer than the Jet SQL that is the Access default.

John

http://www.interautomateconsulting.com