February 12, 2010 at 1:35 pm
Following is Sample data of a table, I have to write a view where I need to exclude the row which seems to be duplicate row with col2 having value 'Dup1' but col6 is not same for the row.
Can some one tell me what select query would be that only row 6 is picked and row 5 shd not.
Col1Col2 Col3Col4Col5Col6 Col7
16170964Test A25907636149820100207NULL
26173577Test M32081144499820100207NULL
36172998Test C25168935399820100207NULL
46172230Test D38564948999820100207NULL
56152082Dup1999995313482010010651459133
66152082Dup1999995313482010010651466385
76164820Test Y4026326417982010021051729991
86173037Test J6222467301982010012651822310
96173748Test K2740483766982010012751822397
106173016Test M2982544012732010012751822397
116165180Test N2911383940482010020151823044
126174486Test L3562464601482010020151823044
Thanks in advance.
/**A strong positive mental attitude will create more miracles than any wonder drug**/
February 12, 2010 at 2:05 pm
Dupe = ROW_NUMBER() OVER(PARTITION BY Col1 Col2 Col3 Col4 Col5 Col6 ORDER BY Col1 Col2 Col3 Col4 Col5 Col6 Col7)
If you set up your sample as a CREATE TABLE statement with appropriate INSERTs for your data, then folks can test against it straight way, without spending 10 mins turning your data into a table.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 15, 2010 at 7:43 am
Thank you that served the purpose. I'll provide the Insert scripts in future, thanks for it.
/**A strong positive mental attitude will create more miracles than any wonder drug**/
August 2, 2010 at 2:46 pm
ChrisM@home (2/12/2010)
Dupe = ROW_NUMBER() OVER(PARTITION BY Col1 Col2 Col3 Col4 Col5 Col6 ORDER BY Col1 Col2 Col3 Col4 Col5 Col6 Col7)If you set up your sample as a CREATE TABLE statement with appropriate INSERTs for your data, then folks can test against it straight way, without spending 10 mins turning your data into a table.
Thank you Chris for this suggestion. Much more elegant than the other solutions I have seen on this question, and it does not require the creation of a temp table to eliminate duplicates.
August 2, 2010 at 3:04 pm
You can also just use a grouping function in a derived table:
select sq.col1, t.col2, t.col3, t.col4, t.col5, t.col6, t.col7
from
(select MIN(col1) as col1
from #test
group by col2, col3, col4, col5, col6) sq
join #test t
on t.col1 = sq.col1
order by sq.col1
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply