\nSometimes a T-SQL gal just has to lower her standards. ANSI standards, that is.\n<\/p>\n
\nYou're sitting there with a tough nut to crack and MS SQL Server just has such a … sweet… resolution for it that…\n<\/p>\n
\nYou just gotta admit that you're probably not ever going to migrate this database in this lifetime and that, if you did, Oracle probably has an equivalently sweet solution, spelled one or two letters differently, and you'll Cross That Bridge When You Come To It.\n<\/p>\n
\nI have a project where we're pulling a list of "candidates" for a customer service survey. The rules are really complicated for selection, but the pseudocode for the query look like this:\n<\/p>\n
\nSELECT Account, Email, ContactName FROM
\n [ReallyComplicatedContactView]
\nWHERE
\n [Millions of contact-filtering conditions here] AND
\n [Lots more time-sensitive conditions here]
\nORDER BY [priorities du jour here]\n<\/p>\n
\nSo now we decide that we also don't want to select more than <target number> candidates from any one Account, at any one time, for this survey. Bleh.\n<\/p>\n
\nT-SQL 2005 to the rescue<\/a>…\n<\/p>\n \nSELECT Account, Email, ContactName, AcctRank FROM \nThat's almost too easy.<\/p>\n","protected":false},"excerpt":{"rendered":" Sometimes a T-SQL gal just has to lower her standards. ANSI standards, that is. You're sitting there with a tough nut to crack and MS SQL Server just has such a … sweet… resolution for it that… You just gotta admit that you're probably not ever going to migrate this database in this lifetime andRead more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-187","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/187","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=187"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/187\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=187"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=187"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=187"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n (SELECT V.Account, V.Email, V.ContactName ,
\n ROW_NUMBER()
\n OVER (PARTITION BY Account
\n ORDER BY Account ASC, [Priorities du jour] ASC )
\n AS AcctRank <\/span>
\n FROM
\n [ReallyComplicatedContactView] V
\n WHERE
\n [as before] AND
\n [as before]
\n ) XX
\nWHERE XX.AcctRank <= @TargetNumber<\/span>
\nORDER BY [as before]\n<\/p>\n