CODEWITHSUNDEEP
sqlsql-servert-sql
Pindakaas
Pindakaas

Reputation: 4439

how to retrieve less rows?

i have this table:

USE [testing]
GO
/****** Object:  Table [dbo].[table1]    Script Date: 1/02/2014 9:35:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table1](
    [id] [int] NOT NULL,
    [date] [datetime] NULL,
    [rate] [decimal](18, 0) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[table1] ([id], [date], [rate]) VALUES (1, CAST(0x0000A2A600000000 AS DateTime), CAST(12 AS Decimal(18, 0)))
GO
INSERT [dbo].[table1] ([id], [date], [rate]) VALUES (2, CAST(0x0000A2A700000000 AS DateTime), CAST(2 AS Decimal(18, 0)))
GO
INSERT [dbo].[table1] ([id], [date], [rate]) VALUES (3, CAST(0x0000A2A800000000 AS DateTime), CAST(3 AS Decimal(18, 0)))
GO
INSERT [dbo].[table1] ([id], [date], [rate]) VALUES (4, CAST(0x0000A2A900000000 AS DateTime), CAST(56 AS Decimal(18, 0)))
GO

I would like to return the difference in rates between 2 rows, and return 3 rows :

-10
1
43

What do I have to change on this query?

select t1.rate-t2.rate
from table1 t1
join table1 t2 on 
t1.id> t2.id
where (t2.id-t1.id) <=1

Upvotes: 0

Views: 60

Answers (2)

Hart CO
Hart CO

Reputation: 34784

You were close, assuming ID's are in the order you want and no gaps: 

SELECT t1.rate-t2.rate
FROM Table1 t1
JOIN Table1 t2 
 ON t1.id = t2.id + 1

If using SQL Server 2012 you can make use of the LEAD() function:

SELECT rate - LEAD(rate) OVER (ORDER BY id)
FROM Table1

That returns a 4th row with NULL, so could use a cte/subquery to limit to the 3 rows with populated values.

Note, if the ID's are not gauranteed to be in the order you want, or if there are gaps between some id's you should use the ROW_NUMBER() function (if not using the LEAD() version with explicit ORDER BY):

WITH cte AS (SELECT *,ROW_NUMBER() OVER(ORDER BY id) RN
              FROM Table1)
SELECT t1.rate-t2.rate
FROM cte t1
JOIN cte t2 
 ON t1.RN = t2.RN + 1

Demo of all 3: SQL Fiddle

Upvotes: 3

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

SELECT
    T1.rate - T2.rate as rateDiff
FROM table1 T1
  JOIN table1 T2
     ON T1.id = T2.Id + 1

Upvotes: 1

Related Questions