find the first row of ordered records that sum is less than a cert

6 Jun 2006 9:25 PM

nick

I have a table ordered by column c1

c1 c2
-- --
a 10
b 20
c 30
d 80
e 1000

How to locate the first one with sum(c2) < 100? should be 'c' in the example.
Hope not use cursor.

6 Jun 2006 9:31 PM

Tom Moreau

Try:

select top 1
    x.*
from
(
    select
        t1.c1, t1.c2,
            (select sum (t2.c2) from MyTable t2
                where t2.c1 <= t1.c1) as RunningTotal
    from
        MyTable t2
) as x
where
    RunningTotal < 100
order by
    RunningTotal desc

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..

"nick" <n***@discussions.microsoft.com> wrote in message
news:5831171E-0C25-4135-83B0-B7334969A85D@microsoft.com...

I have a table ordered by column c1

c1 c2
-- --
a 10
b 20
c 30
d 80
e 1000

How to locate the first one with sum(c2) < 100? should be 'c' in the
example.
Hope not use cursor.

6 Jun 2006 9:37 PM

David Portas

nick wrote:

> I have a table ordered by column c1
>

Not! Tables aren't ordered. Only the results of a query can be ordered.

I'll have to assume C1 is the key. Please include DDL with your posts
then we won't have to guess. Here's your sample data and my solution.
I've also assumed that "first" means MAX(c1) because that's the only
way I can figure that you'd get "c" as the result.

CREATE TABLE tbl (c1 CHAR(1) NOT NULL PRIMARY KEY, c2 INT NOT NULL);

INSERT INTO tbl (c1,c2) VALUES ('a', 10);
INSERT INTO tbl (c1,c2) VALUES ('b', 20);
INSERT INTO tbl (c1,c2) VALUES ('c', 30);
INSERT INTO tbl (c1,c2) VALUES ('d', 80);
INSERT INTO tbl (c1,c2) VALUES ('e', 1000);

SELECT MAX(c1)
FROM tbl AS T
WHERE 100 >
(SELECT SUM(c2)
FROM tbl
WHERE c1 <= T.c1);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

7 Jun 2006 2:24 AM

nick

thanks, i also figured out these way.
sorry that i described the problem in a wrong way. try to state the order in
the calculating....

Show quoteHide quote

"David Portas" wrote:

> nick wrote:
> > I have a table ordered by column c1
> >
>
> Not! Tables aren't ordered. Only the results of a query can be ordered.
>
> I'll have to assume C1 is the key. Please include DDL with your posts
> then we won't have to guess. Here's your sample data and my solution.
> I've also assumed that "first" means MAX(c1) because that's the only
> way I can figure that you'd get "c" as the result.
>
> CREATE TABLE tbl (c1 CHAR(1) NOT NULL PRIMARY KEY, c2 INT NOT NULL);
>
> INSERT INTO tbl (c1,c2) VALUES ('a', 10);
> INSERT INTO tbl (c1,c2) VALUES ('b', 20);
> INSERT INTO tbl (c1,c2) VALUES ('c', 30);
> INSERT INTO tbl (c1,c2) VALUES ('d', 80);
> INSERT INTO tbl (c1,c2) VALUES ('e', 1000);
>
> SELECT MAX(c1)
> FROM tbl AS T
> WHERE 100 >
>   (SELECT SUM(c2)
>     FROM tbl
>     WHERE c1 <= T.c1);
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>

6 Jun 2006 10:24 PM

Steve Kass

Nick,

Until SQL Server implements the full syntax of
SUM() ... OVER ..., this may be one of those problems
best solved with a cursor. Using David's definitions,

DECLARE C CURSOR FAST_FORWARD FOR
SELECT c1, c2 FROM tbl
ORDER BY c1

DECLARE @sum INT
SET @sum = 0

DECLARE @threshold INT
SET @threshold = 100

DECLARE @c_hold CHAR
SET @c_hold = NULL
DECLARE @c1 CHAR, @c2 INT
SET @c1 = @c_hold
SET @c2 = 0

OPEN C

FETCH NEXT FROM C INTO @c1, @c2
WHILE
@sum + @c2 < @threshold
AND @@FETCH_STATUS = 0
BEGIN
SET @c_hold = @c1
SET @sum = @sum + @c2
FETCH NEXT FROM C INTO @c1, @c2
END

CLOSE C
DEALLOCATE C

SELECT * FROM tbl
WHERE c1 = @c_hold
go

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- 256DC15D-7567-4276-B5BF-6BA78B7DC928

nick wrote:

Show quoteHide quote

>I have a table ordered by column c1
>
>c1 c2
>-- --
>a 10
>b 20
>c 30
>d 80
>e 1000
>
>How to locate the first one with sum(c2) < 100? should be 'c' in the example.
>Hope not use cursor.
>
>

7 Jun 2006 8:33 PM

Jim Underwood

SUM ()... OVER doesn't work in SQL 2005?

I thought it did, although I have not used 2005, and am just going by
snippets of posts here and there...

Show quoteHide quote

"Steve Kass" <sk***@drew.edu> wrote in message
news:e6mW3fbiGHA.1264@TK2MSFTNGP05.phx.gbl...
> Nick,
>
> Until SQL Server implements the full syntax of
> SUM() ... OVER ..., this may be one of those problems
> best solved with a cursor. Using David's definitions,
>
>
> DECLARE C CURSOR FAST_FORWARD FOR
> SELECT c1, c2 FROM tbl
> ORDER BY c1
>
> DECLARE @sum INT
> SET @sum = 0
>
> DECLARE @threshold INT
> SET @threshold = 100
>
> DECLARE @c_hold CHAR
> SET @c_hold = NULL
> DECLARE @c1 CHAR, @c2 INT
> SET @c1 = @c_hold
> SET @c2 = 0
>
> OPEN C
>
> FETCH NEXT FROM C INTO @c1, @c2
> WHILE
>   @sum + @c2 < @threshold
>   AND @@FETCH_STATUS = 0
> BEGIN
>   SET @c_hold = @c1
>   SET @sum = @sum + @c2
>   FETCH NEXT FROM C INTO @c1, @c2
> END
>
> CLOSE C
> DEALLOCATE C
>
> SELECT * FROM tbl
> WHERE c1 = @c_hold
> go
>
>
> -- Steve Kass
> -- Drew University
> -- http://www.stevekass.com
> -- 256DC15D-7567-4276-B5BF-6BA78B7DC928
>
> nick wrote:
>
> >I have a table ordered by column c1
> >
> >c1 c2
> >-- --
> >a 10
> >b 20
> >c 30
> >d 80
> >e 1000
> >
> >How to locate the first one with sum(c2) < 100? should be 'c' in the
example.
> >Hope not use cursor.
> >
> >