with salTable as(
select lastName, ROW_NUMBER() over (order by salesYDT desc)as rankof, SalesYDT from
SalesPerson
)
select * from salTable where rankof=2
------------------------------------------------
SELECT s.LastName,
ROW_NUMBER() OVER (ORDER BY s.SalesYDT) AS 'Row Number',
RANK() OVER (ORDER BY s.SalesYDT) AS 'Rank',
DENSE_RANK() OVER (ORDER BY s.SalesYDT) AS 'Dense Rank',
NTILE(4) OVER (ORDER BY s.SalesYDT) AS 'Quartile',
CAST(s.SalesYDT AS INT) SalesYDT, s.PostalCode
FROM
SalesPerson s
WHERE
SalesYDT <>
0;
------------------------------------------
SELECT top 20 *
FROM
SalesPerson
ORDER BY SalesYDT OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY
USE [master]
GO
/******
Object: Table [dbo].[SalesPerson] Script Date: 02/12/2013 16:52:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SalesPerson](
[lastName] [varchar](200) NULL,
[SalesYDT] [decimal](18, 2) NULL,
[PostalCode] [nchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
