User Profile

Collapse

Profile Sidebar

Collapse
recordscout
recordscout
Last Activity: Mar 26 '10, 04:05 PM
Joined: Dec 2 '09
Location:
  •  
  • Time
  • Show
  • Source
Clear All
new posts
  • recordscout
    replied to DISTINCT and ORDER BY
    in ASP Classic
    OK - Managed to sort something out that will fix it for me at least. I tried the statements above and they were at least twice as slow to execute.

    However I sorted something I am please with!

    SELECT *,MIN(Price) as MINPRICE FROM products WHERE (STATEMENT HERE) GROUP BY NAME order by ORDER

    What I have found this does and nice and quick is select the price as MINPRICE that I can use along with all the other data...
    See more | Go to post

      Leave a comment:

    • recordscout
      replied to DISTINCT and ORDER BY
      in ASP Classic
      OK - Got there in the end and many thanks for all the links and examples:
      http://www.artfulsoftware.com/infotr...p?&bw=1280#101

      They managed to write the problem in words I could not manage!

      You can see my original problem here: http://www.sunglasses-shop.co.uk/ray-ban-sunglasses.asp (not currently fixed) - but for example if you look at product code on the first page 2156 it will show the product added...
      See more | Go to post

        Leave a comment:

      • recordscout
        replied to DISTINCT and ORDER BY
        in ASP Classic
        Thank you... I will check this out right away and report back!
        See more | Go to post

          Leave a comment:

        • recordscout
          replied to DISTINCT and ORDER BY
          in ASP Classic
          Thank you for your help on this!
          Unfortunatley the products are not in any order - I just wrote them like that.

          If it is not possible then I will have to live with it!

          From the MYSQL site I have tried things like:

          mysql> SELECT student_name, MIN(test_score) , MAX(test_score)
          -> FROM student
          -> GROUP BY student_name;

          with no luck. Also - this one looked...
          See more | Go to post

            Leave a comment:

          • recordscout
            replied to DISTINCT and ORDER BY
            in ASP Classic
            OK - I will try and explain the reason it is required.

            ProductA (black) = $20
            ProductA (white) = $40
            ProductA (green) = $45
            ProductA (silver) = $5

            ProductB (white) = $15
            ProductB (green) = $80
            ProductB (silver) = $120
            ProductB (brown) = $10

            ProductC (white) = $10
            ProductC (green) = $5
            ProductC (silver) = $69

            I have a list that requires the products...
            See more | Go to post

              Leave a comment:

            • recordscout
              replied to DISTINCT and ORDER BY
              in ASP Classic
              As a seriously messy workaround I had one statement selection the distinct product names and listing them in order, then another one taking the product name and using this reference to select the price. It works, but very slow!

              I think I could use INNER JOIN if it was 2 tables, but it is just the one I don't think you can use JOIN on one table?

              I have found a lot of people online asking similar questions, but all the answers...
              See more | Go to post

                Leave a comment:

              • recordscout
                replied to DISTINCT and ORDER BY
                in ASP Classic
                Thanks for this and I have previously tried this. What this does is change the list to show the cheapest products first and not the product name first.
                See more | Go to post

                  Leave a comment:

                • recordscout
                  started a topic DISTINCT and ORDER BY
                  in ASP Classic

                  DISTINCT and ORDER BY

                  Hi,

                  I have a table with product names and separate prices.
                  I want to show the distinct product names in alphabetical order, but at the same time show the cheapest price first.

                  I can of course use GROUP BY for the product names, but when I apply the order by PRODUCTNAME to the GROUP BY it will list the first product in the database and not the cheapest product. I can not use INNER JOIN as it is from one table....
                  See more | Go to post
                  No activity results to display
                  Show More
                  Working...