Avoid duplicate rows by keep same order in SQL Server

Question

I am looking for another solution for my sql query result.

My query below returns

SELECT 
    M.Mroutes_srno, M.Mroutes_loginid 
FROM
    Mroutes M
LEFT JOIN 
    MAccSanctioningLimit Limit ON M.mroutes_role = Limit.sac_type
                               AND M.Mroutes_moduleno = Limit.sac_moduleno
                               AND M.Mroutes_companycd = Limit.company_cd
WHERE
    M.mroutes_deptcd = 'BKAF'
    AND M.Mroutes_moduleno = 145
    AND M.mroutes_role NOT IN ('MIS', 'CFO', 'MD')

Output:

Mroutes_srno    Mroutes_loginid
--------------------------------
1   --------    AIJ1546
2   --------    BCS8021
3   --------    AIJ1546
4   --------    BCS8021
5   --------    venkatesh
6   --------    151N1636

But, after removing duplicate rows it result like below.

SELECT  
    M.Mroutes_loginid  
FROM
    Mroutes M
LEFT JOIN 
    MAccSanctioningLimit Limit ON M.mroutes_role = Limit.sac_type
                               AND M.Mroutes_moduleno = Limit.sac_moduleno
                               AND M.Mroutes_companycd = Limit.company_cd
WHERE 
    M.mroutes_deptcd = 'BKAF'
    AND M.Mroutes_moduleno = 145
    AND M.mroutes_role NOT IN ('MIS', 'CFO', 'MD')
GROUP BY
    M.Mroutes_loginid

Output:

Mroutes_loginid
---------------
151N1636
AIJ1546
BCS8021
venkatesh

I need to keep the query in same order.

Add an order by clause to your query. Something like "order by M.Mroutes_srno ASC". — Rahul Vijay Dawda
– Rahul Vijay Dawda, Commented Feb 26, 2016 at 4:38
It's not clear what is your question, or what you are trying to achieve. You could use order by Mroutes_loginid, if what you want is a consistent order. — Yoav Gur
– Yoav Gur, Commented Feb 26, 2016 at 4:39
I need to keep the query in same order. - in that case, you must explicitly specify an ORDER BY clause in your SELECT — marc_s
– marc_s, Commented Feb 26, 2016 at 5:47
yes right .. there is answer with order by min(M.Mroutes_srno) — Ajay K
– Ajay K, Commented Feb 26, 2016 at 5:55

Giorgi Nakeuri · Accepted Answer · 2016-02-26 04:54:45Z

1

You need to use row_number window function:

;WITH cte AS(
SELECT M.Mroutes_srno,
       M.Mroutes_loginid,
       ROW_NUMBER() OVER(PARTITION BY M.Mroutes_loginid ORDER BY M.Mroutes_srno) rn 
from Mroutes M
left JOIN MAccSanctioningLimit  Limit on M.mroutes_role =Limit.sac_type
    and  M.Mroutes_moduleno = Limit.sac_moduleno
    and M.Mroutes_companycd = Limit.company_cd
where M.mroutes_deptcd ='BKAF'
and M.Mroutes_moduleno =145
and M.mroutes_role NOT in ('MIS', 'CFO', 'MD'))

SELECT Mroutes_srno, Mroutes_loginid
FROM cte 
WHERE rn = 1
ORDER BY Mroutes_srno

Or you can order by aggregation like:

SELECT  M.Mroutes_loginid  
from Mroutes M
left JOIN MAccSanctioningLimit  Limit on M.mroutes_role =Limit.sac_type
    and  M.Mroutes_moduleno = Limit.sac_moduleno
    and M.Mroutes_companycd = Limit.company_cd
where M.mroutes_deptcd ='BKAF'
and M.Mroutes_moduleno = 145
and M.mroutes_role NOT in ('MIS', 'CFO', 'MD')
group by M.Mroutes_loginid
order by min(M.Mroutes_srno)

edited Feb 26, 2016 at 4:54

answered Feb 26, 2016 at 4:49

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ajay K Over a year ago

thanks .. i used order by aggregation option its work

Moumit · Accepted Answer · 2016-02-26 05:52:49Z

0

It is happening because .. it might be possible that .. your delete query which deleting the duplicate record from Mroutes table is deleting the first occurrence of AIJ1546 values .. which leading 151N1636 to come first in result set ...

Try to skip the first occurrence row of duplicate value .. your probelm will be solved..

Note: When you did not specify any order by clause in select query it by-default choose the primary key to sort the result set..

answered Feb 26, 2016 at 5:52

Moumit

9,8709 gold badges59 silver badges66 bronze badges

Collectives™ on Stack Overflow

Avoid duplicate rows by keep same order in SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related