Another ROW_NUMBER() request - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Another ROW_NUMBER() request

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-16 : 05:19:23

I am querying the system views to obtain all the Foreign Keys (name, columns, parent name, parent columns, etc)for a table. Some tables have more than 1 FK so I need to rank them in order:

Here is the query:


SELECT FKcols.TABLE_NAME, FKcols.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fks 
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FKcols ON FKcols.CONSTRAINT_NAME = fks.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pks ON pks.CONSTRAINT_NAME = fks.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PKcols ON PKcols.CONSTRAINT_NAME = pks.CONSTRAINT_NAME
	AND FKcols.ORDINAL_POSITION = PKcols.ORDINAL_POSITION

Here is the result set that I want (so each FK constraint is assigned a different number only for each table):


1	Item			FK_Item_Header
1	Item			FK_Item_Header
1	ItemDiscount	FK_ItemDiscount_Header
1	ItemDiscount	FK_ItemDiscount_Header
2	ItemDiscount	FK_ItemDiscount_Item
1	Tender			FK_Tender_Header
1	Tender			FK_Tender_Header

Also, does anyone know of any good material that explains the ROW_NUMBER function to someone that does not have a mathematical background - i.e. so that a child could understand...)
Thanks

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 05:23:18

[code]
SELECT DENSE_RANK() OVER (PARTITION BY FKcols.TABLE_NAME ORDER BY FKcols.CONSTRAINT_NAME) AS Seq,
FKcols.TABLE_NAME, FKcols.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fks
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FKcols ON FKcols.CONSTRAINT_NAME = fks.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pks ON pks.CONSTRAINT_NAME = fks.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PKcols ON PKcols.CONSTRAINT_NAME = pks.CONSTRAINT_NAME
AND FKcols.ORDINAL_POSITION = PKcols.ORDINAL_POSITION
[/code]

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-16 : 05:42:56

Row_Number Returns the sequential number of a row within a partition of a result set

In Below example col1 is used to partition the resultset

select col1,col2,ROW_NUMBER(PARTITION BY col1 ORDER BY col2) as rownumber from yourtable

o/p
col1 col2 rownumber
1 4 1
1 5 2
1 6 3
2 8 1
2 9 2

Jai Krishna

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-12-16 : 07:00:16

Thank you!

Hearty head pats

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-16 : 07:10:42

Welcome

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources