Eliminate rows with duplicate values in a certain column, select one based on another column
I have a table with many columns. The table includes an address_ID column and a type_ID column.
There are some duplicates in the address_ID column, which become apparent when I sort on that column.
I want to create a new table which has no duplicate address_ID rows.
The order of picking which row to select, when there are multiple rows with the same address_ID, goes like this:
1. Select row with type_ID 2869
2. If none, then select row with type_ID 4162
3. If none, select row with type_ID 2868
4. If none, select row with type_ID 4919
2. If none, give up and just select any row
What SELECT statement would give me this result?
There are some duplicates in the address_ID column, which become apparent when I sort on that column.
I want to create a new table which has no duplicate address_ID rows.
The order of picking which row to select, when there are multiple rows with the same address_ID, goes like this:
1. Select row with type_ID 2869
2. If none, then select row with type_ID 4162
3. If none, select row with type_ID 2868
4. If none, select row with type_ID 4919
2. If none, give up and just select any row
What SELECT statement would give me this result?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jim Horn
<above comment was edited after original submission
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi..
Can you try this
INSERT INTO TableB(Col1, Col2, Col3, ... , Coln)
SELECT DISTINCT A.Col1, A.Col2, A.Col3, ... , A.Coln
FROM TableA A
LEFT JOIN TableB B
ON A.address_ID = B.address_ID
WHERE B.address_ID IS NULL
Can you try this
INSERT INTO TableB(Col1, Col2, Col3, ... , Coln)
SELECT DISTINCT A.Col1, A.Col2, A.Col3, ... , A.Coln
FROM TableA A
LEFT JOIN TableB B
ON A.address_ID = B.address_ID
WHERE B.address_ID IS NULL
ASKER
Thank you everyone.
Paul's code worked out of the box. (The first two had problems, though they appear to be similar.)
Paul's code worked out of the box. (The first two had problems, though they appear to be similar.)