[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.
http://www.waetech.com/service_areas/GA/
706.210.0168
More information about the thelist
mailing list