473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL Problem - Auction

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
6 1701
-----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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Greg Bryant | last post by:
I've been looking for an auction script to use as a starting point in a rewrite of a coldfusion auction site. The site is a member's only auction, where different categories of *users* may not be...
0
by: Auction software | last post by:
Free download full version , all products http://netauction8.url4life.com/ Groupawy --------------- Google Groups Email spider. The first email spider for google groups. Millions of valid...
0
by: Auction software | last post by:
Free download full version , all products from Mewsoft dot com http://netauction8.url4life.com/ Groupawy --------------- Google Groups Email spider. The first email spider for google groups....
1
by: Davef | last post by:
I have to make a Auction progam for a client. Does anyone know some free basic code to one to get me started? -- ______________________ David Fetrow HelixPoint LLC....
0
by: Ravi | last post by:
Hi, I am currently working on a web auctioning project using .NET technologies as part of my University dissertation. I have gathered a fair amount of information through my research on the...
0
by: Ravi Puri | last post by:
Hi, I am currently working on a web auctioning project using .NET technologies as part of my University dissertation. I have gathered a fair amount of information through my research on the...
2
by: jon121970 | last post by:
I'm new here.. I am hoping to get some Professional Advice. I want to start my own online Auction. I anticipate it will grow into a high volume/high traffic online auction. I need some...
0
by: ajauctionpro | last post by:
AJ Auction software is the latest and most advanced Auction Script to set up an online auction site with excellent navigation features. They help you build your own online Auction. Auction Script...
0
by: ajauctionpro | last post by:
AJ Auction software is the latest and most advanced Auction Script to set up an online auction site with excellent navigation features. They help you build your own online Auction. Auction Script...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.