SQL newbie question (aggregate function problems)

[bsw2112]

Hello

I am not very strong in sql and was wondering if anybody could help me with the following query. I have a silent auction and people are adding their bids for various items. Nobody knows what anyone else bids on anyitem
(**** to delimit each column and value
--table name : BID--
bid_id*****FK_item_id*****bidder_name*****amount
-----------------------------------------------
1*****3*****bill*****5.00
7*****2*****allison*****7.00
2*****3*****allison*****6.00
3*****1*****andrew*****5.00
6*****3*****john*****8.00
4*****2*****bill*****1.25
5*****1*****mathew*****10.00
8*****3*****mike*****5.00


I was hoping to return the highest bid for each item as well as the name of the bidder for that highest bid. I am not having any luck.
I would like to see this as the result

FK_item_id*****bidder_name*****amount
-----------------------------------------------
1*****mathew*****10.00
2*****allison*****7.00
3*****john*****8.00

I can return the highest amount and fk_item_id for each item but as soon as I try to add the name, it doesn't work. What am I doing wrong?

here is my sql that works

SELECT FK_item_id, max(amount)
FROM bid
group by  FK_item_id

I would like to do this

SELECT FK_item_id, bidder_name, max(amount)
FROM bid
group by  FK_item_id

Is this not working because bidder_name is not unique

Please help

bsw

[szlamany]

The "highest" bid for each item is not naturally part of the set of data you have shown - it must be derived.

Basically the "derived set" is put into a sub-query and related back to the primary set.

SELECT FK_item_id, bidder_name, amount
FROM bid b1
where b1.amount=(select max(b2.amount) from bid b2
                  where b2.fk_item_id=b1.fk_item_id)

Of course that can return two bids if they each have the same amount.

There are ways of dealing with that as well - TOP 1's and order by's - but you would need some criteria that would weigh one bid more then the other (like it came in first).

[bsw2112]

Hello szlamany,

Thank you very much for your answer. I am still having problems. I guess I should change my SQL skill label from "not strong" to "pathetic"

Would you be able to look at my screen cap (table layout) I altered some of the column names but am trying to use your SQL. I am currently just trying to get it to work in Acess 2000 and once that works I will port it over to ASP (Access is my backend). I developped a very crappy way of handeling this since my last post, with the following query.

SELECT items.PK_item_id, bid.bid_amount, bid.fname, bid.lname
FROM items INNER JOIN bid ON items.PK_item_id = bid.FK_item_id
ORDER BY items.PK_item_id, bid.bid_amount DESC;

This returns all bids but since they are ordered by item ID (from another table called items) and the bid amount from the bid table, I can now do a crude recordset loop on all records and whenever the item ID changes, I know that I am looking at the highest bid for that item. Not very elegenat I know but it works and currently is my backup

Ofcourse I would rather make your SQL work as it would only return the good record for each item

Would you have time to look at my problem again. I understand if you don't have time.

I am adding my/your query with the new column names and a screen cap of my table

SELECT FK_item_id, fname,  bid.bid_amount
FROM bid as b1
where b1.bid.bid_amount=(select max( b2.bid.bid_amount) from bid as b2 where b1.FK_item_id= b2.FK_item_id)

Thank you szlamany

bsw

[szlamany]

So I can see you know that TABLENAME.COLUMNNAME is a valid syntax for getting a field from a table - you used it in your first query.

Then the query you posted that's from my example looks like this

SELECT FK_item_id, fname,  bid.bid_amount
FROM bid as b1
where b1.bid.bid_amount=(select max( b2.bid.bid_amount) from bid as b2 where b1.FK_item_id= b2.FK_item_id)

As you see the BID table is being used twice - in the outer query and the inner query.

That's why we use the ALIAS trick - name one of the "sets" B1 and the other "set" B2.

But you used the wrong syntax - it should be ALIASNAME.COLUMNNAME - you don't put the table name.

This should work

SELECT b1.FK_item_id, b1.fname,  b1.bid_amount
FROM bid as b1
where b1.bid_amount=(select max(b2.bid_amount) from bid as b2
                 where b1.FK_item_id= b2.FK_item_id)

I don't use ACCESS - but I know you can create a query window and test these - try the query I just posted now.

[bsw2112]

Thank you so much szlamany

It worked.

I was just wondering if you can explain your previous statement?

The "highest" bid for each item is not naturally part of the set of data you have shown - it must be derived

What does that mean and why do I need to uses aliases when I query the same table twice?

I've done SQL Server in the past (mostly stored procedures) but I don't remember ever having to do that. That was a number of years ago when I actualy used SQL more than once every 6 months

Thanks again for your help and have a good weekend
bsw

[szlamany]

...I was just wondering if you can explain your previous statement?

The "highest" bid for each item is not naturally part of the set of data you have shown - it must be derived

Basically that's just my way of saying you don't have a column in that set of data - in that table - for the HIGHEST BID.

You could create a VIEW - like this

Create View BidHighest
 As Select FK_Item_Id,max(bid_amount) "BidHighest" from bid
         Group by FK_Item_Id

Now if you looked at this view it would have two columns - ITEM ID and BID AMOUNT. Only one row per ITEM - with the HIGHEST BID AMOUNT next to that ITEM.

And if we were discussing that "set" of data - that table - the HIGHEST BID amount for an ITEM would be "part of that set of data".

And of course if we had that VIEW you could SELECT from that VIEW and JOIN back to the ORIGINAL BID table using both the ITEM ID and the HIGHEST BID AMOUNT and arrive at the same results as we did in the query you just got working.

Basically you needed to "derive" the highest bid amount. Whether you create a VIEW like I just mentioned - where the "derivation" is more obvious to see - or use a sub-query in a WHERE clause - it's basically the exact same thing.

You needed to "derive" something from the SET of data to be used in the final query. The HIGHEST BID AMOUNT is not naturally part of the set of data in the BID table - BID AMOUNTS are - low ones, middle ones and high ones. But the HIGHEST - that specific amount - must be derived - by some combo of GROUP BY (if doing a VIEW) or MAX with WHERE a SINGLE ITEM...

Lots of words there - sorry...

But when you start thinking of your data and tables as sets it makes conceptualizing a query much easier...

...why do I need to uses aliases when I query the same table twice?

If you did not alias then it would not know what field to get from what "table" - you are using the same table twice. That introduces an ambiguity that must somehow be resolved.

Let's say you had an EMPLOYEE table and in that table you also stored the BOSSID of the employee who is your boss.

Select E1.EmpName,E2.EmpName "BossName"
   From Employee E1
   Left Join Employee E2 on E2.EmpId=E1.BossId

So here I am showing the EMPNAME and then joining back to the same table on the BOSSID value...

And I want to show the EMPNAME of that Boss. How else could you do that without an ALIAS like E1 and E2. You cannot use Employee.EmpName - there are "two" employee tables in that query.

[bsw2112]

Hello szlamany

I really appreciate all in recent weeks! I learned a lot. I also went to Chapters (a big book store in Canada) and read up on SQL and discovered what you were trying to get through my thick scull. The book called them self-joins and went on to explain when to use them etc..

In any case thanks again and I hope I can be of help to you in the future. My areas of expertise are accessibility and usability.

Have a good one!

bsw