Get Top Bidder for each product based on ID sequence (First IN; First Priority win the item - first IN; top price win the item), 1 user only allow win 1 item

Transact-SQL
1

Hi All,

I have this:

Declare @orders table(
ID int,
UserName nvarchar(50),
Code nvarchar(50),
BidPrice float
)

insert into @orders
select ID= 176 ,UserName='AW',Code='D014',BidPrice='110' union
select ID= 151 ,UserName='EK',Code='D014',BidPrice='100' union
select ID= 152 ,UserName='GY',Code='L011',BidPrice='178' union
select ID= 153 ,UserName='RZ',Code='L011',BidPrice='245' union
select ID= 154 ,UserName='RZ',Code='D008',BidPrice='60' union
select ID= 155 ,UserName='RZ',Code='L012',BidPrice='240' union
select ID= 156 ,UserName='EK',Code='D010',BidPrice='200' union
select ID= 167 ,UserName='GY',Code='L014',BidPrice='178' union
select ID= 168 ,UserName='DT',Code='L005',BidPrice='101' union
select ID= 169 ,UserName='MS',Code='D014',BidPrice='120' union
select ID= 160 ,UserName='EK',Code='D015',BidPrice='200' union
select ID= 170 ,UserName='MS',Code='D015',BidPrice='120' union
select ID= 171 ,UserName='MS',Code='D018',BidPrice='120' union
select ID= 172 ,UserName='MS',Code='D019',BidPrice='120' union
select ID= 173 ,UserName='MS',Code='D020',BidPrice='120' union
select ID= 174 ,UserName='MS',Code='D011',BidPrice='38' union
select ID= 175 ,UserName='MS',Code='D007',BidPrice='20' union
select ID= 177 ,UserName='AW',Code='D015',BidPrice='110' union
select ID= 178 ,UserName='AW',Code='D018',BidPrice='120' union
select ID= 179 ,UserName='YCL',Code='D018',BidPrice='122' union
select ID= 180 ,UserName='YCL',Code='L001',BidPrice='119' union
select ID= 181 ,UserName='YCL',Code='L006',BidPrice='111' union
select ID= 182 ,UserName='YCL',Code='L011',BidPrice='52' union
select ID= 183 ,UserName='YCL',Code='L012',BidPrice='52' union
select ID= 184 ,UserName='DT',Code='L011',BidPrice='55' union
select ID= 185 ,UserName='SM',Code='L014',BidPrice='100' union
select ID= 186 ,UserName='AT',Code='L012',BidPrice='52' union
select ID= 187 ,UserName='STM',Code='L002',BidPrice='60' union
select ID= 188 ,UserName='STM',Code='L009',BidPrice='60' union
select ID= 189 ,UserName='STM',Code='D013',BidPrice='50' union
select ID= 190 ,UserName='STM',Code='D019',BidPrice='120' union
select ID= 191 ,UserName='STM',Code='D016',BidPrice='35' union
select ID= 192 ,UserName='STM',Code='D012',BidPrice='35' union
select ID= 193 ,UserName='STM',Code='D011',BidPrice='35' union
select ID= 194 ,UserName='STM',Code='D003',BidPrice='18' union
select ID= 195 ,UserName='EK',Code='D009',BidPrice='275' union
select ID= 196 ,UserName='AZN',Code='D018',BidPrice='120' union
select ID= 197 ,UserName='AZN',Code='D020',BidPrice='120' union
select ID= 198 ,UserName='AZN',Code='D014',BidPrice='120' union
select ID= 199 ,UserName='AZN',Code='D017',BidPrice='120' union
select ID= 200 ,UserName='AZN',Code='L011',BidPrice='88' union
select ID= 201 ,UserName='AZN',Code='D015',BidPrice='110' union
select ID= 202 ,UserName='AZN',Code='D019',BidPrice='110' union
select ID= 203 ,UserName='EK',Code='D018',BidPrice='275' union
select ID= 204 ,UserName='SM',Code='L011',BidPrice='555' union
select ID= 205 ,UserName='EK',Code='D019',BidPrice='275' union
select ID= 206 ,UserName='EK',Code='D020',BidPrice='275' union
select ID= 207 ,UserName='AT',Code='L011',BidPrice='55' union
select ID= 208 ,UserName='SM',Code='L005',BidPrice='335' union
select ID= 209 ,UserName='RC',Code='D017',BidPrice='123' union
select ID= 210 ,UserName='RC',Code='D015',BidPrice='108' union
select ID= 211 ,UserName='TQT',Code='D002',BidPrice='100' union
select ID= 212 ,UserName='TQT',Code='D001',BidPrice='101' union
select ID= 213 ,UserName='AT',Code='D014',BidPrice='110' union
select ID= 214 ,UserName='IBS',Code='L001',BidPrice='400' union
select ID= 215 ,UserName='IBS',Code='L005',BidPrice='400' union
select ID= 216 ,UserName='IBS',Code='L014',BidPrice='350' union
select ID= 217 ,UserName='IBS',Code='L011',BidPrice='300' union
select ID= 218 ,UserName='SM',Code='L001',BidPrice='360' union
select ID= 219 ,UserName='SM',Code='L006',BidPrice='489' union
select ID= 220 ,UserName='SM',Code='L003',BidPrice='388' union
select ID= 221 ,UserName='SM',Code='L012',BidPrice='360' union
select ID= 222 ,UserName='SM',Code='L010',BidPrice='268' union
select ID= 223 ,UserName='SM',Code='L007',BidPrice='259' union
select ID= 224 ,UserName='SM',Code='L013',BidPrice='220' union
select ID= 225 ,UserName='SM',Code='L008',BidPrice='220' union
select ID= 226 ,UserName='SM',Code='L009',BidPrice='220' union
select ID= 227 ,UserName='SM',Code='L015',BidPrice='200' union
select ID= 228 ,UserName='SM',Code='D014',BidPrice='555' union
select ID= 229 ,UserName='SM',Code='D015',BidPrice='559' union
select ID= 230 ,UserName='SM',Code='D018',BidPrice='558' union
select ID= 231 ,UserName='SM',Code='D020',BidPrice='557' union
select ID= 236 ,UserName='JB',Code='L014',BidPrice='120' union
select ID= 237 ,UserName='JB',Code='D010',BidPrice='60' union
select ID= 238 ,UserName='JB',Code='D014',BidPrice='130' union
select ID= 239 ,UserName='JB',Code='D001',BidPrice='65' union
select ID= 240 ,UserName='AW',Code='D019',BidPrice='132' union
select ID= 241 ,UserName='AW',Code='D020',BidPrice='132' union
select ID= 242 ,UserName='COL',Code='L011',BidPrice='101' union
select ID= 243 ,UserName='LZO',Code='L005',BidPrice='150' union
select ID= 244 ,UserName='LZO',Code='L011',BidPrice='100' union
select ID= 245 ,UserName='LZO',Code='L012',BidPrice='100' union
select ID= 246 ,UserName='LZO',Code='L006',BidPrice='150' union
select ID= 247 ,UserName='LZO',Code='L014',BidPrice='150' union
select ID= 248 ,UserName='LZO',Code='D018',BidPrice='150' union
select ID= 249 ,UserName='LZO',Code='D015',BidPrice='150' union
select ID= 250 ,UserName='LZO',Code='D014',BidPrice='150' union
select ID= 251 ,UserName='HMC',Code='L011',BidPrice='350' union
select ID= 252 ,UserName='HMC',Code='L012',BidPrice='550' union
select ID= 263 ,UserName='AMNG',Code='D017',BidPrice='252' union
select ID= 264 ,UserName='AMNG',Code='L011',BidPrice='81' union
select ID= 265 ,UserName='AMNG',Code='L014',BidPrice='121' union
select ID= 266 ,UserName='AMNG',Code='L013',BidPrice='121' union
select ID= 267 ,UserName='AMNG',Code='D001',BidPrice='71' union
select ID= 268 ,UserName='AMNG',Code='D002',BidPrice='71' union
select ID= 269 ,UserName='AMNG',Code='D008',BidPrice='71' union
select ID= 270 ,UserName='AMNG',Code='D009',BidPrice='71' union
select ID= 271 ,UserName='AMNG',Code='D010',BidPrice='71' union
select ID= 272 ,UserName='AMNG',Code='D021',BidPrice='71' union
select ID= 273 ,UserName='AMNG',Code='D011',BidPrice='39' union
select ID= 274 ,UserName='AMNG',Code='D012',BidPrice='39' union
select ID= 275 ,UserName='AMNG',Code='D003',BidPrice='31' union
select ID= 276 ,UserName='AMNG',Code='D004',BidPrice='31' union
select ID= 277 ,UserName='AMNG',Code='D005',BidPrice='31' union
select ID= 278 ,UserName='AMNG',Code='D006',BidPrice='31' union
select ID= 279 ,UserName='AMNG',Code='D007',BidPrice='31' union
select ID= 280 ,UserName='AMNG',Code='D013',BidPrice='51' union
select ID= 281 ,UserName='AMNG',Code='D016',BidPrice='51' union
select ID= 282 ,UserName='AMNG',Code='D014',BidPrice='121' union
select ID= 283 ,UserName='AMNG',Code='D015',BidPrice='121' union
select ID= 284 ,UserName='AMNG',Code='D018',BidPrice='121' union
select ID= 285 ,UserName='AMNG',Code='D019',BidPrice='121' union
select ID= 286 ,UserName='AMNG',Code='D020',BidPrice='121' union
select ID= 287 ,UserName='COL',Code='L013',BidPrice='110' union
select ID= 288 ,UserName='COL',Code='L014',BidPrice='111' union
select ID= 289 ,UserName='YY',Code='D018',BidPrice='155' union
select ID= 290 ,UserName='YY',Code='D015',BidPrice='151' union
select ID= 291 ,UserName='YY',Code='D014',BidPrice='151' union
select ID= 292 ,UserName='YY',Code='D019',BidPrice='131' union
select ID= 293 ,UserName='YY',Code='D020',BidPrice='131' union
select ID= 294 ,UserName='YY',Code='D017',BidPrice='151' union
select ID= 295 ,UserName='YY',Code='D013',BidPrice='55' union
select ID= 296 ,UserName='YY',Code='D016',BidPrice='55' union
select ID= 297 ,UserName='ALH',Code='D013',BidPrice='36' union
select ID= 299 ,UserName='ALH',Code='D015',BidPrice='111' union
select ID= 300 ,UserName='ALH',Code='D016',BidPrice='41' union
select ID= 301 ,UserName='ALH',Code='D018',BidPrice='121' union
select ID= 302 ,UserName='ALH',Code='D019',BidPrice='121' union
select ID= 303 ,UserName='ALH',Code='D020',BidPrice='121' union
select ID= 304 ,UserName='ALH',Code='L003',BidPrice='151' union
select ID= 305 ,UserName='ALH',Code='L004',BidPrice='111' union
select ID= 306 ,UserName='ALH',Code='L005',BidPrice='201' union
select ID= 307 ,UserName='ALH',Code='L006',BidPrice='151' union
select ID= 308 ,UserName='ALH',Code='L008',BidPrice='131' union
select ID= 309 ,UserName='ALH',Code='L009',BidPrice='61' union
select ID= 310 ,UserName='ALH',Code='L010',BidPrice='61' union
select ID= 311 ,UserName='ALH',Code='L011',BidPrice='69' union
select ID= 312 ,UserName='ALH',Code='L012',BidPrice='61' union
select ID= 313 ,UserName='ALH',Code='L013',BidPrice='121' union
select ID= 314 ,UserName='ALH',Code='L014',BidPrice='121' union
select ID= 315 ,UserName='ALH',Code='L015',BidPrice='59' union
select ID= 321 ,UserName='ELCH',Code='L006',BidPrice='150' union
select ID= 326 ,UserName='YYLE',Code='L001',BidPrice='309' union
select ID= 327 ,UserName='YYLE',Code='L005',BidPrice='309' union
select ID= 328 ,UserName='YYLE',Code='L003',BidPrice='209' union
select ID= 329 ,UserName='YYLE',Code='L004',BidPrice='209' union
select ID= 330 ,UserName='YYLE',Code='L006',BidPrice='209' union
select ID= 331 ,UserName='YYLE',Code='L007',BidPrice='209' union
select ID= 232 ,UserName='PYC',Code='L011',BidPrice='56' union
select ID= 233 ,UserName='PYC',Code='L012',BidPrice='56' union
select ID= 298 ,UserName='ALH',Code='D014',BidPrice='111' union
select ID= 332 ,UserName='YYLE',Code='L008',BidPrice='209' union
select ID= 333 ,UserName='YYLE',Code='L009',BidPrice='109' union
select ID= 334 ,UserName='YYLE',Code='L010',BidPrice='109' union
select ID= 335 ,UserName='YYLE',Code='L015',BidPrice='109' union
select ID= 336 ,UserName='JYLIM',Code='D014',BidPrice='110' union
select ID= 337 ,UserName='JYLIM',Code='D015',BidPrice='110' union
select ID= 338 ,UserName='JYLIM',Code='D018',BidPrice='110' union
select ID= 339 ,UserName='JYLIM',Code='D019',BidPrice='110' union
select ID= 340 ,UserName='JYLIM',Code='D020',BidPrice='110' union
select ID= 341 ,UserName='JYLIM',Code='L001',BidPrice='120' union
select ID= 342 ,UserName='JYLIM',Code='L005',BidPrice='150' union
select ID= 343 ,UserName='JYLIM',Code='L011',BidPrice='60' union
select ID= 344 ,UserName='JYLIM',Code='L012',BidPrice='60' union
select ID= 345 ,UserName='JYLIM',Code='L006',BidPrice='110' union
select ID= 346 ,UserName='JYLIM',Code='L014',BidPrice='110' union
select ID= 347 ,UserName='JYLIM',Code='L008',BidPrice='110' union
select ID= 381 ,UserName='SKDN',Code='L011',BidPrice='107' union
select ID= 382 ,UserName='SKDN',Code='L012',BidPrice='111' union
select ID= 383 ,UserName='SKDN',Code='D018',BidPrice='171' union
select ID= 384 ,UserName='SKDN',Code='D019',BidPrice='191' union
select ID= 385 ,UserName='SKDN',Code='D020',BidPrice='191' union
select ID= 386 ,UserName='SKDN',Code='D017',BidPrice='191' union
select ID= 387 ,UserName='SKDN',Code='D014',BidPrice='171' union
select ID= 388 ,UserName='SKDN',Code='D015',BidPrice='171' union
select ID= 389 ,UserName='SKDN',Code='L003',BidPrice='171' union
select ID= 390 ,UserName='SKDN',Code='L004',BidPrice='171' union
select ID= 391 ,UserName='SKDN',Code='L006',BidPrice='191' union
select ID= 392 ,UserName='SKDN',Code='L007',BidPrice='181' union
select ID= 393 ,UserName='SKDN',Code='L008',BidPrice='171' union
select ID= 396 ,UserName='KX',Code='L003',BidPrice='160' union
select ID= 234 ,UserName='EK',Code='L014',BidPrice='200' union
select ID= 235 ,UserName='FZA',Code='D018',BidPrice='150' union
select ID= 253 ,UserName='JSCC',Code='D008',BidPrice='100' union
select ID= 254 ,UserName='JSCC',Code='D018',BidPrice='300' union
select ID= 255 ,UserName='JSCC',Code='D010',BidPrice='80' union
select ID= 256 ,UserName='JSCC',Code='L012',BidPrice='150' union
select ID= 257 ,UserName='JSCC',Code='L005',BidPrice='300' union
select ID= 258 ,UserName='JSCC',Code='D019',BidPrice='325' union
select ID= 259 ,UserName='JSCC',Code='D020',BidPrice='350' union
select ID= 260 ,UserName='JSCC',Code='D017',BidPrice='275' union
select ID= 261 ,UserName='JSCC',Code='D014',BidPrice='200' union
select ID= 262 ,UserName='JSCC',Code='D015',BidPrice='200' union
select ID= 348 ,UserName='LTLL',Code='D017',BidPrice='366' union
select ID= 349 ,UserName='LTLL',Code='D018',BidPrice='306' union
select ID= 350 ,UserName='LTLL',Code='L011',BidPrice='266' union
select ID= 351 ,UserName='LTLL',Code='L005',BidPrice='266' union
select ID= 355 ,UserName='AMCH',Code='L011',BidPrice='404' union
select ID= 356 ,UserName='AMCH',Code='L012',BidPrice='202' union
select ID= 357 ,UserName='AMCH',Code='L014',BidPrice='303' union
select ID= 358 ,UserName='AMCH',Code='L013',BidPrice='303' union
select ID= 359 ,UserName='AMCH',Code='D020',BidPrice='303' union
select ID= 360 ,UserName='AMCH',Code='D019',BidPrice='303' union
select ID= 361 ,UserName='AMCH',Code='D018',BidPrice='303' union
select ID= 362 ,UserName='AMCH',Code='D015',BidPrice='303' union
select ID= 363 ,UserName='AMCH',Code='D014',BidPrice='303' union
select ID= 364 ,UserName='AMCH',Code='D009',BidPrice='101' union
select ID= 365 ,UserName='AMCH',Code='D008',BidPrice='101' union
select ID= 366 ,UserName='ALW',Code='L003',BidPrice='120' union
select ID= 367 ,UserName='ALW',Code='L008',BidPrice='145' union
select ID= 368 ,UserName='ALW',Code='D019',BidPrice='145' union
select ID= 316 ,UserName='SGH',Code='D020',BidPrice='240' union
select ID= 317 ,UserName='SGH',Code='D019',BidPrice='250' union
select ID= 318 ,UserName='SGH',Code='D018',BidPrice='260' union
select ID= 319 ,UserName='SGH',Code='L012',BidPrice='185' union
select ID= 320 ,UserName='ELCH',Code='L014',BidPrice='150' union
select ID= 322 ,UserName='KNLM',Code='D018',BidPrice='169' union
select ID= 323 ,UserName='KNLM',Code='D014',BidPrice='169' union
select ID= 324 ,UserName='KNLM',Code='D015',BidPrice='169' union
select ID= 325 ,UserName='KNLM',Code='L014',BidPrice='219' union
select ID= 352 ,UserName='PRAV',Code='D019',BidPrice='150' union
select ID= 353 ,UserName='PRAV',Code='L013',BidPrice='120' union
select ID= 354 ,UserName='PRAV',Code='L014',BidPrice='150' union
select ID= 369 ,UserName='SYMH',Code='L011',BidPrice='61' union
select ID= 370 ,UserName='SYMH',Code='L012',BidPrice='60' union
select ID= 371 ,UserName='SYMH',Code='L010',BidPrice='51' union
select ID= 372 ,UserName='OLA',Code='L001',BidPrice='150' union
select ID= 373 ,UserName='OLA',Code='L003',BidPrice='150' union
select ID= 374 ,UserName='OLA',Code='L004',BidPrice='150' union
select ID= 375 ,UserName='OLA',Code='L005',BidPrice='150' union
select ID= 376 ,UserName='OLA',Code='L006',BidPrice='150' union
select ID= 377 ,UserName='OLA',Code='L007',BidPrice='150' union
select ID= 378 ,UserName='OLA',Code='L008',BidPrice='150' union
select ID= 379 ,UserName='OLA',Code='L013',BidPrice='150' union
select ID= 380 ,UserName='OLA',Code='L014',BidPrice='150' union
select ID= 400 ,UserName='YSLW',Code='D008',BidPrice='151' union
select ID= 401 ,UserName='YSLW',Code='D009',BidPrice='151' union
select ID= 402 ,UserName='YSLW',Code='D001',BidPrice='151' union
select ID= 403 ,UserName='YSLW',Code='D002',BidPrice='151' union
select ID= 404 ,UserName='YSLW',Code='D010',BidPrice='151' union
select ID= 405 ,UserName='YSLW',Code='D011',BidPrice='91' union
select ID= 406 ,UserName='YSLW',Code='D004',BidPrice='91' union
select ID= 407 ,UserName='YSLW',Code='D005',BidPrice='91' union
select ID= 408 ,UserName='YSLW',Code='D006',BidPrice='91' union
select ID= 409 ,UserName='YSLW',Code='D007',BidPrice='91' union
select ID= 410 ,UserName='YSLW',Code='D012',BidPrice='91' union
select ID= 411 ,UserName='YSLW',Code='D003',BidPrice='91' union
select ID= 412 ,UserName='YYLE',Code='L002',BidPrice='99' union
select ID= 413 ,UserName='YYLE',Code='L013',BidPrice='209' union
select ID= 414 ,UserName='YYLE',Code='L014',BidPrice='209' union
select ID= 394 ,UserName='SPNG',Code='L006',BidPrice='161' union
select ID= 395 ,UserName='SPNG',Code='L008',BidPrice='161' union
select ID= 397 ,UserName='SHIR',Code='L005',BidPrice='161' union
select ID= 398 ,UserName='SHIR',Code='L007',BidPrice='161' union
select ID= 399 ,UserName='SHIR',Code='L003',BidPrice='161' union
select ID= 418 ,UserName='FZA',Code='D009',BidPrice='71' union
select ID= 419 ,UserName='AW',Code='D009',BidPrice='55' union
select ID= 420 ,UserName='ELCH',Code='L015',BidPrice='100' union
select ID= 421 ,UserName='ELCH',Code='L009',BidPrice='150' union
select ID= 422 ,UserName='AW',Code='L009',BidPrice='70' union
select ID= 423 ,UserName='AW',Code='L015',BidPrice='100'

