x

Displaying one row of two based on column value

Below is a copy of my query; the problem is that certain individuals have ZIP codes for both mailing address (MAIL) and residence address (RESI). The default is address is their RESI address although if they have a MAIL address then that is the one to be used. So I want only one row for each person but I need it to show MAIL address if MAIL is evident and not their RESI.

So what I see currently is:

BOX ID----INDV ID------LAST NAME--------STATE-------ZIP------ADDRESS TYPE

4563--------Q455---------SMITH------------------NV--------02382----------RESI

4563--------Q455---------SMITH------------------NV--------15234----------MAIL

4563--------Q888---------ALLY------------------NY--------08324----------RESI

4563--------RA453---------CORA------------------CA--------90210----------RESI

4563--------RA453---------CORA------------------CO--------08235----------MAIL

What I would like to see is:

BOX ID----INDV ID------LAST NAME--------STATE-------ZIP------ADDRESS TYPE

4563--------Q455---------SMITH------------------NV--------15234----------MAIL

4563--------Q888---------ALLY------------------NY--------08324----------RESI

4563--------RA453---------CORA------------------CO--------08235----------MAIL

I have pondered ways to do this and have googled quite a bit but being rather new to SQL I can not for the life of me figure it out. This community seems pretty helpful so thought I would give it a shot. I was thinking an IF statement but again I could not figure out a way to do this.

 SELECT folders.box_id, 
  folders.folder_scan, 
  applicant.last_name, 
  address.state_cd,     
  address.zip_txt, 
  address.address_applicant_id, 
  address.address_type_cd  
 FROM address, folders, applicant  
 WHERE address.address_applicant_id = folders.folder_id 
  and applicant.applicant_id = folders.folder_id 
  and folders.box_id = 'boxID'  
 order by folders.folder_scan  
 go   

Any help would be greatly appreciated!

more ▼

asked Aug 08, 2012 at 12:05 PM in Default

avatar image

SteamWhistle
0 1 1 3

It's actually Adaptive Server Enterprise 15.0.3; sorry I should have led with that.

Aug 08, 2012 at 08:57 PM SteamWhistle

Ah.. than you need probably ask some forum related to that RDBMS. The examples and replies are for Microsoft SQL Server.

Aug 08, 2012 at 09:17 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Ok, I took your example data and output to come up with something that seems to be correct.

The major work here really is using the windowing function ROW_NUMBER(). This is a function that allows you to segregate data in different ways. You said that you need to have one address for each customer (I took INDVID to be a customer id), a MAIL address takes precedence over a RESI address, but otherwise the first matching address should be returned per customer.

The PARTITION BY does the spliy by INDVID then I order the data using the address type, arbitrarily assigning MAIL as the first record and all others as being after that.

Please not that this doesn't account for cases where an INDVID has more than one MAIL address type/ You will need to consider this yourself.

My code returns the expected output from your question and seems to be the best way of doing what you want.

You would swap out my test code with the real select you supplied and then you can test if it works right.

 DECLARE @Table AS TABLE
     (BOXID int,
      INDVID varchar(20),
      LASTNAME varchar(20),
      [STATE] varchar(20),
      ZIP varchar(20),
      ADDRESSTYPE varchar(20))
 INSERT  INTO @Table
     (BOXID, INDVID, LASTNAME, STATE, ZIP, ADDRESSTYPE)
         
     SELECT 4563, 'Q455', 'SMITH', 'NV', '02382', 'RESI' UNION ALL
     SELECT 4563, 'Q455', 'SMITH', 'NV', '15234', 'MAIL' UNION ALL
     SELECT 4563, 'Q888', 'ALLY', 'NY', '08324', 'RESI' UNION ALL
     SELECT 4563, 'RA453', 'CORA', 'CA', '90210', 'RESI' UNION ALL
     SELECT 4563, 'RA453', 'CORA', 'CO', '08235', 'MAIL';
 
 

 WITH    DataSegments
           AS (SELECT    *,
                         ROW_NUMBER() OVER 
                         (PARTITION BY INDVID ORDER BY 
                             CASE WHEN ADDRESSTYPE = 'Mail' THEN 1
                                  ELSE 99
                             END) AS Segment
               FROM      @Table AS T)
     SELECT  BOXID,
             INDVID,
             LASTNAME,
             STATE,
             ZIP,
             ADDRESSTYPE
     FROM    DataSegments
     WHERE   Segment = 1
