I looked through the questions here but didn't find one that suited my case.
I'm trying to write a query that will output the difference between rows
Here is a table:
ITEM CYCLES
--------------------
itemA 5
itemA 17
itemA 20
itemA 22
itemB 26
itemB 30
itemB 37
it is actually obtained by a query, and with an order by (item, cycles)
here is what I'd like the query to give me:
itemA 12
itemA 3
itemA 2
itemB 4
itemB 7
I have absolutely no idea how to proceed in SQL. Is it even possible ? or do I have to write a function ?
*****************************EDIT*********************************
I appologize for the lack of precision, and even some absurd mistakes. I was rushing out and wrote hastily :/
I'm analyzing item failures, and need to output cycles between failures.
And actually looking at it today I don't understand why I put that middle column (A,B,C...) which I don't have in my table.
And indeed, I don't need to output zero values, but there shouldn't be any anyway.
I'll try the provided solutions and will get back; thanks for the answers !
Here's an updated solution, based on the changes to your question. Note that you'll need to change Qry
to the name of your query:
SELECT Qry.Item, Qry.Cycles - (SELECT TOP 1 Cycles FROM Qry AS Q
WHERE Qry.Item=Q.Item
AND Q.Cycles < Qry.Cycles
ORDER BY Q.Cycles DESC) AS Diff
FROM Qry
WHERE (SELECT TOP 1 Cycles FROM Qry AS Q
WHERE Qry.Item=Q.Item AND Q.Cycles < Qry.Cycles) Is Not Null
ORDER BY Qry.Item, Qry.Cycles;
This produces the following output:
Item Diff
itemA 12
itemA 3
itemA 2
itemB 4
itemB 7
I assume that the 6
in your sample output was a typo, since 30 - 26 = 4
.