Query to get the list of Addresses that have AddressType one else AddressType two?

Consider the folowing table

Id  PersonId    Address   AddressTypeId
--------------------------------------------------------------------
1     1         AI1P1T1      1
2     1         AI2P1T2      2  
3     2         AI3P2T2      2

I want to write a query to print the list of Addresses of Persons who have AddressType =1 or AddressTypeId=2 and

When person has AddressType =1 then select it, else select person with AddressType =2

Expected result:

Address
--------------
AI1P1T1           
AI3P2T2   

Good day,

Please check if this solve your needs:

/***************************** DDL+DML */
drop table if exists T;
create table T(Id int,PersonId int, [Address] nvarchar(10), AddressTypeId int)
INSERT T(Id,PersonId, [Address], AddressTypeId)
values
(1,1,'AI1P1T1',1),
(2,1,'AI2P1T2',2),
(3,2,'AI3P2T2',2)
GO

select * from T
GO

/***************************** Solution */
With MyCTE as (
    select *, ROW_NUMBER() OVER (partition by PersonId order by AddressTypeId) as RN
    from T
)
select [Address]
from MyCTE
where 
    AddressTypeId in (1,2) -- if there can be only positive numbers then you can use "< 3"
    and RN = 1
GO

Using Set Operators, In this lab, I will use set operators to combine the results of multiple queries in the named AddressType with the value 'Billing' for customers where the address type in the SalesLT. Combine the results returned by the two queries to create a list of all customer AddressType = 'Main Office' UNION ALL SELECT c. What is the command that can be used to get the IP address and the names of the computers that are located in the same network? Can't be done in general. Certain OSes and networking systems can go some way to doing this, so if you edit your question to include that information, you might get more directed answers. – womble ♦ Jun 24 '09 at 3:12.

You can try this also using joins:

 select t1.PersonId,t1.Address from #T t1 
  inner join (select personid,min(AddressTypeId)atype from #T
  group by PersonId )x 
  on x.atype=t1.AddressTypeId and x.PersonId=t1.PersonId

Visual Basic .NET Database Programming, LISTING 10 . 2 CONTINUED dgOrderDates . Visible = False Else Filter using the selected CustomerOrders I record ' s CustomerId . data _ row _ view RowFilter = order _ id _ filter PART ' If the DataGrids have no data source , give them one . If you select an address type and click OK , the event handler uses the m _  Address Book or Contact List in Windows 10 Have just upgraded to Windows 10 from Windows 7 and cannot seem to find my list of contacts or address book on the Windows 10 email icon (I was successful in adding Hotmail, Yahoo, and Gmail accounts in Windows 10).

I would write a subquery to make ROW_NUMBER by window function, then use MAX in the main query.

SELECT 
    PersonId, MAX(Address) Address
FROM 
    (SELECT
         PersonId,
         (CASE 
             WHEN ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY PersonId) = 1 
                THEN Address 
          END) Address
     FROM 
         T
     WHERE 
         AddressTypeId IN (1,2)
    ) t1
GROUP BY 
    PersonId

sqlfiddle

[Results]:

| PersonId | Address |
+----------+---------+
|        1 | AI1P1T1 |
|        2 | AI3P2T2 |

iPhone All-in-One For Dummies, 2. Tap Create a New Apple ID. The Create an Apple ID screen opens. 3. You have to be at least 13 years of age to create an Apple ID. You can do one of the following: • Use your current email address: Type in the email address you want to use as your Select a question from the options and then type in the answer. If you see more than one IP address before the router's IP address, there's more than one network device between your computer and the router. If you see only the router's IP address, you don't have any managed network hardware between your computer and the router, though you might have simple devices like hubs and unmanaged switches.

Here's the top 1 with ties trick:

select top 1 * with ties
from yourtable 
order by row_number() over (partition by PersonId order by AddressTypeId)

This will also work for versions <2012, and can return every field

Autocomplete for Addresses and Search Terms, Search for Places API, then select it from the results list. The API offers two types of autocomplete widgets, which you can add via the Autocomplete and The results are biased towards, but not restricted to, places contained within these bounds. var val = place.address_components[i][componentForm[​addressType]]; An address list is a collection of mail-enabled recipient objects in Exchange Online. Address lists are based on recipient filters. You can filter by recipient type (for example, mailboxes and mail contacts), recipient properties (for example, Company or State or Province), or both. Address lists aren't static; they're updated dynamically.

You could use an union between the result for the result for only 1, only 2 and 1 when 1 and 2

  select Address 
  from my_table m 
  Inner join ( 
  select  PersonId , count(distinct distinct AddressTypeId)
  from my_table  
  where AddressTypeId in (1, 2)
  group by PersonId 
  having count(distinct AddressTypeId) = 2 
  ) t  on t.personId = m.personId andm.AddressTypeId = 1 

  UNION 
  select Address 
  from my_table m 
  Inner join ( 
  select  PersonId , count(distinct distinct AddressTypeId)
  from my_table  
  where AddressTypeId in ( 2)
  group by PersonId 
  having count(distinct AddressTypeId) = 1
  ) t  on t.personId = m.personId andm.AddressTypeId = 2 

  UNION 
  select Address 
  from my_table m 
  Inner join ( 
  select  PersonId , count(distinct distinct AddressTypeId)
  from my_table  
  where AddressTypeId in ( 1)
  group by PersonId 
  having count(distinct AddressTypeId) = 1
  ) t  on t.personId = m.personId andm.AddressTypeId = 1  

Address Compound Fields, The Address type extends the Location type, the data type used for compound In this case, address field names are prefixed with the type of address,  3. Use UNION ALL to combine the results returned by the two queries to create a list of all customer addresses that is sorted by company name and then address type. SELECT c.CompanyName, a.AddressLine1, a.City, 'Billing' AS AddressType FROM SalesLT.Customer AS c JOIN SalesLT.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID JOIN SalesLT.Address AS a

How to Copy Multiple Tables from On-Premise to Cloud in Azure , There are numerous reasons to utilize cloud computing to supplement or [​Address] and [Person].[AddressType] that came from an [AdventureWorks] database. Select the Author & Monitor tile to start the Azure Data Factory user The ForEach activity iterates through a list of tables, and passes one by  If you don’t have a database selected in the database explorer, chances are it selects Master and your query will fail. One way to get to the right database is to select it from the Available Databases drop dowwn. This tool is shown when you open a query window. The picture below highlights this.

Article - Banner Data Entry Standards, Even if you will only be querying data from the system, you should When creating a new person record, always do a name search before anything else to Address Type Banner has a number of Address Types as shown in the chart below. This two-letter mandatory field identifies which type of address is  Thx Mr Adezzi; It only adds the first email address in the Rt_EmailQ query to the "To" email column of the email to be send. I would like the code to take all the email addresses (in "email" text field) listed in this Rt_EmailQ (query), add it to the "To" column in the email where the addresses are separated by ";" for the report called "RaceresultsR".

First, we must create our own object type called ADDRESS_TYPE. Then we create a table of ADDRESS_TYPE called ADDRESS_TABLE_TYPE. The table ADDRESS_TABLE_TYPE is what we will use as our nested table column type. All of this sounds quite confusing. Just try to remember that every attribute of a table needs some form of data type.

Comments
  • you will need to write a stored procedure with your busines logic. First checking if table has rows for addressTypeID = 1 and if no select with addressTypeID =2
  • What is the logic behind selecting AI3P2T2 instead of AI2P1T2 ?
  • Thanks for your response, I want distinct PersonId
  • @ElhamAzadfar This query returns one row by one personId so I don't get you to comment quite. Please, clarify =)