Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Unanswered: SQL Server 2000 Query

    hello
    im running sql server 2000 and im trying to run a query to return each distinct category, and then a product from that category

    here is the query i am using:

    SELECT DISTINCT left(productID,2) as category,
    MAX(productID) as example_product
    FROM products
    where inventory_count > 0 and active_product=1
    GROUP BY left(field1,2)

    it works perfect, but im trying to add in there something so that it chooses the example_product that has the greatest inventory_count productID

    when i try and put in a "order by inventory_count" it gives me syntax errors

    please let me know
    thanks!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    SELECT left(P.productID, 2) as category, P.productID, P.inventory_count
    FROM products as P
       INNER JOIN (SELECT left(productID,2) as category,  max(inventory_count) as max_inventory_count
    				FROM products
    				where inventory_count > 0 and active_product=1
    				GROUP BY left(productID,2)
    				) AS C ON
            left(P.productID,2) = C.category AND
    		P.inventory_count = C.max_inventory_count
    If multiple Products have the same (highest) inventory_count, they will all appear in the result.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2009
    Posts
    3
    i tried your code and it runs very slow
    there are 20,000 line items btw
    also like you said
    when there are matching records it displays them all

  4. #4
    Join Date
    Nov 2009
    Posts
    3
    it tried your code,

    its very slow
    there are 20,000 line items btw
    and like you said,
    when the items are the same it returns all the rows

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by duro View Post
    when the items are the same it returns all the rows
    Which row do you want to return if there are several rows with the same highest inventory_count? You need to be more specific about your selection criteria.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    If you had SQL Server 2005, the solution would be a lot faster and unique (CTE, ROW_NUMBER()).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •