Hi All, I am having a task in SSIS in which I need to pull second highest record but not the MAX-1. Can I do it in the SSIS or do I need to write the SQL Statement in the OLE DB Command. Can someone please provide the idea to get the next highest record in both the ways. Thank you, VSP

If you are running SQL Server 2012, you can use sequence. CREATE SEQUENCE [dbo].[NewSeq] AS INT START WITH 1 INCREMENT BY 1 // Get next value NEXT VALUE FOR NewSeq You can read more here.

Thank you very much for your prompt response. I use SQL Server 2008 , I need a way getting the second highest through sql query or using SSIS. I can't use RANK() function as the table has few millions of records and might effect the performance. Please suggest me a way. Thank you, VSP

Another method is ROW_NUMBER in a CTE, assuming you don't care about ties. If the query is simple like the example below, performance depends on whether or not the ORDER BY column is indexed. WITH RankedValues AS ( SELECT SomeColumn ,ROW_NUMBER() OVER(ORDER BY SomeColumn) AS RankedValue FROM dbo.SomeTable ) SELECT SomeColumn FROM RankedValues WHERE RankedValue = 2; Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

ty Dan, I'll consider your suggestion and check the query performance. VSP

Hi Dan, When I use the query you provided above I am getting the same ID Values for Rank = 1, 2 . But the ID has so many values. Ty VSP

FYI, using Dan's suggestion, make sure the column you are using is the column you are ranking. Example, second highest score on a test result... you would rank with the (order by score). Alternately you could expand the ranking a bit by partitioning the results... ;with cte as(select rowno=row_number()over(Partition by student,score order by student,score) ,* from tblstudentscores) select * from cte where rowno=2 If you run the same query above but switch the partition values and the order values, you should be starkly differing result sets. Figure out which one (which order by and partition sequence) is the one you need to pull that secondary value.R, J

Can you post your table DDL and query? Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Can you post your table DDL and query? Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Hi Dan, I have used the DENSE_RANK instead of ROW_NUMBER then I got the record what I am looking for. May be my question was not clear in the thread but thank you very much for guiding me in the right direction. Thank you, VSP