Optimize Query

Question

Optimize Query

Default port

Points: 1400

January 27, 2022 at 7:29 am

--#######################################
--Given Rawdata
--#######################################
create table #Base (C1 char(1),C2 char(3))

insert into #Base values 
 ('A','122')
,('A','123')
,('B','122')
,('B','122')
,('B','123')
,('C','122')
,('D','122')
,('D','123')
,('D','123')

select C1,C2,count(*)
from #Base
group by C1,C2
order by 1

--#######################################
--Target/Goal: 
--C1 = 'B' is the only instance of more C2 instances of 122 than 123
--#######################################
--Any ideas for a better version than this?
--#######################################
select  v1.C1
       from (
              select C1,/*C2,*/       C3=count(*)
                  from #Base
                  where C2 = '122'
                  group by C1/*,C2*/        
            ) v1 
            inner join
            (
              select C1,/*C2,*/       C3=count(*)
                 from #Base
                 where C2 = '123'
                 group by C1/*,C2*/
            ) v2 
on v1.C1 = v2.C1
and v1.C3 > v2.C3

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

This seems to return the correct results and could be "more optimized" (depending on cardinalities, indexes, etc.)

select C1
from #Base
group by C1
having sum(iif(C2='122', 1, 0))>nullif(sum(iif(C2='123', 1, 0)), 0);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

@ktflash - Just as a suggestion to help clarify exactly what you want for future posts.. It would be helpful if you'd post a clipping of a spreadsheet or columnized text that shows exactly what you want so people don't have to interpret the code that doesn't work (which was handy for you to include and thanks for that). It'll save folks a little time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

SELECT C1
FROM #Base
WHERE C2 IN ('122', '123')
GROUP BY C1
HAVING SUM(CASE WHEN C2 = '123' THEN 1 ELSE 0 END) > SUM(CASE WHEN C2 = '122' THEN 1 ELSE 0 END)
ORDER BY C1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ktflash Default port Points: 1400 More actions · Answer 4

Jeff Moden wrote:

@ktflash - Just as a suggestion to help clarify exactly what you want for future posts.. It would be helpful if you'd post a clipping of a spreadsheet or columnized text that shows exactly what you want so people don't have to interpret the code that doesn't work (which was handy for you to include and thanks for that). It'll save folks a little time.

i admit i was quiet sparse on the explanation, but my post has:

example data + creation of the base temp table
a WORKING query example showing the wanted result
the one liner explaining WHY the provided query provides the result it provides

The explanation should be better i can agree on that. But what do you mean with this part:

Jeff Moden wrote:

It would be helpful if you'd post a clipping of a spreadsheet or columnized text that shows exactly what you want

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help