By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,493 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

SQL Problem - Auction

P: n/a
Hello,

Similar to my last post I have another SQL problem. If you are running
an auction and you have the following fields;

lot_no
bidder_no
bid
bid_time
bid_date

How do you write an SQL for this if you want ONLY the highest bid
entered at the earliest date and time with the bidder number. I can get
it without the bidder number, but as soon as I add it in I get all the
records again. How do you filter out the ones you do not want. Here
is the code I have that works without the bidder number;

SELECT Min(bid_date) AS MinOfbid_date, Min(bid_time) AS MinOfbid_time,
lot_no, Max(bid) AS MaxOfbid
FROM bids
GROUP BY lot_no;

When I add the bidder number like this;

SELECT Min(bid_date) AS MinOfbid_date, Min(bid_time) AS MinOfbid_time,
lot_no, Max(bid) AS MaxOfbid, bidder_id
FROM bids
GROUP BY lot_no, bidder_id;

I get all the records again. The final output should be a five column
table - date, time, lot, bid, bidder.

Any assistance is greatly appreciated.

worldbadger

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT TOP 1 lot_no, bid, bidder_id, bid_date, bid_time
FROM bids
ORDER BY bid DESC, bid_date ASC, bid_time ASC

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQldTpIechKqOuFEgEQLnGACghdbtiTnmmoWkZgZrL2iA0Q r/6QEAoMtT
KsUaLx0dK3jPnpi7+vOa1q7n
=vFq4
-----END PGP SIGNATURE-----
worldbadger wrote:
Hello,

Similar to my last post I have another SQL problem. If you are running
an auction and you have the following fields;

lot_no
bidder_no
bid
bid_time
bid_date

How do you write an SQL for this if you want ONLY the highest bid
entered at the earliest date and time with the bidder number. I can get
it without the bidder number, but as soon as I add it in I get all the
records again. How do you filter out the ones you do not want. Here
is the code I have that works without the bidder number;

SELECT Min(bid_date) AS MinOfbid_date, Min(bid_time) AS MinOfbid_time,
lot_no, Max(bid) AS MaxOfbid
FROM bids
GROUP BY lot_no;

When I add the bidder number like this;

SELECT Min(bid_date) AS MinOfbid_date, Min(bid_time) AS MinOfbid_time,
lot_no, Max(bid) AS MaxOfbid, bidder_id
FROM bids
GROUP BY lot_no, bidder_id;

I get all the records again. The final output should be a five column
table - date, time, lot, bid, bidder.

Nov 13 '05 #2

P: n/a
Oops...once again I find myself not fully explaining the situation. I
wanted the top bid for each lot number, not just for the entire
auction. There are about 1700 lots.

Although I know this does not work I want something like:

WHERE max(bid) per lot (if tied then earliest bid given) include bidder
number

Please help.

worldbadger

Nov 13 '05 #3

P: n/a
worldbadger wrote:
Oops...once again I find myself not fully explaining the situation. I
wanted the top bid for each lot number, not just for the entire
auction. There are about 1700 lots.

Although I know this does not work I want something like:

WHERE max(bid) per lot (if tied then earliest bid given) include bidder
number


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT lot_no, bid, bidder_id, bid_date, bid_time
FROM bids As B
WHERE bidder_id IN
(SELECT TOP 1 bidder_id
FROM bids
WHERE lot_no = B.lot_no
AND bid_date = B.bid_date
ORDER BY bid DESC, bid_date ASC, bid_time ASC)

If there are ties (same bid at same date/time) the SELECT TOP 1 will
arbitrarily choose one of the tie'ing bidders. This won't happen if the
bid_time is kept in seconds 'cuz the probablity of 2 bidders making same
bid at exact same second is very low.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlelt4echKqOuFEgEQKa6gCgqnV3NSmCpqm/ijapFDMg+tkQ/e0An0Wc
Aydhekmx0X/QTtM+4q5Mui/Z
=P3W2
-----END PGP SIGNATURE-----
Nov 13 '05 #4

P: n/a
WOW.....you can probably tell I am a real amateur at this.

I made change of bidder_id to bid in the WHERE to get rid of duplicate
bids;

SELECT lot_no, bid, bidder_id, bid_date, bid_time
FROM bids As B
WHERE bid IN
(SELECT TOP 1 bid
FROM bids
WHERE lot_no = B.lot_no
AND bid_date = B.bid_date
ORDER BY bid DESC, bid_date ASC, bid_time ASC)

but I am still getting tie bids both showing up (I have a small table
with a couple of tie bids but different dates and times). How do I get
rid of the tie bids and only show the earliest entry (time is collected
by date and HH.MM.SS).

Your assistance with this is greatly appreciated.

Nov 13 '05 #5

P: n/a
worldbadger wrote:
WOW.....you can probably tell I am a real amateur at this.

I made change of bidder_id to bid in the WHERE to get rid of duplicate
bids;

SELECT lot_no, bid, bidder_id, bid_date, bid_time
FROM bids As B
WHERE bid IN
(SELECT TOP 1 bid
FROM bids
WHERE lot_no = B.lot_no
AND bid_date = B.bid_date
ORDER BY bid DESC, bid_date ASC, bid_time ASC)

but I am still getting tie bids both showing up (I have a small table
with a couple of tie bids but different dates and times). How do I get
rid of the tie bids and only show the earliest entry (time is collected
by date and HH.MM.SS).


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about this:

SELECT lot_no, bid, bidder_id, bid_date, bid_time
FROM bids As B
WHERE bidder_id IN
(SELECT TOP 1 bidder_id
FROM bids
WHERE auction_id = B.auction_id
AND lot_no = B.lot_no
ORDER BY bid DESC, bid_date ASC, bid_time ASC)

I should have used the bidder_id instead of the bid ($) - changed that.
I got rid of the bid_date criteria in the subquery just in case the
auction runs over more than one day. If there is an auction_id in table
bids you will want to put that criteria in the subquery's WHERE clause:

WHERE auction_id = B.auction_id

I say this 'cuz I'm assuming lot_nos are only significant per auction.
IOW,

Auction 1 Lots 1 thru 500
Auction 2 Lots 1 thru 30

Both auction 1 & 2 have lots 1-30. The significance of Lot 25 is only
known by association w/ it's auction number.

If you're still getting ties, make sure the time is stored as a datetime
data type. If it is stored as a string, make sure it is formatted in 24
hour style. E.g., 11pm = 23:00:00. This is so it will sort correctly.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQliWPIechKqOuFEgEQIOZgCfW2b4Q9Vc4rnYgVnWhEc6ff Yx9aoAn3W3
JcXVGgBzQVLiPrztEwgYKe3m
=r4df
-----END PGP SIGNATURE-----
Nov 13 '05 #6

P: n/a
Magnificent....it worked like a charm. Many thanks for all your help.
I think I need to learn more about querys.

worldbadger

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.