remove duplicates from a row and keep the order sql

Question

I have a table as follows

SeriesVariables_ID     SeriesVariables_Label     SeriesVariables_Value    Series_ID
143                    Batch Number              Test20PassAll            28
144                    SerialNo                  SIMTEST                  28
145                    Seg Pull Date             20/1/2014                28
146                    Seg Pull Time             101010                   28
147                    Batch Number              Test20PassAll            28
148                    SerialNo                  SIMTEST                  28
149                    Seg Pull Date             20/1/2014                28
150                    Seg Pull Time             101010                   28
151                    Batch Number              Test20PassAll            7
152                    SerialNo                  SIMTEST2                 7
153                    Seg Pull Date             20/2/2014                7
154                    Seg Pull Time             202020                   7

What I would like is to keep the order of the table and remove the duplicates. The result will look something like this

SeriesVariables_ID     SeriesVariables_Label     SeriesVariables_Value    Series_ID
143                    Batch Number              Test20PassAll            28
144                    SerialNo                  SIMTEST                  28
145                    Seg Pull Date             20/1/2014                28
146                    Seg Pull Time             101010                   28
151                    Batch Number              Test20PassAll            7
152                    SerialNo                  SIMTEST2                 7
153                    Seg Pull Date             20/2/2014                7
154                    Seg Pull Time             202020                   7

Any help is greatly appreciated!

It doesn't matter the order in your database. Because the order should be driven by your SQL queries. Just remove the duplicated elements and use ORDER BY in your SQL queries to get the order you want — TheEwook, Mar 21 '14 at 12:35
Hi Thanks for your response. How do i remove the duplicate items? — Abe, Mar 21 '14 at 12:40
I'm sure this question has been answered multiple times on stackoverflow. — TheEwook, Mar 21 '14 at 12:46

score 0 · Accepted Answer · edited May 23 '17 at 12:28

0

If you are running SQL, I've found the answer at https://stackoverflow.com/a/18949/1781207 to be very helpful for these situations. It will also keep the lowest SeriesVariables_ID as you want.

For example, in your code it should be (where "MyTable" is the name of your table)

DELETE MyTable 
FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(SeriesVariables_ID) as RowId, SeriesVariables_Label, SeriesVariables_Value, Series_ID
   FROM MyTable 
   GROUP BY SeriesVariables_Label, SeriesVariables_Value, Series_ID
) as KeepRows ON
   MyTable.SeriesVariables_ID = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

You can test to see the selected rows by running the inner select statement alone

SELECT MIN(SeriesVariables_ID) as RowId, SeriesVariables_Label, SeriesVariables_Value, Series_ID
FROM MyTable
GROUP BY SeriesVariables_Label, SeriesVariables_Value, Series_ID

edited May 23 '17 at 12:28

Community

1
1

answered Mar 21 '14 at 12:36

Jem

4,313
2
18
20

1

This is specific for sqlserver. We don't know for sure if the op runs sql server. – TheEwook Mar 21 '14 at 12:38
Hi Jem, thanks for your response. Can I have a sample please. – Abe Mar 21 '14 at 13:44
Hi Jem, Thanks for that. This is exactly what I was looking for. You are really a Jem. Thanks again!! – Abe Mar 21 '14 at 14:26

remove duplicates from a row and keep the order sql

1 Answers1