more ▼

answered Aug 08, 2012 at 12:57 PM

avatar image

WilliamD
26.2k 18 37 48

+1, you have been a quicker and I didn't refresh the question prior posting answer. :-)

Aug 08, 2012 at 01:05 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

On SQL Server 2005+ you can achieve this using Sub Query with ROW_NUMBER furntion as in example below.

 WITH BaseQuery AS (
     SELECT folders.box_id, 
        folders.folder_scan, 
        applicant.last_name, 
        address.state_cd,     
        address.zip_txt, 
        address.address_applicant_id, 
        address.address_type_cd,
        ROW_NUMBER() OVER(PARTITION BY folders.box_id, Indiv_id ORDER BY address.address_type_cd) AS RowNo
     FROM address, folders, applicant  
     WHERE address.address_applicant_id = folders.folder_id 
     and applicant.applicant_id = folders.folder_id 
     and folders.box_id = 'boxID'  
 )
 SELECT
     *
 FROM BaseQuery
 WHERE RowNo = 1
 order by folders.folder_scan  

In the Partition BY part of the ROW_NUMBER function you have to specify all the columns, which uniquey identify the single person.

more ▼

answered Aug 08, 2012 at 01:03 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Your answer was with the real code though, so +1 for you :)

Aug 08, 2012 at 01:10 PM WilliamD

Thank you for the quick posts fellas; I will try this when I get to work later. But Pavel, I may be missing something but how does it filter out the RESI address if there is a MAIL address?

As mentioned I am fairly new to SQL but is it saying that it will take the 1st row if there are two rows for a specific account?

Aug 08, 2012 at 01:35 PM SteamWhistle

This is what I get when I use that code.

Msg 156, Level 15, State 2: Server 'NCRPC_DB', Line 1: Incorrect syntax near the keyword 'WITH'. Msg 156, Level 15, State 2: Server 'NCRPC_DB', Line 17: Incorrect syntax near the keyword 'OVER'. Msg 107, Level 15, State 1: Server 'NCRPC_DB', Line 39: The column prefix 'folders' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.

Cant figure out why...

Aug 08, 2012 at 07:30 PM SteamWhistle

In my example it should work in exactly the same way as in example by @WilliamD. As when Ordering than the MAIL is prior RESI. And the ROW_NUMBER function numbers the rows in that order and starts with 1 for each partition - the partition is a combination of keys specified after the PARTITION BY. It means it will start from 1 for each unique combination of the fields specified after the PARTITION BY and continue in the order specified after the ORDER BY Keywork. Then it is filtered so only the rows with RowNo = 1. It means if there is MAIL and RESI only the MAIL will be taken. In case only one type of address is provided, that addres will be returned.

You can take a look for details about the ROW_NUMBER on MSDN: ROW_NUMBER (Transact-SQL)

Aug 08, 2012 at 07:53 PM Pavel Pawlowski

What version of SQL Server you are running? The WITH Keyword and ROW_NUMBER function is supported on SQL 2005 and above. The example will not work on SQL Server 2000.

In case of SQL 2005 and above, if there some command prior the WITH, it must be terminated by semicolon.

Aug 08, 2012 at 07:56 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1183
x457
x18

asked: Aug 08, 2012 at 12:05 PM

Seen: 3769 times

Last Updated: Aug 08, 2012 at 09:17 PM

Copyright 2018 Redgate Software. Privacy Policy