mardi 4 août 2015

Select all second highest values only from temp table

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