sql - TSQL Interleaved sequence without join operations or user defined objects -


is possible create interleaved sequence first , last values going in turns untill data set empty without using joins , user defined functions or procedures? 

  code  type    model   price     1   hats    1298    700,00     1   shoes   1232    600,00     1   pants   1276    400,00     2   hats    1321    970,00     2   shoes   1121    850,00     2   pants   1433    270,00     3   hats    1750    1200,00     3   shoes   1233    600,00     3   pants   1434    290,00     4   hats    1298    1050,00     4   shoes   1121    850,00     4   pants   1401    150,00     5   hats    1752    1150,00     5   shoes   1121    850,00     5   pants   1408    270,00     6   hats    1298    950,00     6   shoes   1233    950,00     6   pants   1288    400,00     7   shoes   1232    400,00     8   shoes   1232    350,00     9   shoes   1232    350,00     10  shoes   1260    350,00     11  shoes   1233    980,00     12  shoes   1233    970,00

i have added spaces between rows interleaved sequence idea.

you want odd values going lowest coded items (asc) , values highest coded items (desc). want order type hats,shoes , pants.

code type   model   price 1   hats    1298    700,00 1   shoes   1232    600,00 1   pants   1276    400,00  6   hats    1298    950,00 12  shoes   1233    970,00 6   pants   1288    400,00  2   hats    1321    970,00 2   shoes   1121    850,00 2   pants   1433    270,00  5   hats    1752    1150,00 11  shoes   1233    980,00 5   pants   1408    270,00  3   hats    1750    1200,00 3   shoes   1233    600,00 3   pants   1434    290,00  4   hats    1298    1050,00 10  shoes   1260    350,00 4   pants   1401    150,00  4   shoes   1121    850,00  9   shoes   1232    350,00  5   shoes   1121    850,00  8   shoes   1232    350,00  6   shoes   1233    950,00  7   shoes   1232    400,00

right came solution includes joins looking work without using it.

my solution using joins:

with cteasc  (     select     row_number() over(order code,charindex(type, 'hatsshoespants'))id     ,(row_number() over(partition type order code,charindex(type, 'hatsshoespants')) + 1) / 2 offsetasc     ,code,model,price,type      mydata ), ctedsc  (     select     row_number() on (partition type order code desc)id     ,code,model,price,type     cteasc ) select t1.id ,case     when t1.code%2=1     lag(t1.type,t1.code-t1.offsetasc,t1.type)over(partition t1.type order t1.id)     else lag(t2.type,t1.code-t1.offsetasc,t1.type)over(partition t1.type order t1.id) end type ,case     when t1.code%2=1     lag(t1.model,t1.code-t1.offsetasc,t1.model)over(partition t1.type order t1.id)     else lag(t2.model,t1.code-t1.offsetasc,t1.model)over(partition t1.type order t1.id) end model ,case     when t1.code%2=1     lag(t1.price,t1.code-t1.offsetasc,t1.price)over(partition t1.type order t1.id)     else lag(t2.price,t1.code-t1.offsetasc,t1.price)over(partition t1.type order t1.id) end price  cteasc t1 join ctedsc t2 on t1.code = t2.id , t1.type = t2.type

the idea generate 2 sequences:

  1. the ascending items row numbers 1, 3, 5, ...
  2. the descending items row numbers 2, 4, 6, ...

then, need union all sequences , sort them.

with ascitems (     select *, row_number() on (order somecol asc) r * 2 - 1 r t ) , descitems (     select *, row_number() on (order somecol desc) r * 2 r t ) select * ascitems union select * descitems order r

this should require table t available sorted twice (either index or 2xsorting). union all should manifest cheap merge concat.


Comments

Post a Comment

Popular posts from this blog

angularjs - ADAL JS Angular- WebAPI add a new role claim to the token -

i have created angular spa webapi backend using adal js authentication.as there no roles in ad, need manually add role claims in order give users access different api controllers. the roles stored in database. expecting inject claim through call webapi after authentication ad. webapi code might this. identity.addclaim(new claim("role", "user")); var ticket = new authenticationticket(identity, props); var accesstoken = startup.oauthbeareroptions.accesstokenformat.protect(ticket); is possible replace adal idtoken new token? is viable solution or there other better way handle this? as initial token generated azuread, possible edit token add new claim? appreciated. graph api supporting group claims. see here: http://justazure.com/azure-active-directory-part-4-group-claims/ if @ examples on page, users assigned groups , app can check group in claims. in current version of portal, need app manifest , modify it.
Read more

node.js - Using Node without global install -

i'm working on few node & electron (atom shell) projects , i'm curious how them work without user having install node globally. i've seen few applications including node directory in project contains node.exe binary. how go setting node project use binary? edit: thought should add, i've searched everywhere answer this, unrelated results.
Read more

php - CakePHP HttpSockets send array of paramms -

i problem when make request (i can request). $link = 'http://uri/path/?param1=1&sid=2&sid=3&sid=4' $http = $httpsocket = new httpsocket(); $res = $httpsocket->get($link); if check request pr($http); i see : [line] => /url/path/?param1=1&sid%5b0%5d=2&sid%5b1%5d=3&sid%5b2%5d=4 http/1.1 and i'm getting empty response because server doesn't know parameters sid%5b0%5d=2 when try send parameters array : $data = array('param1' => '1', 'sid' => array('2','3', '4')); i see same changes %5b0%5d - additional indexes. how fix it? unfortunately , can not change server side, if send request in browser, i'll normal response in json format. when make request, can parameters this: $param1 = $_get['param1']; $sid= $_get['sid']; tip: use diferente names parameters. have multiples sid. use sid1, sid2, sid3... update: can parameters this:...
Read more