One record per ID with multiple record types

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

One record per ID with multiple record types

Author

Topic

rjejcsb
Starting Member

4 Posts

Posted - 2012-08-29 : 15:55:42

I am new to SQL and I'm trying to write a query to do the following.

I have ONE table containing record types and IDs. You can have duplicate and multiple types per ID.

Type ID
History 1
History 1
Geography 1
Geography 2
French 2
French 3
English 3
English 4
History 4
History 4

I want to create one record per ID with a 'Type Hierarchy' of History, Geography, All Others. So in the above example, I am looking for the following:

History 1
Geography 2
French 3
History 4

In other words give me all history records, if an ID does not have history give me the geography records, and for the IDs without either give me one of any record type.

Any ideas?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-29 : 16:13:25

is there a table where hierarchy levels are defined? like History,Geograpy,French, etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rjejcsb
Starting Member

4 Posts

Posted - 2012-08-29 : 16:36:19

No. These are just types and the user wants a particular type listed on the view based on her hierarchy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-29 : 17:20:43

quote:
Originally posted by rjejcsb

No. These are just types and the user wants a particular type listed on the view based on her hierarchy.

so is priority passed as a parameter value from user? ie history has to be considered first followed by geography etc
it can vary between users right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rjejcsb
Starting Member

4 Posts

Posted - 2012-08-30 : 10:33:32

No, right now this is just for one user and is not being created to allow for other hierarchies.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:54:23

[code]
SELECT [Type],ID
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE [Type] WHEN 'History' THEN 1 WHEN 'Geography' THEN 2 WHEN 'French' THEN 3 ELSE 4 END) AS Seq
FROM table
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rjejcsb
Starting Member

4 Posts

Posted - 2012-08-30 : 12:09:28

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 15:47:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources