SQL Genius needed - positioning query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Genius needed - positioning query

Author

Topic

RichardEttinger
Starting Member

7 Posts

Posted - 2004-02-20 : 13:01:37

We are building an advertising system based on auctions where customers can bid on positions. The highest bidder for a position should get that position..

The table (simplified for this post) is called Ads
with this query (below)

select a1.AdId, a1.Originalpos, a1.pos, a1.currentbid, a1.listingDate
from ads a1
order by currentbid desc, listingdate desc

rreturns this data

AdId Originalpos pos currentbid listingDate
----------- ----------- ---- --------------------- ---------------------------
2 2 2 .1000 2002-01-30 00:00:00
8 8 8 .1000 2002-01-30 00:00:00
19 5 5 .1000 2002-01-30 00:00:00
25 1 1 .1000 2002-01-30 00:00:00
4 4 4 .0800 2002-01-30 00:00:00
14 4 4 .0800 2002-01-30 00:00:00
16 2 2 .0700 2002-01-30 00:00:00
23 9 9 .0700 2002-01-30 00:00:00
35 2 2 .0600 2004-02-10 08:47:00
34 2 2 .0600 2004-02-10 00:00:00
17 3 3 .0600 2002-01-30 00:00:00
1 15 15 .0500 2002-01-30 00:00:00
6 6 6 .0500 2002-01-30 00:00:00
13 1 1 .0500 2002-01-30 00:00:00
9 9 9 .0500 2002-01-30 00:00:00
10 10 10 .0500 2002-01-30 00:00:00
18 4 4 .0500 2002-01-30 00:00:00
20 6 6 .0500 2002-01-30 00:00:00
24 10 10 .0500 2002-01-30 00:00:00
26 8 8 .0500 2002-01-30 00:00:00
29 1 1 .0500 2002-01-30 00:00:00
30 1 1 .0500 2002-01-30 00:00:00
7 7 7 .0400 2002-01-30 00:00:00
15 1 1 .0400 2002-01-30 00:00:00
11 2 2 .0400 2002-01-30 00:00:00
22 8 8 .0400 2002-01-30 00:00:00
28 1 1 .0400 2002-01-30 00:00:00
31 2 2 .0400 2002-01-30 00:00:00
3 3 3 .0300 2002-01-30 00:00:00
5 5 5 .0300 2002-01-30 00:00:00
21 7 7 .0300 2002-01-30 00:00:00
32 3 3 .0300 2002-01-30 00:00:00
33 4 4 .0200 2002-01-30 00:00:00
27 NULL NULL .0100 2002-01-30 00:00:00

What we need to do is return either the Original Position (which was the Bidders first choice) or the next available position which has not yet been taken by a higher bidder) in a column named NewPos (so we can update the pos from this select query)

I would like to avoid a cursor, unless someone can convince me it would be faster..

I think we need somekind of correlated subquery with a case statement which checks to see if a2.currentbid<a1.currentbid and a2.listingdate<a1.listingdate..

I just can't seem to figure this one out....

Thank you all so much for any help with this query...

Richard Ettinger

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-20 : 13:04:32

Please post the DDL for your table (CREATE TABLE statement) and post the data in the form of INSERT INTO statements. Without this information, we have to do all of the typing. So it makes it easier if the poster does the leg-work. You'll find that your question is answered quickly once we have the needed information. Also, show us what you want the result set to look like.

Tara

RichardEttinger
Starting Member

7 Posts

Posted - 2004-02-20 : 15:04:22

Sorry Tara, I should have done that.. Also I apologize for the formatting of the return results, when I put it in it appeared fine, but in reviwing the post the data got smashed together..

The Create Table statement is

CREATE TABLE [dbo].[AdTest] (
[AdId] [int] NOT NULL ,
[OriginalPos] [tinyint] NULL ,
[Pos] [tinyint] NULL ,
[CurrentBid] [money] NULL ,
[ListingDate] [datetime] NULL ,

) ON [PRIMARY]
GO

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(2,2,2,.1000,'2002-01-30 00:00:00')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(8,2,2,.1000,'2002-01-30')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(19,5,5,.1000,'2002-01-30 00:00:00')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(25,1,1,.10,'2002-01-30')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(4,4,4,.08,'2002-01-30')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(14,4,4,.08,'2002-01-28')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(16,2,2,.07,'2002-01-29')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(23,3,3,.07,'2002-02-1')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(35,2,2,.06,'2002-02-1')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(34,2,2,.06,'2002-02-2')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(17,3,3,.06,'2002-02-1')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(1,15,15,.05,'2002-02-3')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(6,6,6,.05,'2002-02-3')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(13,1,1,.05,'2002-02-3')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(9,1,1,.05,'2002-02-3')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(10,2,2,.05,'2002-02-3')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(18,2,2,.06,'2002-02-3')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(20,1,1,.06,'2002-02-3')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(24,3,3,.05,'2002-02-3')

Insert Into AdTest
(AdId, OriginalPos, pos, currentbid,listingdate)
values
(26,1,1,.05,'2002-02-3')

Data should ORIGINALLY look soething like:

AdId____Originalpos_______pos_______currentbid_______listingDate

