[thelist] SQL Server SELECT TOP on result set

[Les Lytollis]

Hi All
 
I need to trasform some data by splitting a field and getting the
distinct results of the first part of the string. The problem is that
the data is dirty and I am trying to "clean" it through SQL. To give an
example:
 
The original data is in the form
 
ProductCode    ProductName
abcd-10            green shirt
abcd-20            green shirt
abcd-30            grn shirt
efg-10               ladies trousers
efg-20               ladies' trousers
efg-30               trousers - ladies
 
I have split out the ProductCode field to get a StockItemCode with a
function and got as far as
 
SELECT DISTINCT fnGetStockCode(ProductCode) AS StockItemCode,
    ProductName AS StockItemName
FROM products
 
StockItemCode    StockItemName
abcd                    green shirt
abcd                    grn shirt
efg                       ladies trousers
efg                       ladies' trousers
efg                       trousers - ladies
 
but I now need to get just the first result for each StockItemName
what I want to end up with is:
 
StockItemCode    StockItemName
abcd                green shirt
efg                   ladies trousers
 
I have tried using TOP 1 but I can't get to "TOP 1 for each distinct
StockItemCode"
Any help would be appreciated
 
 
PS - Tab, Joshua - thanks for input on my last SQL question, I haven't
gotten around to trying it yet, but will post it as a tip when I do!
 
regards
Les Lytollis
Web Developer
 



********************Confidentiality Notice & Disclaimer ***********************
This message, together with any attachments, is for the confidential and exclusive use of the addressee(s).
If you receive it in error, please delete the message and its attachments from your system immediately and notify us by return e-mail.
Do not disclose, copy, circulate or use any information contained in this e-mail.

(1) Whilst we have taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses, we cannot accept liability for any damage sustained as a result of software viruses and would advise that you carry out your own virus checks before opening any attachment.
(2) The sender shall remain solely accountable for any statements, representations or opinions that are clearly his or her own and not made in the course of employment.
***********************************************************************************