robthomas09
asked on
SQL - remove unnecessary rows from table
Hello experts,
I have a SQL table that contains some data that I want to clean up and keep only the max verison number for a particular person id.
The table: STAGING
person_id version_no code
1808351 1 296.89
1808351 1 304.80
1808351 1 305.10
1808351 1 301.83
1808351 2 296.89
1808351 2 304.80
1808351 2 305.10
1808351 3 301.83
1808351 3 296.89
1808351 4 304.80
1808351 4 305.10
1808351 4 301.83
1808351 4 296.89
1808351 9 304.80
1808351 9 305.10
1808351 9 301.83
1808351 9 296.89
1808351 9 304.80
1808351 9 305.10
1808351 9 301.83
In the above table, I'd like to run a script that removed all rows except for those with the highest version_no, leaving the above example with:
person_id version_no code
1808351 9 304.80
1808351 9 305.10
1808351 9 301.83
1808351 9 296.89
1808351 9 304.80
1808351 9 305.10
1808351 9 301.83
Even selecting all of the data into a new table would work as well, something like:
SELECT t1.*
into NEW_TABLE
FROM STAGING AS t1
LEFT OUTER JOIN STAGING AS t2
ON t1.person_id = t2.person_id
AND MAX((t1.version_no < t2.version_no ))
WHERE t2.person_id_id IS NULL
Thoughts?
Thanks!
I have a SQL table that contains some data that I want to clean up and keep only the max verison number for a particular person id.
The table: STAGING
person_id version_no code
1808351 1 296.89
1808351 1 304.80
1808351 1 305.10
1808351 1 301.83
1808351 2 296.89
1808351 2 304.80
1808351 2 305.10
1808351 3 301.83
1808351 3 296.89
1808351 4 304.80
1808351 4 305.10
1808351 4 301.83
1808351 4 296.89
1808351 9 304.80
1808351 9 305.10
1808351 9 301.83
1808351 9 296.89
1808351 9 304.80
1808351 9 305.10
1808351 9 301.83
In the above table, I'd like to run a script that removed all rows except for those with the highest version_no, leaving the above example with:
person_id version_no code
1808351 9 304.80
1808351 9 305.10
1808351 9 301.83
1808351 9 296.89
1808351 9 304.80
1808351 9 305.10
1808351 9 301.83
Even selecting all of the data into a new table would work as well, something like:
SELECT t1.*
into NEW_TABLE
FROM STAGING AS t1
LEFT OUTER JOIN STAGING AS t2
ON t1.person_id = t2.person_id
AND MAX((t1.version_no < t2.version_no ))
WHERE t2.person_id_id IS NULL
Thoughts?
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There are several ways depending on which version of SQL you have. This version is very clean and easy to index.
SELECT Info.*
FROM Staging AS Info
WHERE version_no = (
SELECT TOP 1 version_no
FROM Staging AS S
ORDER BY S.version_no DESC)
ASKER