sql server - SQL select join the row with max (arithmatic(value1, value2)) -


i trying make trade system people can make offer on items want. there 2 currencies in system, gold , silver. 100 silver = 1 gold. note people can make offers same price others, there duplicate highest offer price.

table structure looks this

trade table   id   tradeoffer table   id userid tradeid references trade(id)   goldoffer   silveroffer

i want display user list of trades sorted highest offer price whenever search constraint.

the ideal output similar this

 trade.id    tradeoffer.id    highestgoldoffer    highestsilveroffer    userid

where highestgoldoffer , highestsilveroffer value of goldoffer , silveroffer column of offer highest (goldoffer * 100 + silveroffer) , userid user made offer

i know can run 2 separate queries, 1 retrieve trades satisfies constraint , extract id run query highest offer, perfectionist prefer 1 sql instead of two.

i select offers (goldoffer * 100 + silveroffer) = max (goldoffer * 100 + silveroffer) possibly return duplicated trade if there multiple people offered same price. there nobody offered on trade yet goldoffer , silveroffer empty, still show trade no offer when happened.

hope made myself clear , help

model , test data

create table trade (id int) create table tradeoffer (     id int,     userid int,     tradeid int,     goldoffer int,     silveroffer int )  insert trade values (1), (2), (3) insert tradeoffer values     (1, 1, 1, 10, 15),     (2, 2, 1, 11, 15),     (3, 1, 2, 10, 16),     (4, 2, 2, 10, 16)

query

select     [tradeid],     [tradeofferid],     [highestgoldoffer],     [highestsilveroffer],     [userid] (     select         t.id [tradeid],         toffer.id [tradeofferid],         toffer.goldoffer [highestgoldoffer],         toffer.silveroffer [highestsilveroffer],         toffer.[userid],         rank() on (             partition t.id             order (([goldoffer] * 100) + [silveroffer]) desc         ) [rank]     trade t     left join tradeoffer toffer         on toffer.tradeid = t.id ) x [rank] = 1

result

enter image description here


Comments

Post a Comment

Popular posts from this blog

apache - Remove .php and add trailing slash in url using htaccess not loading css -

i have add following code htaccess file removing .php extension , add trailing slash urls. worked fine reduced page loading speed , css code not loading @ all. pages ugly. please help rewriteengine on rewritebase / ## hide .php extension snippet # externally redirect /dir/foo.php /dir/foo rewritecond %{the_request} ^[a-z]{3,}\s([^.]+)\.php [nc] rewriterule ^ %1/ [r,l] # add trailing slash rewritecond %{request_filename} !-f rewritecond %{request_uri} !/$ rewriterule . %{request_uri}/ [l,r=301] # internally forward /dir/foo /dir/foo.php rewritecond %{request_filename} !-d rewritecond %{request_filename}.php -f rewriterule ^(.*?)/?$ $1.php [l] we can rewrite urls using .htaccess files. before writing in .htaccess files need verify kind of server using. can check server adding a <?php phpinfo(); ?> in php page. ensure mod_rewrite module enabled in apache server. can identified checking phpinfo included page. common methods used url rewriting follows ...
Read more

javascript - jQuery show full size image on click -

pretty self explanatory not workking . i want display full image when user click on image... yes know there tons of plugins but i want keep simple. any idea ? css : .full { display: none; width: 500px; height: 500px; margin-left: 100px; margin-top: -300px; position: fixed; background-color: #f00; z-index: 6; } .thumbnail { width: 50px; height: 50px; } html: <img class="thumbnail" src="http://4.bp.blogspot.com/-uhbdp2esxng/txneptvi92i/aaaaaaaaaky/upfqis7zto0/s1600/rihanna-wallpaper.jpg"> jquery: $(document).ready(function() { $('.thumbnail').on('click', function() { var img = $('<img />',{src:this.src,'class':'full'}); $('.full').html(img).show(); console.log('done!'); }); }); try looking how jquery works. $(document).ready(function() { $('.thumbnail').on('click', function(...
Read more