query to support lowest bid

[gsc123]

I'm moving to SQL Server 08 soon so I'd like to drop this problem here...

Hello,

I have a problem with this query

SELECT Min(bidAmount) AS price FROM BidHistory WHERE (((BidHistory.bidAmount) In (SELECT Min(BidAmount) FROM BidHistory WHERE BidAmount<10009999 and idproduct = 1560)))

It will always select min bid from the bidhistory table, the problem is, it will always give me the same value, if lowest bid is 210 it will always be 210, even though 2 bidders have bid more if you understand what I mean, if there are 2 bidders bidding then it should give me the min bid that has been bid on with that idproduct code....


so its higher bid from bids of many users but still lowest if the other bidders have not reached the proxy bid in the bidhistory table

 

[bborissov]

If you post some example data and desired result from it it will be easier for me to understand what you want.
And of course SQL Server will always return 210 as MIN() this is the minimum, isn't it?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gsc123]

Some example data it looks through....

the result I need his draw below - these are not figures from db just making them up - in increments of 10

1500
510
220
bidder1 bidder2 bidder3

so next bid should show 520


id idCustomerBid idProduct bidAmount bidDate
2 15 1559 £300.00 19/05/2009 21:05:48
44 15 1560 £210.00 20/05/2009 22:44:08
45 15 1559 £940.00 20/05/2009 22:52:56
46 15 1559 £940.00 20/05/2009 22:53:05
47 15 1559 £940.00 20/05/2009 22:53:16
48 15 1559 £940.00 20/05/2009 22:53:35
49 15 1559 £950.00 20/05/2009 22:53:35
50 31 1559 £1,950.00 20/05/2009 22:53:35
59 15 1559 £1,040.00 20/05/2009 23:06:40
60 15 1559 £1,050.00 20/05/2009 23:08:16
61 15 1559 £1,060.00 20/05/2009 23:08:46
62 15 1559 £1,070.00 20/05/2009 23:10:00
63 15 1559 £1,080.00 20/05/2009 23:10:06
64 15 1559 £1,090.00 20/05/2009 23:10:08
65 15 1559 £1,020.00 20/05/2009 23:10:15
66 15 1559 £1,030.00 20/05/2009 23:10:16
67 15 1559 £1,040.00 20/05/2009 23:10:17
68 15 1560 £220.00 20/05/2009 23:11:46
69 15 1560 £230.00 20/05/2009 23:11:55
70 15 1560 £240.00 20/05/2009 23:11:57
71 15 1560 £250.00 20/05/2009 23:11:59
72 15 1560 £260.00 20/05/2009 23:15:35
73 15 1561 £350.00 20/05/2009 23:15:52
74 15 1561 £360.00 20/05/2009 23:16:53
75 15 1561 £370.00 20/05/2009 23:16:56
76 15 1559 £1,020.00 20/05/2009 23:17:05
77 15 1560 £220.00 20/05/2009 23:17:16
78 15 1560 £230.00 20/05/2009 23:17:30
79 15 1560 £220.00 20/05/2009 23:17:44
80 15 1560 £230.00 20/05/2009 23:18:04
81 15 1560 £240.00 20/05/2009 23:20:07
82 15 1560 £240.00 20/05/2009 23:21:56
83 31 1562 £320.00 20/05/2009 23:23:22
104 15 1560 £240.00 20/05/2009 23:37:13
105 15 1560 £250.00 20/05/2009 23:38:02
106 15 1560 £260.00 20/05/2009 23:38:04
107 15 1560 £270.00 20/05/2009 23:39:16
108 15 1560 £280.00 20/05/2009 23:39:21
109 15 1559 £1,020.00 20/05/2009 23:39:44
110 1481 1559 £1,030.00 21/05/2009 09:36:47
111 1481 1559 £1,040.00 21/05/2009 09:56:40
112 15 1559 £1,050.00 21/05/2009 10:28:08
113 1481 1559 £1,060.00 21/05/2009 11:36:06
114 1481 1559 £1,070.00 21/05/2009 21:28:18
115 1481 1564 £250.00 21/05/2009 21:35:38
116 1481 1564 £260.00 06/02/2009 13:54:43
117 1481 1564 £270.00 06/02/2009 13:54:51
118 15 1559 £1,080.00 06/03/2009 22:08:22
119 15 1559 £1,090.00 06/03/2009 22:08:30
120 15 1563 £310.00 06/03/2009 22:11:19
121 15 1559 £1,100.00 06/03/2009 22:20:11
122 15 1559 £1,110.00 06/03/2009 22:20:18
123 15 1563 £320.00 06/03/2009 22:20:42
124 15 1563 £330.00 06/03/2009 22:20:52
125 15 1559 £1,120.00 06/03/2009 22:29:38
126 15 1559 £1,130.00 06/03/2009 22:30:35
127 15 1559 £1,140.00 06/03/2009 23:20:18
128 15 1563 £340.00 06/03/2009 23:20:48
129 15 1563 £350.00 06/03/2009 23:22:59
130 15 1559 £1,150.00 06/03/2009 23:24:53
131 15 1559 £1,160.00 06/03/2009 23:28:50
132 15 1559 £1,170.00 06/03/2009 23:29:14
133 15 1559 £1,180.00 06/03/2009 23:55:21
134 15 1564 £280.00 06/05/2009 09:29:25
135 15 1564 £290.00 06/05/2009 09:29:31
136 1489 1561 £350.00 06/09/2009 21:43:46
137 15 1562 £220.00 06/10/2009 09:52:42
138 15 1562 £230.00 06/10/2009 10:11:06
139 15 1562 £240.00 06/10/2009 10:11:16
140 15 1560 £290.00 06/10/2009 10:54:51
141 15 1560 £290.00 06/10/2009 10:55:59
142 15 1560 £220.00 14/06/2009 07:24:44

 

