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 helpSELECT MAX(bidPrice) as bidPrice,uId,bidDate FROM t_bid where auctionId=1
GROUP BY uId,bidDate

EDIT: What do you mean "multiple" rows?|||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|||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?|||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...|||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)|||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...

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|||?!?|||Just cut and paste it in to QA, and execute it...

is the what?!? is for?

Is there a question there?|||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|||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?