SQL SERVER: TOP 10 rows per field - if less than 10 rows then display empty rows -


i need select top 10 acct rows based on sys_cd value. hence wrote below query. query working fine.

select sys_cd, acct, cntacct ,rowid   ( select sys_cd, acct, count(acct) cntacct,            row_number() on (partition sys_cd                               order count(acct) desc                              )              rowid     [fcidial].[dbo].table1     err_cd != 'y'       group sys_cd, acct   ) tmp rowid <= 10 order sys_cd, rowid, acct;

it providing below result

sys_cd fin_aaat    cntfin_aaat rowid aaa     606000          4        1 aaa     566000          3            2 aaa     503200          1            3 bbb     251260      42433978         1 bbb     400601      41181797         2 bbb     400401      8399908          3 bbb     503200      2087703          4 bbb     604000      40795        5 bbb     130039      4748             6 bbb     252000      655              7 bbb     736000      40               8 bbb     735000      38               9 bbb     734000      36               10 ccc     233210      73611         1 ccc     464250      39397             2 ccc     186020      35231             3 ccc     265155      4949              4

the query result correct.

but expected output is, sys_cd if rowid less 10 display blank rows remaining rows.

example: in above 'aaa' present 3 rowids. need display 7 blank rows.
'bbb'is present 10 rowids. no need of blank rows. 'ccc' present 4 rowids, need display 6 blank rows.

i expect below output.

sys_cd acct        cntacct        rowid aaa     606000          4        1 aaa     566000          3            2 aaa     503200          1            3                     - blank row                      - blank row                      - blank row                      - blank row                      - blank row                      - blank row                      - blank row                      - blank row  bbb     251260      42433978         1 bbb     400601      41181797         2 bbb     400401      8399908          3 bbb     503200      2087703          4 bbb     604000      40795        5 bbb     130039      4748             6 bbb     252000      655              7 bbb     736000      40               8 bbb     735000      38               9 bbb     734000      36               10 ccc     233210      73611         1 ccc     464250      39397             2 ccc     186020      35231             3 ccc     265155      4949              4                     - blank row                      - blank row                      - blank row                      - blank row                      - blank row                      - blank row                      - blank row                      - blank row

how can achieve desired result.

you need values sys_cd, , table of numbers 1 - 10:

select  ccd.sys_cd, n.rowid    (select distinct sys_cd [fcidial].[dbo].table1 err_cd != 'y') ccd         cross join (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) n (rowid);

once have can left join original query, end null missing records:

with tmp (   select  sys_cd,              acct,              count(acct) cntacct,             row_number() on (partition sys_cd order count(acct) desc) rowid        [fcidial].[dbo].table1       err_cd != 'y'     group sys_cd, acct )  select  ccd.sys_cd, tmp.acct, tmp.cntacct, n.rowid    (select distinct sys_cd [fcidial].[dbo].table1 err_cd != 'y') ccd         cross join (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) n (rowid)         left join tmp             on tmp.sys_cd = ccd.sys_cd             , tmp.rowid = n.rowid order ccd.sys_cd, n.rowid;

Comments

Post a Comment

Popular posts from this blog

html - Outlook 2010 Anchor (url/address/link) -

i'm trying link specific pdf page msoutlook'10 or msword'10, #page=... anchor particularly useful , works fine elsewhere. unfortunately when click [ctr-k] in outlook or winword, links re-formatted assuming unc path. pdf file located on shared drive. using "file:" prefix links open pdf in adobe reader (outside of browser), , "http:" open inside browser. issue seem need open in browser "file:" prefix in order anchor work. know anchors work inside browser. outlook uses windows/explorer default program (based on file extension) seems open in adobe reader neglecting anchor. is aware of solution, link specific page or bookmark of shared-pdf ms outlook and/or winword link? common references: mailermailer.com/...anchor-tags-html-emails campaignmonitor.com/...anchor-links-in-email-newsletters/ stackoverflow.com/...anchor-in-pdf-document
Read more

javascript - Why does running this loop 9 times take 100x longer than running it 8 times? -

consider code: test = function() { } t = new test(); (var = 0; < 8; i++) { result = t instanceof test; } if change number of iterations 8 9 , loop take 100 times longer complete in latest version of firefox (41.0.1). tested on 2 different pcs , magic limit 8. here jsperf test used: http://jsperf.com/instanceof-8-times-vs-9-times does have idea why might happen? seems specific instanceof . not happen if else object, example check property. note: filed bugzilla bug this. jan de mooij mozilla team has posted details in bugzilla thread . here's simplistic interpretation of highly technical answers: in i < 8 case, firefox smart enough hoist result = t instanceof test; statement out of loop (according tests, doesn't seem omit altogether). in i < 9 case, apparently doesn't optimization . why? reason not clear, related fact 9 iterations threshold above function considered "hot" enough run through jit compiler. i < 8 cas...
Read more

Getting gateway time-out Rails app with Nginx + Puma running on Digital Ocean -

following this guide setup app on digital ocean droplet, not working. site not loading , it's throwing: "gateway time-out". the puma server works, since ps aux | grep puma prints: root 15878 0.0 0.4 376832 2168 ? sl 12:50 0:00 puma 2.13.4 (unix:///var/www/myapp/shared/tmp/sockets/myapp-puma.sock) [20150930164940] root 15881 0.0 0.7 903676 3692 ? sl 12:50 0:06 puma: cluster worker 0: 15878 [20150930164940] root 24654 0.0 0.1 11748 916 pts/2 s+ 18:00 0:00 grep --color=auto puma my nginx config: user root; worker_processes 1; events { worker_connections 1024; } http { upstream puma { server unix:///var/www/myapp/shared/tmp/sockets/myapp-puma.sock; } server { listen 80; server_name myapp.com; root /var/www/myapp/current/public; access_log /var/www/myapp/current/log/access.log; error_log /...
Read more