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.
Author |
Topic |
MikeHasNoSQL
Starting Member
3 Posts |
Posted - 2014-08-21 : 07:37:43
|
I'm learning about the NTILE function and I was playing around with it.I've read a few different explanations of it but I don't see anything explaining why it adds rows to my results set and what these rows are.For example:Using SSMS 2014 and AdventureWorks database I ran the following:Select Distinct C.FirstName , C.LastName ,C.EmailAddressFrom Person.Contact C Inner Join Sales.SalesOrderHeader SOH On C.ContactID = SOH.ContactIDThis returns 19,119 rowsIf I run the same thing with NTILE:Select Distinct C.FirstName , C.LastName ,C.EmailAddress , NTILE(5) Over (Order by C.LastName) As 'Group'From Person.Contact C Inner Join Sales.SalesOrderHeader SOH On C.ContactID = SOH.ContactID;It returns 19,179 rows |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2014-08-21 : 08:08:17
|
I think it has to do with the DISTINCT.Your NTILE(5)... as Group is changing the result set so the DISTINCT has another count of equal rows. Too old to Rock'n'Roll too young to die. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-08-21 : 16:30:10
|
[code]WITH cteAS ( SELECT DISTINCT c.FirstName, c.LastName, c.EmailAddress FROM Person.Contact AS c INNER JOIN Sales.SalesOrderHeader AS soh ON soh.ContactID = c.ContactID)SELECT *, NTILE(5) OVER (ORDER BY LastName) As [Group]FROM cte;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
MikeHasNoSQL
Starting Member
3 Posts |
Posted - 2014-08-22 : 07:41:54
|
quote: Originally posted by SwePeso
WITH cteAS ( SELECT DISTINCT c.FirstName, c.LastName, c.EmailAddress FROM Person.Contact AS c INNER JOIN Sales.SalesOrderHeader AS soh ON soh.ContactID = c.ContactID)SELECT *, NTILE(5) OVER (ORDER BY LastName) As [Group]FROM cte;
Yup that works... If it's not too much trouble, can explain why it works that way with the CTE but not just in the Select?Thank you |
|
|
|
|
|
|
|