SQL statement
-
Really easy question:
How do I write a SQL statement where I want one field unique (MasterCodeID) but several other columns from the same table, say Field 1, Field 2 that are not unique?
Thanks
Sam
-
A little more information would be helpful. The structure of the table and a couple of test records would be even more helpful. From your initial request a simple select "Select * from TableA where MasterCodeID = 134" would meet your requirements if masterCodeID were repeated in the table several times for different records.
Based on the information given I doubt you'll get anything more helpful unless we have a clairvoyant out there.
James.
-
As JLK mentioned, I'm not getting what you are trying to accomplish. The only thing that pops into my head is that maybe you are misunderstanding how SELECT DISTINCT works, and are assuming that it requires each column to be unique. It doesn't do that, as it only requires that the entire row be unique, thus:
SELECT DISTINCT MasterCodeID, Field 1, Field 2, Field 3 FROM TableA would only return those rows where all four combined create a unique row. The best explanation I can give is assuming they are all varchars, DISTINCT would only eliminate those in which the concatenated results of all of the selected fields are duplicates of another row.
The only other thing I can think that you're looking for is one row per MasterCodeID, but then which values from Field1, 2, and 3 do you want to display?
-
To strictly answer you question you could:
Select distinct(MasterCodeID),Min(othercolum),max(anothercolum) ...
could be used to get something from the other non-distinct colums. You choose the aggregate function that suits your purposes.
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply