Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Filter duplicate rows based on a field

Tags:

sql

sql-server

I have several records (bills) that are basically duplicates of each other, except for one field, which represents the language that bill is in.

For example:

ID,BillID,Account,Name,Amount,Lang
1,0001,abcd,John Smith,10.99,ENG
2,0002,qwer,Jane Doe,9.99,ENG
3,0001,abcd,John Smith,10.99,SPA
4,0003,abcd,John Smith,4.99,CHI

All fields are strings, except ID, which is an autonumber.

In my SQL select, I have

SELECT * 
FROM Bills 
WHERE Account='abcd'

and it returns 3 rows in total, but 2 rows for the same bill. I need to return unique bills for a specific account. So in the scenario above, I want to retrieve 2 bills with billID 0003 and either SPA or ENG version of 0001, but not both.

What would by query be?

Thank you

EDIT: I cannot rely on a specific language always being there. For example, I cannot say SELECT * FROM Bills WHERE Account='abcd' AND Lang='ENG' because sometimes a bill may be only in one language, which is not ENG, and sometimes may be in several languages in any combination.

like image 435
George Avatar asked Jun 20 '13 18:06

George


2 Answers

Probably the easiest way would be to use ROW_NUMBER and PARTITION BY

SELECT * FROM (
   SELECT b.*,
   ROW_NUMBER() OVER (PARTITION BY BillID ORDER BY Lang) as num
   FROM Bills b
   WHERE Account = 'abcd'
) tbl
WHERE num = 1
like image 164
Eric Petroelje Avatar answered Oct 02 '22 21:10

Eric Petroelje


select
ID,BillID,Account,Name,Amount,max(Lang)
FROM Bills 
WHERE Account='abcd'
group by BillID,Account,Name,Amount;

Given that you are not giving priority to any specific language if there is same bill in multiple languages. The above query will work perfect.

EDIT : Removed "ID" from group by. @Phil You are right..!!

like image 41
cosmos Avatar answered Oct 02 '22 20:10

cosmos