SQL Server : multiple rows with same column values except one

Question

I have data like these:

name_text   ----------c_num---d_type-----unique_id----------------------v_text--r_ticket_id
29311875_FAD_V100.doc 3560709 Contract 21DABDDF49BA41289F1905D1D6B79ABF 2,01    28600206
29311875_FAD_V100.doc 3560709 Contract 21DABDDF49BA41289F1905D1D6B79ABF 2,01    28600240
Copy.docx             3560715 Guide    9D06F8EFF4EC4A2D862F5A0DB3BA357B 2       28600219

I want to select just one row which has the bigger value of r_ticket_id column, if name_text, c_num, d_type, unique_id, v_text columns values are equal.

In these case, with select statement just this columns should be fetched.

name_text   ----------c_num---d_type-----unique_id----------------------v_text--r_ticket_id
29311875_FAD_V100.doc 3560709 Contract 21DABDDF49BA41289F1905D1D6B79ABF 2,01    28600240
Copy.docx             3560715 Guide    9D06F8EFF4EC4A2D862F5A0DB3BA357B 2       28600219

What is the exact SQL query that will do this job?

are there any possibilities that a record may have same unique id but have different values on every column? — John Woo
– John Woo, Commented Oct 21, 2012 at 14:48

John Woo · Accepted Answer · 2012-10-21 14:50:35Z

5

This may be helpful when a record may have the same unique id but have different values on every column. Make use of ROW_NUMBER and window function. I guess they are already present on sql-server 2005.

SELECT name_text,c_num,d_type,unique_id,v_text,r_ticket_id
FROM
    (
        SELECT  name_text, c_num, d_type, unique_id, v_text, r_ticket_id,
                ROW_NUMBER() OVER (PARTITION BY unique_id 
                                   ORDER BY r_ticket_id DESC) rn
        FROM tableName
    ) x
WHERE x.rn = 1

SQLFiddle Demo

edited Oct 21, 2012 at 14:50

answered Oct 21, 2012 at 14:38

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

1 Comment

Mehmet Ali Sert Over a year ago

Thank you for this reasonable and instructive answer.

billinkc · Accepted Answer · 2012-10-21 14:43:37Z

2

In SQL, we have GROUP BY operation to allow us to consolidate repeated rows. In your question, you want to roll up all the same values except for the r_ticket_id. For that one, you wish to find the biggest value which in SQL translates to the MAX function.

SELECT
    name_text
,   c_num
,   d_type
,   unique_id
,   v_text
,   MAX(r_ticket_id) AS max_r_ticket_id
FROM
    dbo.MyTableNameHere
GROUP BY
    name_text
,   c_num
,   d_type
,   unique_id
,   v_text

edited Oct 21, 2012 at 14:43

answered Oct 21, 2012 at 14:38

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

Collectives™ on Stack Overflow

SQL Server : multiple rows with same column values except one

2 Answers 2

SQLFiddle Demo

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