Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
DoktorD1313
Starting Member
1 Post |
Posted - 2013-08-21 : 16:12:49
|
Ok, here's what I'm trying to do.I have a table with four important columns (only four important to this problem, anyway).One column is ID (the primary key), second is ItemID, third is SupplierID, and the last is Cost.This table contains one row for every item/supplier pair that exists in the store.In other words, if a particular caulking is ItemID '871111' and had two suppliers '197' and '62', the table would look likeID | ItemID | SupplierID | Cost | | | | |81 |871111 |197 |1.27 |82 |871111 |62 |1.17 |83 |253165 |197 |.95 |84 |253165 |62 |1.05 |I'm looking for a query that would return the lowest cost for each unique ItemID in the table to show the lowest cost vendor.What I'm looking for it to return would be:ID | ItemID | SupplierID | Cost | | | | |82 |871111 |62 |1.17 |83 |253165 |197 |.95 |From there I'd be able to update my item file with the primary vendor being the cheapest.I'm starting to tear my hair out trying to think of how to do this. I have a feeling the answer is staring me in the face. I just can't see it with my tunnel vision. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-21 : 16:22:40
|
[code]SELECT ID, ItemID, SupplierID, CostFROM( SELECT *, ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY Cost ASC) AS RN FROM YourTable)s WHERE RN = 1[/code] |
|
|
sivadss2007
Starting Member
18 Posts |
Posted - 2013-08-28 : 06:52:44
|
SELECT ID,ItemId,SupplierId,cost,row_number() over (partition by itemid order by cost ) as rowidinto #tempfrom table_nameselect * from #temp where rowid = 1P.Siva |
|
|
sivadss2007
Starting Member
18 Posts |
Posted - 2013-08-28 : 06:52:45
|
SELECT ID,ItemId,SupplierId,cost,row_number() over (partition by itemid order by cost ) as rowidinto #tempfrom table_nameselect * from #temp where rowid = 1P.Siva |
|
|
|
|
|
|
|