[thelist] SQL Server SELECT TOP on result set

Joshua Olson joshua at waetech.com
Thu Feb 26 08:14:17 CST 2004

> -----Original Message-----
> From: Les Lytollis
> Sent: Thursday, February 26, 2004 9:01 AM
> 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

This is not a trivial problem.  The solution, though, can be found by
answering the following... what DEFINES the first StockItemName for any
given StockItemCode?  Is it the first alphabetically, or is it the first as
defined by the database's default sort order based on the clustered key?

Also, before you attempt this, I highly suggest that you add a new field to
the table and break apart the StockItemCode from the ProductCode and put the
cleaned fields into separate fields.

Then, if it's alphabetical, then something like this should work:

SELECT StockItemCode, Min(StockItemName) AS StockItemName
FROM products
GROUP BY StockItemCode

If the "first record" is defined by the clustered index, you will need to
employ some fancy footwork to get your answer.  I'll go over that solution
if that's the case.

Joshua Olson
Web Application Engineer
WAE Tech Inc.

More information about the thelist mailing list