8_______2_______2_______.1000_______2002-01-28 00:00:00.000
2_______2_______2_______.1000_______2002-01-30 00:00:00.000
19______5_______5_______.1000_______2002-01-30 00:00:00.000
25______1_______1_______.1000_______2002-01-30 00:00:00.000
14______4_______4_______.0800_______2002-01-28 00:00:00.000
4_______4_______4_______.0800_______2002-01-30 00:00:00.000
16______2 ______2_______.0700_______2002-01-29 00:00:00.000
23______9_______9_______.0700_______2002-02-01 00:00:00.000
35______2_______2_______.0600_______2002-02-01 00:00:00.000
34______2_______2_______.0600_______2002-02-01 00:00:00.000
17______3_______3_______.0600_______2002-02-01 00:00:00.000
18______2_______2_______.0600_______2002-02-03 00:00:00.000
1_______15______15______.0500_______2002-02-03 00:00:00.000
6_______6_______6_______.0500_______2002-02-03 00:00:00.000
13______1 ______1_______.0500_______2002-02-03 00:00:00.000
9_______1_______1_______.0500_______2002-02-03 00:00:00.000
10______2_______2_______.0500_______2002-02-03 00:00:00.000

Pos is the position the ad shows up in on our site. So pos 1 would be the top position of an ad, pos 2 would be the next position, pos 3 the next position, etc, etc, etc... We allow bidders to choose their position... But the hghest bidder for a pos (position) gets their choice and the next lower bidder gets the next sequential position of value)

Ideally what we would like is to UPDATE pos (which is the position 1 through x) to a unique pos
giving the highest bidders their choice for their Originalpos which is their first choice.. If the pos is already taken by the highest bidder it should then become the next available sequential position of value.

The Ideal Output Table would be:

AdId____Originalpos_______pos_______currentbid_______listingDate

8_______2_______1_______.1000_______2002-01-28 00:00:00.000
2_______2_______2_______.1000_______2002-01-30 00:00:00.000
19______5_______5_______.1000_______2002-01-30 00:00:00.000
25______1_______3_______.1000_______2002-01-30 00:00:00.000
14______4_______4_______.0800_______2002-01-28 00:00:00.000
4_______4_______6_______.0800_______2002-01-30 00:00:00.000
16______2 ______7_______.0700_______2002-01-29 00:00:00.000
23______9_______8_______.0700_______2002-02-01 00:00:00.000
35______2_______9_______.0600_______2002-02-01 00:00:00.000
34______2_______10_______.0600_______2002-02-01 00:00:00.000
17______3_______11_______.0600_______2002-02-01 00:00:00.000
18______2_______12_______.0600_______2002-02-03 00:00:00.000
1_______15______13______.0500_______2002-02-03 00:00:00.000
6_______6_______14_______.0500_______2002-02-03 00:00:00.000
13______1 ______15_______.0500_______2002-02-03 00:00:00.000
9_______1_______16_______.0500_______2002-02-03 00:00:00.000
10______2_______17_______.0500_______2002-02-03 00:00:00.000

I know this is a tough one..... Its gotta perform fairly well as well..

Thank you agin in advance...

Richard :-)

RichardEttinger
Starting Member

7 Posts

Posted - 2004-02-20 : 15:20:45

If it makes the T-sql statement easier, the below output is also acceptable:

Thethe high bidder could keep his position, if he is the high bidder for that position in the case of AdId 1 and 23 below..

In this output the ads were not reassigned a new value..
either way is acceptable, especially if one will perform better..

AdId____Originalpos_______pos_______currentbid_______listingDate

8_______2_______1_______.1000_______2002-01-28 00:00:00.000
2_______2_______2_______.1000_______2002-01-30 00:00:00.000
19______5_______5_______.1000_______2002-01-30 00:00:00.000
25______1_______3_______.1000_______2002-01-30 00:00:00.000
14______4_______4_______.0800_______2002-01-28 00:00:00.000
4_______4_______6_______.0800_______2002-01-30 00:00:00.000
16______2 ______7_______.0700_______2002-01-29 00:00:00.000
23______9_______9_______.0700_______2002-02-01 00:00:00.000
35______2_______8_______.0600_______2002-02-01 00:00:00.000
34______2_______10_______.0600_______2002-02-01 00:00:00.000
17______3_______11_______.0600_______2002-02-01 00:00:00.000
18______2_______12_______.0600_______2002-02-03 00:00:00.000
1_______15______15______.0500_______2002-02-03 00:00:00.000
6_______6_______13_______.0500_______2002-02-03 00:00:00.000
13______1 ______14_______.0500_______2002-02-03 00:00:00.000
9_______1_______15_______.0500_______2002-02-03 00:00:00.000
10______2_______16_______.0500_______2002-02-03 00:00:00.000

This is the toughest SQL problem I have ever had...

Thank you in advance

Richard

jbkayne
Posting Yak Master

100 Posts

Posted - 2004-02-20 : 20:24:24

Here is a good start. How do you want to handle ties? Also can you explain listingDate?

select
a1.AdId
, a1.Originalpos
, a1.pos
, a1.currentbid
, a1.listingDate
, (select count(*) from adtest where currentbid >= a1.currentbid) as posgroup
into #temptable
from
adtest a1

select
temptbl.AdId
, temptbl.Originalpos
, temptbl.pos
, temptbl.currentbid
, temptbl.listingDate
, posgroup
,(select count(*) from #temptable as temptbl2 where posgroup = temptbl.posgroup and originalpos <= temptbl.originalpos) + isnull((select top 1 posgroup from #temptable where posgroup = (select max(posgroup) from #temptable where posgroup < temptbl.posgroup)),0) as newpos
from
#temptable as temptbl
order by newpos

drop table #temptable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources