DISTINCT syntax; selecting one record from multiple

Hopefully this question is not horribly out of place, and hopefully hasn't been answered countless times in the last day. I've searched this forum and several other places, but can't seem to find the right solution.

I have three tables "items", "dates", "membership", and "collection".

"items" joins "dates" on Id, a unique identifier. "collection" defines certain collections of "items", each has a unique collection_id. So "membership" has an Id field, and a collection_id field. Each "items" record can belong to multiple "collection"s.

The query:

select a.Id, a.Name, b.Date from items as a LEFT JOIN dates as b on (a.Id = b.Id) LEFT JOIN membership as c on (a.Id = c.Id) LEFT JOIN collection as d on (c.collection_id = d.collection_id)

returns the expected results, but I need to only get ONE of each "items" record, regardless of how many memberships it has. The membership record it returns is unimportant. I tried DISTINCT, but I'm not sure I have the syntax down right, and can't seem to find documentation or a sample that applies to this situation.

If anyone has any suggestions, I would love to hear them and appreciate it very much. Thank you.


D. Mors

To use DISTINCT, apply it to the column in the SELECT list that you want only one of. In this case,Continue the SQL query as usual.

One small suggestion on table aliases - the same "rules" apply as programming - use variables (i.e. table aliases) that mean something. e.g. items as i

Stumpy,
Thanks for the reply. Unfortunately, that did not seem to work. Mayhaps I'm doing something else wrong... I'll have to scrutinize my query and see what may be amiss.

I have read elsewhere that DISTINCT applies to all the columns in the SELECT statement, so I'm wondering if selecting certain columns in parenthesis is only valid in certain servers. It does not throw an error or exception, so I assume my server (MSSQL Server... 2000, I think) is accepting it.

I'm at a loss. Any other possible solutions? Thanks again for your help.

Another suggestion - if you can, use a server-side language to filter the recordset.

Look into grouping results as well. That may net some weird results, but I've had some luck using that to select distinct rows that included timestamps but in which some of the non-timestamp fields were identical and needed not to be duplicated.