Using SQL to select unique records

[PaulBarter]

I need to create a SQL statement for a control in VB6 to select only unique values of 2 fields:

eg

SELECT Field1, Field2, Field3, Field4 FROM MyTable ORDER BY Field2, Field3;

but I only want 1 record for any unique Field2 & Field3 combination.

If I use DISTINCT then it automatically include Field1 and Field4 in it's uniqueness test.

This is a file created in Access 2000

Any ideas?

 

[Bluejay07]

Your current select command will not allow you to be selective on the results. It will find all results that correspond to the query. You will most like require a more complex query with joins and a where clause.

You can also use forum183 for more help regarding the restructuring of your query.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[MakeItSo]

You say

I only want 1 record for any unique Field2 & Field3 combination.

The question is: WHICH record? In what manner is this one record unique?
Answering that question might provide you with the information you require.

Can you post some sample records and explain which of these would be such a unique record?
Do you have true duplicates which you want to sort out?

Cheers,
MakeItSo

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[PWise]

SELECT Max(Field1) as mf1, Field2, Field3, min(Field4) as mf4 FROM MyTable
Group by Field2, Field3
ORDER BY Field2, Field3

 

[gmmastros]

PWise,

You should explain that with your suggested query, it's possible to get values for Field1 and Field4 from different rows in the table. Maybe this is OK for PaulBarter, but maybe it's not either.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

Paul:

George is right perhaps you can explain what you are trying to achive

PWise

 

[PaulBarter]

I am using the data in a multi-column drop down combo I have written.

You are right - what does make it unique.

Thinking more about it Field4 is unnecessary and was included in mainly as a check in development that the selection was correct, but Field1 is in fact the record ID that I need for future processing, and will of course always be unique.