CODEWITHSUNDEEP
sqlsql-server-2008
bmurrell30
bmurrell30

Reputation: 713

Trying to return only one type of each item from database

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.

Upvotes: 0

Views: 85

Answers (3)

kjmerf
kjmerf

Reputation: 4335

This one is a little simpler:

SELECT DISTINCT sub.item
FROM (SELECT LEFT(columnName,5) AS item FROM Table) sub

Upvotes: 0

msheikh25
msheikh25

Reputation: 578

Distinct would work if you were only looking for the one column alone. But when you add the remaining columns you need, it is thrown off. You can try this:

select * from (
    select 
    left(col1, 5) col1,
    col2,
    col3,
    col4,
    row_number() over (partition by left(col1, 5) order by col1) rn
    from your_table
) t
where rn = 1

you can change the order by to a different column such as a date/time column if you want to get the first or last record for each item.

Upvotes: 1

pjcognetta
pjcognetta

Reputation: 83

You'll want to have the specific variety type as a separate field for each item, just as a general technique for database design:

10316 | 00 | B

Then you can query using:

SELECT DISTINCT(item)
FROM itemlist
WHERE itemnum=10316;

Hope this helps

Upvotes: 0

Related Questions