Add last two values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mcolson - 26 Jul 2007 20:16 GMT

I am trying to create a stored procedure that will add the last two
entries from a column. If I had a column named box, I would want to
add the two values in box, for which my ID column is Max and Max-1.

Is there anyone who can help me with this?

Matt

Reply to this Message

Jason Lepack - 26 Jul 2007 20:21 GMT

select
sum(box)
from (
select top 2
box
from
your_table
order by
id desc)

Cheers,
Jason Lepack

> I am trying to create a stored procedure that will add the last two
> entries from a column. If I had a column named box, I would want to
[quoted text clipped - 3 lines]
>
> Matt

Reply to this Message

mcolson - 26 Jul 2007 20:43 GMT

> select
> sum(box)
[quoted text clipped - 16 lines]
>
> > Matt

so i should
SET box = select
sum(box)
from (
select top 2
box
from
batchdaa
order by
IDnum desc)
?

Reply to this Message

mcolson - 26 Jul 2007 21:28 GMT

> > select
> > sum(box)
[quoted text clipped - 28 lines]
> IDnum desc)
> ?

ahh the power of parenthesis

box = (SELECT SUM(box) FROM batchdata WHERE IDnum >= (SELECT
(MAX(IDnum)-1) FROM batchdata))

I had to make sure the parenthesis were all in the correct spot.
Especially since I am doing this for 4 columns and there was a comma
at the end

Reply to this Message

M A Srinivas - 30 Jul 2007 10:13 GMT

> > > select
> > > sum(box)
[quoted text clipped - 39 lines]
>
> - Show quoted text -

You need to make sure the following . (assuming you don't want to sum
duplicate values )

1. There are no duplicate maximum values
2. A row with maximum - 1 exists and this also should not have any
duplicates .
you may be looking for next to maximum ( not max - 1 ) . ie

Your values should not have gaps
8
6
5
3

In this case max = 8 , max - 1 = 7 does not exist

Reply to this Message