[thelist] SQL Server SELECT TOP on result set

[Les Lytollis]

Hi Joshua

Thanks for that - MIN(ProductName) is what I was trying to get to. 

The definition of first StockItemName is fairly irrelevant in this case,
they should all be the same since they are entered by a "bulk modify"
routine, but occasionally individual products are ammended or added (and
often misspelled!).

As far as splitting apart the cleaned data, this will actually be a
SELECT INTO as part of a much larger project taking legacy data used
internally and transforming it for our customers for use over the web.
So I now have the joy of not only taking the cleaned data out into a
separate field, but creating a new entity (Stock Item) and maintaining
the relationships of the old data and the transformed data!

Can you build us a web site they said...

Ho Hum!
Les Lytollis
Web Developer


> -----Original Message-----
> From: Joshua Olson [mailto:joshua at waetech.com] 
> Sent: 26 February 2004 14:14
> To: thelist at lists.evolt.org
> Subject: RE: [thelist] SQL Server SELECT TOP on result set
> 
> 
> > -----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



********************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.
***********************************************************************************