Using T-SQL (SQL Server 2008 R2), I'm trying to list only the rows with the second highest value in a particular column from a temp table and then place the results into a new temp table. The PK is the ID, which can have increasing version numbers and then unique codes.
Example:
ID | Name| Version | Code
------------------------
1 | A | 1 | 10
1 | A | 2 | 20
1 | A | 3 | NULL
2 | B | 1 | 40
2 | B | 2 | 50
2 | C | 1 | 60
The desired outcome of the query is
ID | Version | Code
------------------------
1 | 2 | 20
2 | 1 | 40
To achieve this I need the below query to be adapted to pull the second highest value as long as the result gives a version number greater than 1. These results come from a temp table and will then be placed into a final results temp table. EDIT: Please note this will be applied over 33000 rows of data so I would prefer something neater than INSERT VALUES. Thanks.
Current query:
SELECT
ID
,Version
,Code
INTO
#table2
FROM
#table1
SELECT *
FROM #table2
WHERE Version > 1
ORDER BY ID asc
DROP TABLE #table1
DROP TABLE #table2
I have tried running the where clause WHERE Version < (SELECT MAX(VERSION) FROM #TABLE 2) but this has no effect, presumably due to the unique code values and in any case wouldn't work where I have more than 3 Versions.
Ideas would be gratefully received.
Thanks in advance.
Aucun commentaire:
Enregistrer un commentaire