• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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!
0
robthomas09
Asked:
robthomas09
2 Solutions
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now