[gsc123]

also the aboth is on all the same idproduct ...

I have just had a thought and would the result not be 1 below max on the same id?

 

[gsc123]

ie:

SELECT TOP 2 bidhistory.bidAmount, bidhistory.idProduct FROM bidhistory WHERE (((bidhistory.idProduct)=1559)) ORDER BY bidhistory.bidAmount DESC;

 

[bborissov]

And GROUP BY what?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gsc123]

do I need to group?

 

[bborissov]

From the data you provide the MIN bid for 1559 is 300

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

For 2005 and 2008 only:

-- Preparing Test data, you don't need this
DECLARE @Test TABLE(id  int, idCustomerBid int, idProduct int, bidAmount numeric(10,2))
INSERT INTO @Test VALUES(2 ,15,1559,300.00)
INSERT INTO @Test VALUES(44,15,1560,210.00)
INSERT INTO @Test VALUES(45,15,1559,940.00)
INSERT INTO @Test VALUES(46,15,1559,940.00)
INSERT INTO @Test VALUES(47,15,1559,940.00)
INSERT INTO @Test VALUES(48,15,1559,940.00)
INSERT INTO @Test VALUES(49,15,1559,950.00)
INSERT INTO @Test VALUES(50,31,1559,1950.00)
INSERT INTO @Test VALUES(59,15,1559,1040.00)
INSERT INTO @Test VALUES(60,15,1559,1050.00)
INSERT INTO @Test VALUES(61,15,1559,1060.00)
INSERT INTO @Test VALUES(62,15,1559,1070.00)
INSERT INTO @Test VALUES(63,15,1559,1080.00)
INSERT INTO @Test VALUES(64,15,1559,1090.00)
INSERT INTO @Test VALUES(65,15,1559,1020.00)
INSERT INTO @Test VALUES(66,15,1559,1030.00)
INSERT INTO @Test VALUES(67,15,1559,1040.00)
INSERT INTO @Test VALUES(68,15,1560,220.00)
INSERT INTO @Test VALUES(69,15,1560,230.00)
INSERT INTO @Test VALUES(70,15,1560,240.00)
INSERT INTO @Test VALUES(71,15,1560,250.00)
INSERT INTO @Test VALUES(72,15,1560,260.00)
INSERT INTO @Test VALUES(73,15,1561,350.00)
INSERT INTO @Test VALUES(74,15,1561,360.00)
INSERT INTO @Test VALUES(75,15,1561,370.00)
INSERT INTO @Test VALUES(76,15,1559,1020.00)
INSERT INTO @Test VALUES(77,15,1560,220.00)
INSERT INTO @Test VALUES(78,15,1560,230.00)
INSERT INTO @Test VALUES(79,15,1560,220.00)
INSERT INTO @Test VALUES(80,15,1560,230.00)
INSERT INTO @Test VALUES(81,15,1560,240.00)
INSERT INTO @Test VALUES(82,15,1560,240.00)
INSERT INTO @Test VALUES(83,31,1562,320.00)
INSERT INTO @Test VALUES(104,15,1560,240.00)
INSERT INTO @Test VALUES(105,15,1560,250.00)
INSERT INTO @Test VALUES(106,15,1560,260.00)
INSERT INTO @Test VALUES(107,15,1560,270.00)
INSERT INTO @Test VALUES(108,15,1560,280.00)
INSERT INTO @Test VALUES(109,15,1559,1020.00)
INSERT INTO @Test VALUES(110,1481,1559,1030.00)
INSERT INTO @Test VALUES(111,1481,1559,1040.00)
INSERT INTO @Test VALUES(112,15,1559,1050.00)
INSERT INTO @Test VALUES(113,1481,1559,1060.00)
INSERT INTO @Test VALUES(114,1481,1559,1070.00)
INSERT INTO @Test VALUES(115,1481,1564,250.00)
INSERT INTO @Test VALUES(116,1481,1564,260.00)
INSERT INTO @Test VALUES(117,1481,1564,270.00)
INSERT INTO @Test VALUES(118,15,1559  ,1080.00)
INSERT INTO @Test VALUES(119,15,1559,1090.00)
INSERT INTO @Test VALUES(120,15,1563,310.00)
INSERT INTO @Test VALUES(121,15,1559,1100.00)
INSERT INTO @Test VALUES(122,15,1559,1110.00)
INSERT INTO @Test VALUES(123,15,1563,320.00)
INSERT INTO @Test VALUES(124,15,1563,330.00)
INSERT INTO @Test VALUES(125,15,1559,1120.00)
INSERT INTO @Test VALUES(126,15,1559,1130.00)
INSERT INTO @Test VALUES(127,15,1559,1140.00)
INSERT INTO @Test VALUES(128,15,1563,340.00)
INSERT INTO @Test VALUES(129,15,1563,350.00)
INSERT INTO @Test VALUES(130,15,1559,1150.00)
INSERT INTO @Test VALUES(131,15,1559,1160.00)
INSERT INTO @Test VALUES(132,15,1559,1170.00)
INSERT INTO @Test VALUES(133,15,1559,1180.00)
INSERT INTO @Test VALUES(134,15,1564,280.00)
INSERT INTO @Test VALUES(135,15,1564,290.00)
INSERT INTO @Test VALUES(136,1489,1561,350.00)
INSERT INTO @Test VALUES(137,15,1562,220.00)
INSERT INTO @Test VALUES(138,15,1562,230.00)
INSERT INTO @Test VALUES(139,15,1562,240.00)
INSERT INTO @Test VALUES(140,15,1560,290.00)
INSERT INTO @Test VALUES(141,15,1560,290.00)
INSERT INTO @Test VALUES(142,15,1560,220.00)
--- End

-- The query, you need to change @Test to your actual table name
SELECT  idCustomerBid ,bidAmount
FROM (SELECT *, ROW_NUMBER()OVER (PARTITION BY idCustomerBid ORDER BY bidAmount) AS Row FROM @Test) Tst
WHERE idProduct = 1560 AND Row = 2

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

Sorry, the query should be:

SELECT  idProduct ,bidAmount, Row
FROM (SELECT *, ROW_NUMBER()OVER (PARTITION BY idProduct ORDER BY idProduct, bidAmount) AS Row FROM @Test) Tst
WHERE idProduct = 1560 AND Row = 2

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gsc123]

I have'nt switched over to SERVER yet, is this possible to convert to access query or not? or is access not supportable for this type of query?

 

[bborissov]

I have no idea about Access.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.