Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Accounts with same rows as a given account

## I have an ACCOUNT table with columns ACCT_NBR, STYLE and PERCENTAGE. There can be many records for a single account (that is, there can be many records with same ACCT_NBR column value). I need to select all accounts having records similar to a given account.

I have an ACCOUNT table with columns ACCT_NBR, STYLE and PERCENTAGE. There can be many records for a single account (that is, there can be many records with same ACCT_NBR column value). I need to select all accounts having records similar to a given account.

Example:

```AccNum1  Style1  Percentage1
AccNum1  Style2  Percentage2
AccNum2  Style1  Percentage1
AccNum3  Style1  Percentage1
AccNum3  Style2  Percentage2```

Here, AccNum1 and AccNum3 have the same values in columns (other than ACCT_NBR column). So, I need to collect the list of similar accounts together for a big batch process. Could you please let me know how to achieve it using analytical functions? I have tried and done it using SQL.

Right off the top I must apologize, because this solution will not use analytical functions. Sorry, I'm just not up to speed on them yet. I don't even know if a solution to your question is possible using analytical functions. Hopefully, one of my five or six regular readers will know, and send in a query.

The way to solve this with ordinary SQL is with a grouped self-join.

```select t2.ACCT_NBR
from ACCOUNT as t1
inner
join ACCOUNT as t2
on t1.ACCT_NBR  <> t2.ACCT_NBR
and t1.STYLE      = t2.STYLE
and t1.PERCENTAGE = t2.PERCENTAGE
where t1.ACCT_NBR = 'AccNum1'
group
by t2.ACCT_NBR
having count(*)
= ( select count(*)
from ACCOUNT
where ACCT_NBR = 'AccNum1' ) ```

This self-join query joins each style/preference row for AccNum1 (see WHERE clause) with every matching style/preference row for any other account (except itself), and groups the result by the other account. If the number of matching rows for the other account is the same as the number of rows for AccNum1, then the other account satisfies the HAVING clause, and is returned in the result set.

In the HAVING clause, you can see a subquery. This is an uncorrelated subquery, since it is not related to each particular GROUP BY group, which in this case is T2.ACCT_NBR. This means the subquery can be evaluated once, before the main query starts execution. Then the main query produces a COUNT(*) for each T2.ACCT_NBR, which is then compared to the subquery count obtained earlier. If there are any missing style/preference rows for the other account, then its COUNT(*) will be less than the count for AccNum1. Neat, eh?

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Developing an enterprise data strategy: 10 steps to take

Consultants detail 10 to-do items for data management teams looking to create a data strategy to help their organization use data...

• ### How graph data science is helping the fight against COVID-19

Technology to rapidly correlate and connect disparate data sets in a knowledge graph is being used by German researchers and ...

• ### Okera sees increasing demand for enterprise data governance

Data governance and access vendor raises \$14 million as incoming CEO Nick Halsey sees a continuing need for enterprise data ...

• ### OmniSci analytics tools to come embedded in HP workstations

Under the terms of an agreement between OmniSci and HP revealed on Thursday, OmniSci's BI platform will come as a preloaded ...

• ### Economic downturn shows businesses true value of analytics

Business intelligence is proving to be a critical tool for enterprises as they attempt to survive during the sudden economic ...

• ### Key differences of a data scientist vs. data engineer

Data scientists and data engineers often work together, and sometimes the positions are treated as the same. Read on to find out ...

## SearchSAP

• ### SAP Cloud Platform app helps German citizens find their way home

Rückholprogramm, an SAP Cloud Platform app, was built in just a few days to fulfill a request from the German Federal Foreign ...

• ### SAP S/4HANA deployment options: Legacy ERP vs. cloud

Take this tour of the on-premises S/4HANA and its two cloud siblings and get expert advice on major differences in cost and ...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Compare these top 6 Java IDEs for development

Find the Java IDE that best fits your development needs. Compare these options from Eclipse, NetBeans, IntelliJ, JDeveloper, ...

• ### Eclipse launches Theia 1.0 as alternative to VS Code

The Eclipse Foundation's Theia framework for building desktop and cloud IDEs has the potential to be as big as the initial ...

• ### Developing a COVID-19 application? Design it the right way

Application developers with skills and an idea could design an app to combat the virus. Follow these insights to effectively ...

## SearchDataCenter

• ### Advances in server hardware components speed up data centers

Server hardware has consistently evolved since the 1980s. Now, IT professionals can implement the latest in CPU and GPU ...

• ### Improvements in CPU features help shape selection

CPUs have evolved to meet ever-increasing technology demands. We look at the way performance and power characteristics have ...

• ### Dive into the quantum computing qubit with IBM's Bob Sutor

The quantum computing industry is entering a new era. IBM's Bob Sutor discusses the technology's importance and how his latest ...

## SearchContentManagement

• ### Enterprise search software comparison

Enterprise search has always been both a necessity and a challenge, and vendors have sought to bring improvements to the market ...

• ### New OpenText CE apps, services roll out

OpenText containerizes its applications for cloud deployment; adds raft of content services and features for customer experience ...

• ### Box Shield adds malware detection

Box Inc. quarantines malware-infected files with features that allow content to be viewed, with admin and security team alerts to...

## SearchHRSoftware

• ### Food industry turns to AI hiring platform to fill 1M jobs

The food industry is hungry for employees. It has a shopping list of occupations it needs filled to keep essential food producers...

• ### How companies can use tech to ramp up remote work productivity

With the sudden spike of employees working from home, companies need to get creative in how they keep employees connected and ...

• ### H-1B work visa petitions hit record high, despite pandemic

The U.S. received a record 275,000 H-1B work visas for the 2021 fiscal year. But these visa petitions were prepared in better ...

Close