how to remove duplicates row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to remove duplicates row

Author

Topic

abc123
Starting Member

47 Posts

Posted - 2009-02-18 : 06:56:35

I have one query which is returning following rows

here B.col2 is the primary key for tbl2

SELECT DISTINCT A.col1, B.col2, B.col3,
B.col4, B.col5, A.col6
FROM tbl1 A,
tbl2 B
WHERE ---
---
--

ORDER BY B.col2

col1 col2 col3 col4 col5 col6

FVA 0041918 NULL 5309980 001 3505
FVA 0048652 NULL 5301783 001 3505
PRINFLEX 0053316 NULL 5055284 001 3309
PRINFLEX 0063334 NULL 5030806 002 3309
VA2004 0065456 NULL 5304522 001 3512
VA2004 0065654 NULL 5304524 001 3512
VA2004 0065654 NULL 5304214 001 3512
VA2004 0065654 NULL 5304525 001 3512

Here col1 and col2 for (VA2004,0065456) this combination , query is returning
multiple rows as col4 have different values.

but I need only 1st row for this combination.

it means o/p should be

col1 col2 col3 col4 col5 col6

FVA 0041918 NULL 5309980 001 3505
FVA 0048652 NULL 5301783 001 3505
PRINFLEX 0053316 NULL 5055284 001 3309
PRINFLEX 0063334 NULL 5030806 002 3309
VA2004 0065456 NULL 5304522 001 3512

SO plz tell me how I can remove the duplicates from above query

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-18 : 07:13:59

try like this
select * from tablea e join tableb d on d.col1= e.col1 where condt........ order by

why u have use the cross join if there is any same column the use inner join

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-02-18 : 07:29:43

Have you tried creating the query using a GROUP BY Function instead of the distinct function

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 07:30:34

quote:
Originally posted by abc123

I have one query which is returning following rows

here B.col2 is the primary key for tbl2

Can you format the query,data etc you have posted. It makes it easier for us to understand your problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 08:52:27

quote:
Originally posted by abc123

I have one query which is returning following rows

here B.col2 is the primary key for tbl2

SELECT DISTINCT A.col1, B.col2, B.col3,
B.col4, B.col5, A.col6
FROM tbl1 A,
tbl2 B
WHERE ---
---
--

ORDER BY B.col2

col1 col2 col3 col4 col5 col6

FVA 0041918 NULL 5309980 001 3505
FVA 0048652 NULL 5301783 001 3505
PRINFLEX 0053316 NULL 5055284 001 3309
PRINFLEX 0063334 NULL 5030806 002 3309
VA2004 0065456 NULL 5304522 001 3512
VA2004 0065654 NULL 5304524 001 3512
VA2004 0065654 NULL 5304214 001 3512
VA2004 0065654 NULL 5304525 001 3512

Here col1 and col2 for (VA2004,0065456) this combination , query is returning
multiple rows as col4 have different values.

but I need only 1st row for this combination.

it means o/p should be

col1 col2 col3 col4 col5 col6

FVA 0041918 NULL 5309980 001 3505
FVA 0048652 NULL 5301783 001 3505
PRINFLEX 0053316 NULL 5055284 001 3309
PRINFLEX 0063334 NULL 5030806 002 3309
VA2004 0065456 NULL 5304522 001 3512

SO plz tell me how I can remove the duplicates from above query

sorry your explanation doesnt match data posted. in posted data you dont have duplicates for (VA2004,0065456) but only for (VA2004, 0065654). If your attempt was to get 1st row out of this, you need to specify the rule for determining first row. without specify with respective to another column(s), there's no concept of first and last in sql

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-18 : 13:50:55

If I'm understanding your problem correctly, NeilG and visakh16 are both suggesting essentially the same solution. visakh16 also points out that your data doesn't quite match your explanation, so let's assume that the last 4 rows in your result set are all supposed to be VA2004 0065456. Let's also assume that the "rule for determining first row" that visakh16 refers to above is that you want to keep the minimum value for col4, and ignore rows with a greater col4 value.

The GROUP BY function that NeilG suggests would return 1 row for each of the values in the columns that you are grouping by, and return an aggregate value for any columns you do not group by. So you want to GROUP BY col1, col2, col3, col5, col6, and choose only the MIN(col4) value for your result.

Using your original formatting, try this:

SELECT A.col1, B.col2, B.col3,
min(B.col4), B.col5, A.col6
FROM tbl1 A,
tbl2 B
WHERE ---
---
--
GROUP BY A.col1, B.col2, B.col3,
B.col5, A.col6

ORDER BY B.col2

Results should be:
FVA 0041918 NULL 5309980 001 3505
FVA 0048652 NULL 5301783 001 3505
PRINFLEX 0053316 NULL 5055284 001 3309
PRINFLEX 0063334 NULL 5030806 002 3309
VA2004 0065456 NULL 5304522 001 3512
(... nothing more...)

Hope this helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources