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
 NTILE question

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.EmailAddress
From Person.Contact C
Inner Join Sales.SalesOrderHeader SOH On C.ContactID = SOH.ContactID


This returns 19,119 rows


If 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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-21 : 16:30:10
[code]WITH cte
AS (
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
Go to Top of Page

MikeHasNoSQL
Starting Member

3 Posts

Posted - 2014-08-22 : 07:41:54
quote:
Originally posted by SwePeso

WITH cte
AS (
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
Go to Top of Page
   

- Advertisement -