Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2003
    Posts
    52

    Unhappy Unanswered: a simple group by query, plz help

    hi i want to get the result like
    SELECT MAX(bidPrice) as bidPrice,uId,bidDate FROM t_bid where auctionId=1

    but it doesn't allow me as it says
    Server: Msg 8118, Level 16, State 1, Line 1
    Column 't_bid.uId' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
    Server: Msg 8118, Level 16, State 1, Line 1
    Column 't_bid.bidDate' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

    i tried some combos for grp by like i used both uId and bidDate in grp by but it gave me multiple rows where as i only want one record with max bidPrice and auctionid=1 or anyother
    plz help

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT MAX(bidPrice) as bidPrice,uId,bidDate FROM t_bid where auctionId=1
    GROUP BY uId,bidDate

    EDIT: What do you mean "multiple" rows?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Posts
    52
    i meant multiple records, i have total of 2 records with auctionid=1 if i write this query as
    SELECT MAX(bidPrice) as bidPrice,uId,bidDate FROM t_bid where auctionId=1 group by uId,bidDate
    then it gives me both results instead of one

  4. #4
    Join Date
    Sep 2003
    Posts
    212
    max(colname) will give u the max value of that column in that table.

    what u wanna do is find the max bidPrice for a a particular uID and bidDate rite?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    But they're not "duplicates"

    They have different values. Uid may have different bid dates..which bid date do you want? MIN, MAX? You need to pick 1, or eliminate from the grouping...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Posts
    52
    i want to get the maxprice, the uid and bidDate of the max price on the condition that i have some auction id

    see
    i can have many bids with same auctionid but there will be one max bidPrice amongst dem, i want that price the biddate(date of that record entered) and that uid(user id who made dat bid)
    Last edited by waqas; 12-02-03 at 15:06.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This would have been alot easier with DDL, DML (which you gave) and sample data..

    If you post like this, it'll be easier for people to help

    here you go...
    Code:
    CREATE TABLE xt_bid (Uid int IDENTITY(1,1), bidPrice money, bidDate datetime, auctionId int)
    GO
    
    INSERT INTO xt_bid (bidPrice, bidDate, auctionId)
    SELECT 22.00, '1900-01-01 12:30:00', 1 UNION ALL
    SELECT 23.00, '1900-01-01 12:31:00', 1 UNION ALL
    SELECT 25.00, '1900-01-01 12:32:00', 1 UNION ALL
    SELECT 24.00, '1900-01-01 12:33:00', 1
    GO
    
    SELECT * 
      FROM xt_bid o 
     WHERE bidPrice IN (SELECT MAX(BidPrice) 
    		     FROM xt_bid i
    		    WHERE auctionId = 1)
    
    GO
    
    DROP TABLE xt_bid
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Sep 2003
    Posts
    212

    Question

    ?!?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just cut and paste it in to QA, and execute it...

    is the what?!? is for?

    Is there a question there?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Oct 2003
    Location
    Manila, Philippines
    Posts
    20
    Brett, just curius of ur DML script
    ------
    INSERT INTO xt_bid (bidPrice, bidDate, auctionId)
    SELECT 22.00, '1900-01-01 12:30:00', 1 UNION ALL
    SELECT 23.00, '1900-01-01 12:31:00', 1 UNION ALL
    SELECT 25.00, '1900-01-01 12:32:00', 1 UNION ALL
    SELECT 24.00, '1900-01-01 12:33:00', 1
    ------

    Whats the difference in this script with ur script?

    INSERT INTO xt_bid (bidPrice, bidDate, auctionId) values (blah bla blah)
    INSERT INTO xt_bid (bidPrice, bidDate, auctionId) values (blah bla blah)
    INSERT INTO xt_bid (bidPrice, bidDate, auctionId) values (blah bla blah)
    INSERT INTO xt_bid (bidPrice, bidDate, auctionId) values (blah bla blah)


    I have not used that INSERT statement yet....with UNION ALL clause???

    Thanks in advance

    -bernie

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you try it?

    It's just 1 insert as compared to 4...

    which has less overhead?

    It's so small it doesn't matter, but it's alot easier to cut and paste examples...

    Isn't more tedious the other way?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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