how to select rows where column value has changed

Question

I have a table in which I have few columns like below:

Cusnbr  Name  LoadNumber
 1       Z      10
 1       Z      9
 1       Z      8
 1       C      7
 1       C      6
 1       C      5
 1       B      4
 1       B      3
 1       A      2
 1       A      1

it is just for one cusnbr there are million of cusnbr like this..

I want output like below

Cusnbr  Name  LoadNumber
 1       C      7
 1       B      4
 1       A      2

For that I write below query in sql server 2008:

;With x as
 (
   Select * ,rn=Row_number() over (order by cusnbr,loadnumber) from table
 )
 select x.* from x left outer join x as y  on x.rn=y.rn+1
 and x.name<>y.name  where y.name is not null

but I am not getting the desired output in the above code I am getting last Z also which I don't want and I am getting irregular data not in the correct form in which I want

Any help will be appreciated !!

like this I want but not able to get the desired output

I use this example

Columns need to be sorted. Which columns do you sort in the example you gave? — neer, Oct 24 '17 at 12:47

score 0 · Answer 1 · answered Oct 24 '17 at 11:46

0

Though the question is not clear to me , Guessing from the output I have tried out Dense Rank . I guessed you want the record with highest LoadNumber with the same name .

 Select * from cteTrial where LoadNumber in (
 Select MAX(x.LoadNumber) as LoadNumber from (
 Select cusnbr , name , LoadNumber , DENSE_RANK() over (order by Name desc ) 
 as Dense from cteTrial) as x group by x.Dense 
 )

If you can use CTE it will produce better performance .

answered Oct 24 '17 at 11:46

Saronyo

158
7

ctetrail is x of my cte or table – Amitesh Oct 24 '17 at 12:00
cteTrial is table name I have not use CTE – Saronyo Oct 24 '17 at 12:01
I update my question there is a link I use that code – Amitesh Oct 24 '17 at 12:02
your query will give the max loadnumber data I want the latest updated date what ever the load number is – Amitesh Oct 24 '17 at 12:09

score 0 · Answer 2 · answered Oct 24 '17 at 13:12

i written the code as per expected

;With cte(Cusnbr , Name , LoadNumber)
AS
(
SELECT 1,'Z', 10 Union all
SELECT 1,'Z', 9  Union all
SELECT 1,'Z', 8  Union all
SELECT 1,'C', 7  Union all
SELECT 1,'C', 6  Union all
SELECT 1,'C', 5  Union all
SELECT 1,'B', 4  Union all
SELECT 1,'B', 3  Union all
SELECT 1,'A', 2  Union all  
SELECT 1,'A', 1
)
SELECT cusnbr, 
       NAME, 
       loadnumber 
FROM   (SELECT *, 
               Row_number() 
                 OVER( 
                   partition BY NAME 
                   ORDER BY loadnumber DESC) AS RNk, 
               Row_number() 
                 OVER( 
                   ORDER BY (SELECT 1)) - 1  AS RNO 
        FROM   (SELECT * 
                FROM   cte)dt)DT2 
WHERE  DT2.rnk = 1 
       AND rno > 0 
ORDER  BY NAME DESC

Result

cusnbr  NAME    loadnumber
-------------------------
1        C          7
1        B          4
1        A          2

why u take all value in cte is just one record their are million records — Amitesh, Oct 24 '17 at 13:52

how to select rows where column value has changed

2 Answers2