Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Why do I have to group all fields when using MIN/MAX?

    Lets say I have a list of customers, order date, and an item they ordered. I want to grab the MIN order date by customer, and the associated product. If I do:

    Code:
    SELECT customer,min(order date),item
    FROM mytable
    GROUP BY customer
    I get an error because item is not aggregated. But I don't want to group on it and i don't want to select a min/max of it. i just want it to carry over the item that is associated with that min order date.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    For the following data, what should the result be?
    Code:
    Customer  Date         Item
    A         1900-01-01   One
    A         1900-01-01   Two
    A         1900-02-01   Three
    B         1900-01-01   Four
    B         1900-01-02   Five
    C         1900-01-01   Six
    C         1901-01-01   Seven
    C         1902-01-01   Eight
    C         1903-01-01   Nine
    C         1904-01-01   Ten
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Sorry for the super late reply. i would want the following results:

    Code:
    Customer  Date         Item
    A         1900-01-01   One
    B         1900-01-01   Four
    C         1900-01-01   Six
    Similarly, i have situations where I want to return the most expensive product a customer has ordered (MAX prodCost), as well as that product's name. But again, if I do:
    Code:
    SELECT customer,prodName,MAX(prodCost) from myTable
    GROUP BY customer,prodName
    that groups by the product. I just want to see the most expensive product and that product's name.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yeah, that one hung around for a bit!

    To get past the need for GROUP BY, you can use OVER Clause (Transact-SQL) like:
    Code:
    DECLARE @t TABLE (
       Customer     CHAR(1)
    ,  D            DATE
    ,  Item         VARCHAR(20)
       )
    
    INSERT INTO @t  (Customer, D, Item)
       VALUES
          ('A', '1900-01-01', 'One'),   ('A', '1900-01-01', 'Two'), 
          ('A', '1900-02-01', 'Three'), ('B', '1900-01-01', 'Four'), 
          ('B', '1900-01-02', 'Five'),  ('C', '1900-01-01', 'Six'), 
          ('C', '1901-01-01', 'Seven'), ('C', '1902-01-01', 'Eight'), 
          ('C', '1903-01-01', 'Nine'),  ('C', '1904-01-01', 'Ten');
    
    WITH myCTE (Customer, D, Item, n) AS
    (
       SELECT
          Customer, D, Item
    ,	  Row_Number() OVER (PARTITION BY Customer ORDER BY D, Item) AS n
          FROM @t
    )
    SELECT Customer, D, Item
       FROM MyCTE
       WHERE  1 = myCTE.n
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    thank you!

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    WHICH one of the million ordered items did you want displayed for the DoD?

    I get an error because item is not aggregated. But I don't want to group on it and i don't want to select a min/max of it. i just want it to carry over the item that is associated with that min order date.
    WHICH one of the tens of millions ordered items did you want displayed for the DoD? Did you ever read a book on RDBMS? A column is a scalar value, not another table of several million rows.

    Please read a book -- any book-- on RDBMS so you will not post things like this again. Forums are for sophomore to graduate questions, not for week #1-3 questions.

  7. #7
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    WHICH one of the tens of millions ordered items did you want displayed for the DoD? Did you ever read a book on RDBMS? A column is a scalar value, not another table of several million rows.

    Please read a book -- any book-- on RDBMS so you will not post things like this again. Forums are for sophomore to graduate questions, not for week #1-3 questions.
    thanks for the help pal. I know why the error was occurring, I was simply trying to document my issue in common speak to make it easier for others to grasp what I was looking for.
    Last edited by clawlan; 05-14-13 at 17:57.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think clawlan is rushing out to buy one of Joe's books at this moment.

    I'd have deleted Joe's post if I'd seen it before clawlan quoted it...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    Please read a book -- any book-- on RDBMS so you will not post things like this again. Forums are for sophomore to graduate questions, not for week #1-3 questions.
    ...and as a follow up, Joe has been warned MANY TIMES that it is not his business to dictate who should or should not post, and what should or should not be posted.

    At dbforums, we encourage participation at all levels of expertise.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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