CODEWITHSUNDEEP
sql-server-2008t-sqlrow-number
Gansun
Gansun

Reputation: 301

How to order by length inside WITH clause SQL Server 2008?

Order by Length is not working inside the WITH clause.

;WITH Invalids AS
(
    SELECT TOP 1 WITH TIES
        GroupNumber, 
        RTRIM(Address1) Address1, 
        RTRIM(Postcode) Postcode
    FROM dbo.LoadData WHERE GroupNumber NOT IN
    (
        '000','016','018','025','044','048','049','053','054','055','060','064','067','069','071','073'
    )
    ORDER BY ROW_NUMBER() OVER (PARTITION BY GroupNumber ORDER BY LEN(Address1) DESC, LEN(Postcode) DESC )
)
UPDATE dbo.LoadData
SET 
    Valid = 0, 
    Reason = Reason + 'Bad address; '
WHERE GroupNumber = 
(
    SELECT GroupNumber FROM Invalids WHERE LEN(Address1) = 0 OR LEN(Postcode) = 0

Upvotes: 1

Views: 9299

Answers (1)

Jeremy Pridemore
Jeremy Pridemore

Reputation: 1995

TOP... WITH TIES is going to give you everything that has a matching value in the ORDER BY clause. Because your 'ORDER BY' clause is just a row number that is partitioned by GroupNumber, then every GroupNumber that has at least one record will have one of those records with a row number of 1. Therefore you will return 1 record for every GroupNumber. MSDN also says "The returned order of tying records is arbitrary. ORDER BY does not affect this rule."

I'm going to guess that your 'ORDER BY' in the CTE should simply be:

ORDER BY GroupNumber, LEN(Address1) DESC, LEN(Postcode) DESC

If so, then you would get the lowest GroupNumber, and the longest value for Address1, with the longest value for Postcode, and any duplicates.

On the other hand, if you do want the longest address and postal code for every GroupNumber, then you should already be getting that and I would ask for more information.

Upvotes: 5

Related Questions