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!
robthomas09Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
DELETE FROM STAGING
WHERE version_no < (SELECT MAX(version_no) FROM STAGING)
0
Saurabh Singh TeotiaCommented:
You can use the following query..What it will give you is the max value of each person which you can use to insert into new table like that way you are looking for...

select tb1.person_id,tb1.version_no,tb1.code
from stating tb1
inner join 
(select person_id,max(version_no) as vn
from STAGING t1
group by person_id) tb2 on tb1.person_id=tb2.person_id and tb1.version_no=tb2.vn

Open in new window


Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robthomas09Author Commented:
Thanks!
0
Mark ElySenior Coldfusion DeveloperCommented:
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)

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.