Query problems 1

[bamundsen]

Hello:

Here is an example of my data:

catalogid item_desc sell_price item_cost
1 black widget 25 5
2 black widget 25 5
3 green widget 20 3.5
4 green widget 20 3.5
5 white widget 40 10


I am trying to select all items and sort by sell_price. However, I don't want to see duplicate data. For example, in the sample data above, I only want to see 1 black widget because catalogid 1 & 2 have the same values. I have tried grouping, but cannot get it to work. Any ideas would be greatly appreciated. If this is confusing, please let me know and i will find a different way to explain it.

Thanks,

Brett

 

[jbenson001]

If you don't care which ID you return for the duplicate rows, try this.. it's untested:

select A.*
from YourTable A
inner join
(    select Max(catalogid) as maxcatID, [item desc]
    from YourTable 
    group by [item desc]
) B on A.[item desc]=B.[item desc] and A.catalogid=B.maxcatid

Jim

 

[mrdenny]

You want to use the DISTINCT command.

select distinct item_Desc, sell_price, item_cost
from table1

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLSister]

if it does not matter which of the repeats you choose:

select min(catlogid), item_desc, sell_price, Item_cost
from table1 group by item_desc, sell_price, Item_cost

Of you will items with differnt sell prices or item cost in here but the same description they will show up.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jbenson001]

SQLSister's is the best solution...

 

[bamundsen]

Thank you for your replies, SQLSister's response was very helpful! Problem solved.