Declare @tempResult table(
ID int,
UserName nvarchar(50),
Code nvarchar(50),
BidPrice float
)

--move all the orders into temporary table
insert into @tempResult
select * from @orders

DECLARE @X INT=1;
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

Declare @count int = (select count(*) from @tempResult);
WAY:
print @count
drop table tempResult1
--Step 2
select 'SQ'+cast(SQ as nvarchar(10)) as SQ, Code,BidPrice into tempResult1 from (
select RANK() over (partition by Code order by BidPrice desc ) as SQ, Code,BidPrice
from @tempResult
) A order by cast(SQ as int) ASC

--Step 3
select @cols = STUFF((SELECT ',' + cast(SQ as nvarchar(10))
from tempResult1
group by SQ
order by cast(Replace(SQ,'SQ','') as int) ASC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

drop table tempResult5
set @query = 'SELECT Code, ' + @cols + '
into tempResult5 from tempResult1
pivot
(
max(BidPrice)
for SQ in (' + @cols + ')
) p
order by Code'
--print @query
execute(@query)

drop table tempResult2
--Step 4
select UserName,Code,BidPrice
into tempResult2
from @tempResult
order by UserName asc,ID asc

--Step 5
drop table tempResult3
select 'SQ'+cast(SQ as nvarchar(10)) as SQ, UserName, Code,BidPrice into tempResult3 from (
select RANK() over (partition by Code order by BidPrice desc ) as SQ, UserName, Code, BidPrice
from tempResult2
) A order by cast(SQ as int) ASC

--Step 6
select @cols = STUFF((SELECT ',' + cast(SQ as nvarchar(10))
from tempResult3
group by SQ
order by cast(Replace(SQ,'SQ','') as int) ASC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

drop table tempResult6
set @query = 'SELECT UserName, Code, ' + @cols + '
into tempResult6 from tempResult3
pivot
(
max(BidPrice)
for SQ in (' + @cols + ')
) p
order by UserName, Code'
--print @query
execute(@query)

Declare @finalResult table (
SQ int,
Code nvarchar(20),
UserName nvarchar(20),
OrderID int,
SQ1 int,
loops int,
ID int,
PeriodID int
)

insert into @finalResult
(loops,
PeriodID,
SQ,
Code,
UserName,
ID,
SQ1)
select loops=@X,PeriodID=3, * from
(
select
RANK() over (partition by b.UserName order by ID asc ) as SQ,
a.Code,b.UserName,
o.ID,
a.SQ1
from tempResult5 a
Join(
select * from tempResult6 where SQ1 is not null
) b on b.Code=a.Code and a.SQ1=b.SQ1
join(
select * from @orders
) o on o.UserName=b.UserName and o.Code=a.Code
) A
where SQ=1

delete t1
from @finalResult t1
join(
select * from(
select distinct
RANK() over (partition by a.Code order by a.Code asc,o.ID asc ) as SQ,
a.Code,
a.UserName,
o.ID
from @finalResult a
left join @orders o on a.Code=o.Code and a.UserName=o.UserName
) BB where SQ<>1
)t2 on t1.Code=t2.Code and t1.UserName=t2.UserName

delete from @tempResult where UserName in(select UserName from @finalResult)
and Code not in (select Code from @finalResult)

SET @X += 1;

set @count = (select count(*) from @tempResult);
IF @X<=@count GOTO WAY; --> Here the WHILE @X<=1

--IF @X=@count
--BEGIN
select distinct Code,UserName from @finalResult
--END

Can anyone advise me whether my sql query written correctly to get top winner for each product; limit 1 user only allow win 1 item based on 1st come first win.

Thank you.

Regards,
Mic

2

Rank() can return more than 1 row per partition with a value of one if there is a tie. Row_Number() will return one row with a value of 1.

3

I couldn't quite follow your logic, but it seems to work on the sample data you provided. Seeing that this is a bidding system, would a person ever bid more than once on the same product? If so, your query fails on this data:

insert into @orders
select ID=1,UserName='A1',Code='D014',BidPrice='110' union
select ID=2,UserName='A2',Code='D014',BidPrice='120' union
select ID=3,UserName='A1',Code='D014',BidPrice='130'

I would have written it like so:

set nocount on;

declare @final table(
   id       int
  ,username nvarchar(50)
  ,code     nvarchar(50)
  ,bidprice float
);

declare @inserted int=1;
while (@inserted>0)
begin
   insert into @final(id,username,code,bidprice)
   select id
         ,username
         ,code
         ,bidprice
     from (select id
                 ,username
                 ,code
                 ,bidprice
                 ,row_number() over(partition by username
                                    order by id
                                   )
                  as rn
             from (select id
                         ,username
                         ,code
                         ,bidprice
                         ,row_number() over(partition by code
                                            order by bidprice desc
                                                    ,id
                                           )
                          as rn
                     from @orders as o1
                    where not exists (select 1
                                        from @final as o2
                                       where o2.code=o1.code
                                          or o2.username=o1.username
                                     )
                  ) as o
            where rn=1
          ) as o
    where rn=1
   ;

   set @inserted=@@rowcount;

end;

set nocount off;

/****
 * result
 */
select *
  from @final
 order by code
;

/****
 * rest
 */
select *
  from @orders as o
 where not exists (select 1
                     from @final as f
                    where f.code=o.code
                  )
;

If table is huge, I would create temp table with appropriate index.

1 Like
4

Many Thanks. You're brilliant.