php - Show mysql records according to the rank given and order by -

php - Show mysql records according to the rank given and order by -

i writing query should show records according rank position given along cost order , grouping sitename

shopdetails id | productid | productname | sitename | siteid | site_priority | cost | color 1 555 xyz 1 0 10 bluish 2 555 xyz b 12 1 50 bluish 3 555 xyz 1 0 12 reddish 4 555 xyz c 3 4 9 reddish 5 555 xyz e 15 5 19 bluish 6 555 xyz e 15 5 21 reddish 7 555 xyz b 12 1 42 reddish 8 555 xyz c 3 4 56 bluish

three conditions have getting expected output conditions

if site_priority !=0 set record in position. eg. if site_priority = 5 . show record in 5th position after grouping sitename show records cost asc if site_priority = 0 group records sitename , sort them cost

final expected output

id | productid | productname | sitename | siteid | site_priority | cost | color 7 555 xyz b 12 1 42 reddish 2 555 xyz b 12 1 50 bluish ############ above 2 records kept in first position since site_priority = 1 , ordered cost asc check site_priority 2 there if not show site_priority = 0 cost asc , 2nd records 1 555 xyz 1 0 10 bluish 3 555 xyz 1 0 12 bluish check site_priority 3 there if not check site_priority 0 , not create priority 1 level minus . move site_priority 4 3 , 5 4 . 4 555 xyz c 3 4 9 reddish 8 555 xyz c 3 4 56 bluish 5 555 xyz e 15 5 19 bluish 6 555 xyz e 15 5 21 reddish

is there best way complicated query .

i tired doing not coming expected.

select productid, productname, sitename, site_priority, price, colorname, (select count(*) shopdetails b productid = 1250 , b.site_priority > a.site_priority order cost asc)+1 rnk shopdetails productid = 1250 having site_priority > 0 order rnk

easy understand first condition

sitename | priority 1 b 2 c 3 d 0 e 0 f 0 g 0 h 0 5 outpt 1 ==> position 1 b 2 ==> position 2 c 3 ==> position 3 d 0 ==> position 4 5 ==> position 5 e 0 ==> position 6 f 0 ==> position 7 g 0 ==> position 8 h 0 ==> position 9

this interesting 1 solve. i'm not positive understood correctly here's came (it's little funky). matches output of sample though.

select * test order case when site_priority = 0 ( select k.outp ( select @rownum:= @rownum + 1 outp, t.site_priority outp2 ( select distinct site_priority test order site_priority asc) t, (select @rownum := 0) r t.site_priority != @rownum) k k.outp != k.outp2 limit 1) else site_priority end, sitename, price;

php mysql