SQL to Choose First Item Meeting Criteria

[susieqtotallynew]

I am working with a MS SQL database.

The database stores various names for constituents. Each constituent can have multiple name types. Each name type is assigned an ID number. For example, I might have an individual name type, assigned name_type_ID 1. I may also have a joint name type, assigned name_type_ID 2. My partner might have a name type, as well, assigned name_type_3.

The table representation would look like this:
ID, Name, Name_Type_ID
123, Ms. Susie Sample, 1
123, Mr. and Mrs. John Sample, 2
123, Mr. John Sample, 3
333, Mr. Andrew Jones, 1
Note that not every constituent will have every name type.

I need to be able to return a single name type for each ID number based on a priority listing. For example, if name type 2 exists for a given ID, I would return that item. If name type 2 does not exist, I would take name type 1 instead.

Does anyone have advice about a SQL statement that would accomplish this?

Thank you in advance.

 

[markros]

Do you want to return the maximum type id (say, if #3 exists, we return 3) or only 2 and if no 2, then 1?

In the first case (SQL Server 2005 and up):

;with cte as (select ID, Name, Name_Type_ID, row_number() over (partition by ID order by Name_Type_ID DESC) as RowNum from myTable)

select ID, Name, Name_Type_ID from cte where RowNum = 1[/code

Let me know if you need the second case instead.

 

[susieqtotallynew]

Markros,
I need to be able to select (for example), 3, and if no 3, then 2, and if no 2 then 1.
Could you explain some of your shorthand a bit? I'm not familiar with the expression ;with cte and 1[/code
Thanks,
Susan

 

[markros]

Sorry, I forgot to close code tag It should have been

;with cte as (select ID, Name, Name_Type_ID, row_number() over (partition by ID order by Name_Type_ID DESC) as RowNum from myTable)

select ID, Name, Name_Type_ID from cte where RowNum = 1

You need to read up on Common Table Expressions (CTE) and Windowing functions in SQL Server 2005.

You can check this article

http://www.setfocus.com/TechnicalArticles/Articles/sql-server-2005-tsql-3.aspx

and the articles prior to that.

 

[markros]

In addition to my previous response, you may also check

http://blogs.lessthandot.com/index.php/All/?p=528

and

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/cte-and-hierarchical-queries