Select distinct one some fields, but return all feilds

[shumaker]

I was curious...

Is there a way to select distinct on a combination of some fields and
the for each record returned also get the other fields of an
arbitrarily chosen record matching the fields in the distinct record.

For example, if I have a select distinct on say three fields:
SELECT DISTINCT Code1, Code2, Code3
but the table also has other fields, maybe Foo1 and Foo2, and I want
Foo1 and Foo2 to also be displayed. Since there may be multiple
records that match a particular Code1, Code2, Code3, then I just want
one of those to be arbitrarily chosen.

[David Portas]

What's the key of this table? It would be useful to know. Assuming it is
key_col, you could do this:

SELECT code1, code2, code3, foo1, foo2
FROM YourTable AS T
WHERE key_col =
(SELECT MIN(key_col)
FROM YourTable
WHERE code1 = T.code1
AND code2 = T.code2
AND code3 = T.code3)

This also assumes that code1, code2 and code3 are non-nullable columns.
Again, it does help if you post a better spec of your problem. The best way
is to include DDL (CREATE TABLE statement including keys and constraints),
sample data (INSERT statements) and show your required end results.
Otherwise answers are just guesswork.

--
David Portas
SQL Server MVP
--

[shumaker]

Very helpful. I didn't think I should post scripts because the tables
have alot of fields, and the queries were kind of long(and at least to
me seemed complicated). I thought it would overcomplicate things. So
I thought a simplified example would be better. I guess I should make
a database just for creating simple tables in for experimentation so
that I can post scripts from them.

[David Portas]

If possible, simplify your DDL to just the essential columns (including the
keys). Yes, it's a good idea to keep a scratch database on your laptop or
desktop for this sort of thing. See also:
http://www.aspfaq.com/etiquette.asp?id=5006

Glad I helped.

--
David Portas
SQL Server MVP
--