I have a database of items that stores each available variety of a given item on a separate row. Just going by the item names, the database records look like this:
10316-00-B 10316-00-M 10316-02-B 10316-03-B 10316-04-B 10316-23-B 10316-88-B
...where "10316" is the actual item, and then everything after that represents a specific variety type. I would like to write a query that would return only one record for item "10316" (or whichever), along with the information associated with that item in all the other table columns, and ignore the rest. So I would get the record for item "10316-00-B", but none of the others. This process would repeat for all other items returned by my query.
I'm not picky about which item of a given group is returned; the top one would do. I just to see one listing each for item 10316, 10317, 10318 and onward, instead of each item and variation.
I've tried DISTINCT left(columnName, 5), but that doesn't filter the records as I need. Does anyone know how to do this? I'm using Sql Server 2008 R2.
UPDATE: I'm trying implement the solution provided by @mo2 -- it appears this one is most likely to return all data for a given item, which is what I want. Unfortunately I'm getting the error "No column name was specified for column 1 of 't'", and I'm not sure how to correct this. My version of his query looks like this:
SELECT * FROM ( select left(item, 5), col1, col2, ... , row_number() over (partition by LEFT(item, 5) order by item) rn from Table ) t where rn = 1
FINAL UPDATE: I marked the answer from @kbball as the solution, but really credit should go to @mo2 as well. The answer from @kbball helped me make sense of the answer from @mo2.
This one is a little simpler:
SELECT DISTINCT sub.item FROM (SELECT LEFT(columnName,5) AS item FROM Table) sub