SQL Code for Creating a Hierarchy

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

General SQL Server Forums

New to SQL Server Programming

SQL Code for Creating a Hierarchy

Author

Topic

Cal88
Starting Member

3 Posts

Posted - 2014-07-20 : 19:18:42

Hello,

Hoping to get some help on how to create a hierarchy using SQL.

I have a database with ID numbers that are duplicated based on customer preferences. For example, an ID number can fall into multiple categories

ID# 001 in section A
ID# 001 in section B
ID# 001 in section C

The same ID Number is in section A, B and C.

The hierarchy is that A is at the top and C is at the bottom. So, if my ID number is in all 3 sections (A, B & C) I only want to count it as falling into section A as that is at the top of the rating scale.

Any suggestions on a code that could help with this would be much appreciated.

Thanks!
Cal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-07-21 : 00:05:52

[code]
SELECT Category,COUNT(ID) AS Cnt
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Category) AS Seq
FROM Table
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources