counting record only when field value is not equal to value of previous record

counting record only when field value is not equal to value of previous record

Post by Curtis Gaylor » Sun, 26 May 2002 00:57:04



Any tips on this (using MSSQL 2000)

Table_ID   Field1  Field2
1                4            5
6                4            3
3                5            8
2                6            8
4                6            8
5                6            2
7                7            1
9                9            8
10              9            8
12              9            2
11              9            2

The recordset is sorted by Field1.  Then we only want to count a row when
Field2 is not equal to the preceding record.  In this case, we would get:

Field2   CountOfField2
1            1
2            2
3            1
5            1
8            2

 
 
 

counting record only when field value is not equal to value of previous record

Post by Joe Celk » Sun, 26 May 2002 01:42:00


1) Read the note at the bottom of this post.  This is considered the
minimal netiquette in the newsgroup.

2) Sorted??  Tables have no ordering. None. Nada. Zip.  Your
specification makes no sense.  But more than that, your mental model of
SQL is wrong.  SQL has columns, not fields -- fields do have ordering
within a record.  You are doing sequential file processing in your head,
not set oriented processing.  

3) Your spec does not make sense in terms of sequential file processing
either.  Consider this list (field1, field2), sorted on field1

List #1
(4, 5)
(4, 6) <= does not match previous record
(4, 5)
(4, 6) <= does not match previous record

versus

List #2
(4, 5)
(4, 5)
(4, 6) <= does not match previous record
(4, 6)

Same data, two different counts.  Why? It is the same sort criteria for
both lists, but there is no obligation to preserve some unspecified
ordering.  

You can get a different ordering from the same sorting procedure, on the
same data on the same machine.  Look up stable and non-stable sorting
algorithms in an intro computer science book for details.  

Want to try again?  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

counting record only when field value is not equal to value of previous record

Post by Curtis Gaylor » Sun, 26 May 2002 02:15:08


Sorry.  I failed to mention that the  Table_ID column (unique identifier)
of the original table is also used, as the second parameter, to order the
recordset used for obtaining the count.

First sort order, however, is Column1, then the identity col.  We want to
obtain a count of the values in Column2 only when the value of Column2 is
not the same as the value of Column2 in the previous record when ordered by
Column1, Table_ID.

The order of the data when sorted by Column2, Table_ID would be:

Table_ID   Column1  Column2
1                4            5
6                4            3
3                5            8
2                6            8
4                6            8
5                6            2
7                7            1
9                9            8
10              9            8
11              9            2
12              9            2

We only want to count a record when
Column2 is not equal to the preceding record.  In this case, we would get:

Column2   CountOfColumn2
1            1
2            2
3            1
5            1
8            2

We haven't been able to think of a way to utilize "set oriented processing"
to achieve this.  It would be nice if you might point us in that direction.
We are working at using a cursor to sequentially move through the file when
ordered as specified.  The actual data set is very large, so this process
takes a long time so far.


Quote:> 1) Read the note at the bottom of this post.  This is considered the
> minimal netiquette in the newsgroup.

> 2) Sorted??  Tables have no ordering. None. Nada. Zip.  Your
> specification makes no sense.  But more than that, your mental model of
> SQL is wrong.  SQL has columns, not fields -- fields do have ordering
> within a record.  You are doing sequential file processing in your head,
> not set oriented processing.

> 3) Your spec does not make sense in terms of sequential file processing
> either.  Consider this list (field1, field2), sorted on field1

> List #1
> (4, 5)
> (4, 6) <= does not match previous record
> (4, 5)
> (4, 6) <= does not match previous record

> versus

> List #2
> (4, 5)
> (4, 5)
> (4, 6) <= does not match previous record
> (4, 6)

> Same data, two different counts.  Why? It is the same sort criteria for
> both lists, but there is no obligation to preserve some unspecified
> ordering.

> You can get a different ordering from the same sorting procedure, on the
> same data on the same machine.  Look up stable and non-stable sorting
> algorithms in an intro computer science book for details.

> Want to try again?

> --CELKO--
>  ===========================
>  Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

counting record only when field value is not equal to value of previous record

Post by Curtis Gaylor » Sun, 26 May 2002 02:32:51


A table is created
    Table_ID int  -- identity column
    Time  int        --  in minutes from 6/1/2002
    Price float

Data is collected on the time and price of GM Stock from a database on a
remote server.  The data colllected is always inserted sequentially so that
the Table_ID is always greater for each entry consistent with it's order.
Sometimes there will be multiple entries for the same exact Time.

However, it is possible that data will be collected for historic time frames
and then enterred.  It would be enterred sequentially for it's own time
period, but could be enterred after more recent data.  Using the combination
of ordering the data by Time and then Table_ID would reflect the original
time sequence of the data.

Over any period of time, the Price data could remain the same from one
transaction to the next.

The desired result is a count of the number of times the Price has changed.

 
 
 

counting record only when field value is not equal to value of previous record

Post by Curtis Gaylor » Sun, 26 May 2002 04:06:59


This appears to work:

SELECT     Column2, COUNT(*) AS CountOfCol2
FROM         myTable
WHERE     (NOT EXISTS
                          (SELECT     TOP 1 table_id
                            FROM          myTable AS z
                            WHERE      ((z.table_id = (myTable.table_id -
1)) AND (table_id.column2 = z.column2))
                            ORDER BY column2, table_id))
GROUP BY column2
ORDER BY column2

 
 
 

counting record only when field value is not equal to value of previous record

Post by oj » Sun, 26 May 2002 04:14:59


nice soln!

--
-oj

http://www.rac4sql.net

http://vyaskn.tripod.com


Quote:> This appears to work:

> SELECT     Column2, COUNT(*) AS CountOfCol2
> FROM         myTable
> WHERE     (NOT EXISTS
>                           (SELECT     TOP 1 table_id
>                             FROM          myTable AS z
>                             WHERE      ((z.table_id = (myTable.table_id -
> 1)) AND (table_id.column2 = z.column2))
>                             ORDER BY column2, table_id))
> GROUP BY column2
> ORDER BY column2

 
 
 

counting record only when field value is not equal to value of previous record

Post by Steve Kas » Sun, 26 May 2002 06:36:55


Curtis,

  Are you sure this will work, since it doesn't refer to Column1
anywhere?  The "previous row" as you've described it in your
narrative is not the row with the next smallest table_id value.

Steve Kass
Drew University


> This appears to work:

> SELECT     Column2, COUNT(*) AS CountOfCol2
> FROM         myTable
> WHERE     (NOT EXISTS
>                           (SELECT     TOP 1 table_id
>                             FROM          myTable AS z
>                             WHERE      ((z.table_id = (myTable.table_id -
> 1)) AND (table_id.column2 = z.column2))
>                             ORDER BY column2, table_id))
> GROUP BY column2
> ORDER BY column2

 
 
 

counting record only when field value is not equal to value of previous record

Post by Curtis Gaylor » Sun, 26 May 2002 20:49:29


oops.  typo in changing to the generic names when i posted the code.  the
"order by" in the in-line select should be "column1, table_id" not "column2,
table_id".  Very perceptive!


> Curtis,

>   Are you sure this will work, since it doesn't refer to Column1
> anywhere?  The "previous row" as you've described it in your
> narrative is not the row with the next smallest table_id value.

> Steve Kass
> Drew University


> > This appears to work:

> > SELECT     Column2, COUNT(*) AS CountOfCol2
> > FROM         myTable
> > WHERE     (NOT EXISTS
> >                           (SELECT     TOP 1 table_id
> >                             FROM          myTable AS z
> >                             WHERE      ((z.table_id =
(myTable.table_id -
> > 1)) AND (table_id.column2 = z.column2))
> >                             ORDER BY column2, table_id))
> > GROUP BY column2
> > ORDER BY column2

 
 
 

counting record only when field value is not equal to value of previous record

Post by Steve Kas » Mon, 27 May 2002 01:42:46


Curtis,

  I don't think the order by in the subquery makes any difference when you
are doing EXISTS ... TOP 1.  If there is at least 1 row in the subquery, there
will be a TOP 1 row regardless of the order.  The order by will change which
row it is, but won't change the result of exists.

  If you run this script, you'll see the table with '*' indicating the items to be
counted, then a query that counts them, then your query, which gives different
answers.  There may be properties of your table that you didn't mention that
allow a simpler solution - for example, your query has trouble if equal
Column1 values do not have consecutive Table_ID values.

create table Curtis (
  Table_ID int,
  Column1 int,
  Column2 int
)
insert into Curtis values (1,4,5)
insert into Curtis values (6,4,1)
insert into Curtis values (3,5,7)
insert into Curtis values (2,6,8)
insert into Curtis values (4,5,8)
insert into Curtis values (5,6,2)
insert into Curtis values (7,7,1)
insert into Curtis values (9,9,8)
insert into Curtis values (10,9,8)
insert into Curtis values (13,9,2)
insert into Curtis values (12,8,3)
insert into Curtis values (11,9,2)

--Show the values that should be counted
select *, case when
Column2 <> isnull((
  select top 1 Column2
  from Curtis C1
  where C1.Column1 < C2.Column1
  or (C1.Column1 = C2.Column1 and C1.Table_id < C2.Table_id)
  order by Column1 desc, Table_ID desc
),-1) then '*'+right(Column2,3) else ' '+right(Column2,3) end as Counted
from Curtis C2
order by Column1, Table_ID

go

--Calculate the counts
select
  Column2,
  count(*) as SteveCount
from Curtis C2
where Column2 <> isnull((
  select top 1 Column2
  from Curtis C1
  where C1.Column1 < C2.Column1
  or (C1.Column1 = C2.Column1 and C1.Table_id < C2.Table_id)
  order by Column1 desc, Table_ID desc
),-1)
group by Column2
order by Column2

SELECT
  Column2,
  COUNT(*) AS CurtisCount
FROM Curtis
WHERE NOT EXISTS (
  SELECT TOP 1 table_id
  FROM Curtis AS z
  WHERE z.table_id = (Curtis.table_id -1)
  AND Curtis.column2 = z.column2
  ORDER BY column1,table_id
)
GROUP BY column2
ORDER BY column2
go

drop table Curtis

Steve


> oops.  typo in changing to the generic names when i posted the code.  the
> "order by" in the in-line select should be "column1, table_id" not "column2,
> table_id".  Very perceptive!



> > Curtis,

> >   Are you sure this will work, since it doesn't refer to Column1
> > anywhere?  The "previous row" as you've described it in your
> > narrative is not the row with the next smallest table_id value.

> > Steve Kass
> > Drew University


> > > This appears to work:

> > > SELECT     Column2, COUNT(*) AS CountOfCol2
> > > FROM         myTable
> > > WHERE     (NOT EXISTS
> > >                           (SELECT     TOP 1 table_id
> > >                             FROM          myTable AS z
> > >                             WHERE      ((z.table_id =
> (myTable.table_id -
> > > 1)) AND (table_id.column2 = z.column2))
> > >                             ORDER BY column2, table_id))
> > > GROUP BY column2
> > > ORDER BY column2

 
 
 

counting record only when field value is not equal to value of previous record

Post by Joe Celk » Mon, 27 May 2002 09:01:57


This was a good problem, and I want to use it for a class.  The key
column cannot be assumed to be sequential, just unique and ordered for
the purpose of determing a change of values.  

CREATE TABLE Foobar
(col_2 INTEGER NOT NULL,
 tab_id INTEGER NOT NULL PRIMARY KEY,
 col_1 INTEGER NOT NULL);

INSERT INTO Foobar VALUES (1, 7, 7);

INSERT INTO Foobar VALUES (2, 5, 6);
INSERT INTO Foobar VALUES (2, 11, 9);
INSERT INTO Foobar VALUES (2, 12, 9);

INSERT INTO Foobar VALUES (3, 6, 4);
--added this guy for a test
INSERT INTO Foobar VALUES (4, 45, 4);
INSERT INTO Foobar VALUES (4, 46, 7);
INSERT INTO Foobar VALUES (4, 47, 4);
INSERT INTO Foobar VALUES (4, 48, 7);

INSERT INTO Foobar VALUES (5, 1, 4);

INSERT INTO Foobar VALUES (8, 2, 6);
INSERT INTO Foobar VALUES (8, 3, 5);
INSERT INTO Foobar VALUES (8, 4, 6);
INSERT INTO Foobar VALUES (8, 9, 9);
INSERT INTO Foobar VALUES (8, 10, 9);

INSERT INTO Foobar VALUES (9, 13, 9);
INSERT INTO Foobar VALUES (9, 15, 9);
INSERT INTO Foobar VALUES (9, 17, 9);
INSERT INTO Foobar VALUES (9, 18, 9);
INSERT INTO Foobar VALUES (9, 19, 10);
INSERT INTO Foobar VALUES (9, 21, 10);
INSERT INTO Foobar VALUES (9, 25, 10);
INSERT INTO Foobar VALUES (9, 28, 10);

Look at the data and mark up the subset we want.  
  col_2 tab_id col_1
  =======================
    1     7       7  **

    2     5       6
    2    11       9  *
    2    12       9  *

    3     6       4  **

    4    45       4
    4    46       7
    4    47       4
    4    48       7

    5     1       4  **

    8     2       6
    8     3       5
    8     4       6
    8     9       9  *
    8    10       9  *

Let's look for facts in the problem and a little code.

1) All groups with one row are in the desired result set (the ** rows
above). It is something of an exception, since all the other runs are
two of more rows :

 SELECT col_2, 1 AS run_count
   FROM Foobar
  GROUP BY col_2
 HAVING COUNT(*) = 1;

2) All groups with one value for col_1 are in the desired result set:

 SELECT col_2, COUNT(*) AS run_count
   FROM Foobar
  GROUP BY col_2
 HAVING COUNT(*) = COUNT (DISTINCT col_1);

This is a generalization of (1) to a group with one or more rows.  

3) The greatest number of runs a group can have is COUNT (DISTINCT
col_1), which would imply that it is completed sorted when we got it.

 SELECT col_2, {x: x <= COUNT (DISTINCT col_1)} AS run_count
   FROM Foobar
  GROUP BY col_2
 HAVING COUNT(*) <> COUNT (DISTINCT col_1);

This is nice for checking solutions, but not much else.  

4) To count the rows in a run, we need to establish the start and finish
rows and see what is in between them.  If the values in the sub-grouping
are the same as the first one, then we have a run.  

SELECT col_2, COUNT(*)
  FROM (
-- this subquery gives the actual value of the run
        SELECT DISTINCT F1.col_2, MIN(F1.col_1)
         FROM Foobar AS F1, Foobar AS F2, Foobar AS F3
        WHERE F1.col_2 = F3.col_2
          AND F1.col_2 = F2.col_2
          AND F1.col_2 = F3.col_2  -- same group
          AND F1.tab_id < F2.tab_id -- start and middle
          AND F2.tab_id <= F3.tab_id -- middle and finish
        GROUP BY F1.col_2, F1.tab_id, F3.tab_id
       HAVING SUM(CASE WHEN F1.col_1 = F2.col_1
                       THEN 1 ELSE 0 END)
              = COUNT(*)) AS X (col_2, run)
 GROUP BY col_2
UNION ALL
SELECT col_2, 1
  FROM Foobar
 GROUP BY col_2
HAVING COUNT(*) = COUNT(DISTINCT col_1);  

The F1.col_1 is the value we are checking on.  

This will probably run slower than the SELECT TOP solutions because this
is a sequential problem and SQL Server is a sequential file
implementation under the covers.  And simple file system would beat out
SQL.  I think I can tighten this one up a bit, but this is a holiday.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

counting record only when field value is not equal to value of previous record

Post by Curtis Gaylor » Mon, 27 May 2002 22:46:18


Thank you!  Very, very helpful.

In our data we can assume that the records are written in order, but
(exactly as you mentioned), records may have been deleted leaving gaps in
the ID column sequence.  I resolved this by creating a #temporary table
creating a new identity column, but that was very time consuming.


> Curtis,

>   I don't think the order by in the subquery makes any difference when you
> are doing EXISTS ... TOP 1.  If there is at least 1 row in the subquery,
there
> will be a TOP 1 row regardless of the order.  The order by will change
which
> row it is, but won't change the result of exists.

>   If you run this script, you'll see the table with '*' indicating the
items to be
> counted, then a query that counts them, then your query, which gives
different
> answers.  There may be properties of your table that you didn't mention
that
> allow a simpler solution - for example, your query has trouble if equal
> Column1 values do not have consecutive Table_ID values.

> create table Curtis (
>   Table_ID int,
>   Column1 int,
>   Column2 int
> )
> insert into Curtis values (1,4,5)
> insert into Curtis values (6,4,1)
> insert into Curtis values (3,5,7)
> insert into Curtis values (2,6,8)
> insert into Curtis values (4,5,8)
> insert into Curtis values (5,6,2)
> insert into Curtis values (7,7,1)
> insert into Curtis values (9,9,8)
> insert into Curtis values (10,9,8)
> insert into Curtis values (13,9,2)
> insert into Curtis values (12,8,3)
> insert into Curtis values (11,9,2)

> --Show the values that should be counted
> select *, case when
> Column2 <> isnull((
>   select top 1 Column2
>   from Curtis C1
>   where C1.Column1 < C2.Column1
>   or (C1.Column1 = C2.Column1 and C1.Table_id < C2.Table_id)
>   order by Column1 desc, Table_ID desc
> ),-1) then '*'+right(Column2,3) else ' '+right(Column2,3) end as Counted
> from Curtis C2
> order by Column1, Table_ID

> go

> --Calculate the counts
> select
>   Column2,
>   count(*) as SteveCount
> from Curtis C2
> where Column2 <> isnull((
>   select top 1 Column2
>   from Curtis C1
>   where C1.Column1 < C2.Column1
>   or (C1.Column1 = C2.Column1 and C1.Table_id < C2.Table_id)
>   order by Column1 desc, Table_ID desc
> ),-1)
> group by Column2
> order by Column2

> SELECT
>   Column2,
>   COUNT(*) AS CurtisCount
> FROM Curtis
> WHERE NOT EXISTS (
>   SELECT TOP 1 table_id
>   FROM Curtis AS z
>   WHERE z.table_id = (Curtis.table_id -1)
>   AND Curtis.column2 = z.column2
>   ORDER BY column1,table_id
> )
> GROUP BY column2
> ORDER BY column2
> go

> drop table Curtis

> Steve


> > oops.  typo in changing to the generic names when i posted the code.
the
> > "order by" in the in-line select should be "column1, table_id" not
"column2,
> > table_id".  Very perceptive!



> > > Curtis,

> > >   Are you sure this will work, since it doesn't refer to Column1
> > > anywhere?  The "previous row" as you've described it in your
> > > narrative is not the row with the next smallest table_id value.

> > > Steve Kass
> > > Drew University


> > > > This appears to work:

> > > > SELECT     Column2, COUNT(*) AS CountOfCol2
> > > > FROM         myTable
> > > > WHERE     (NOT EXISTS
> > > >                           (SELECT     TOP 1 table_id
> > > >                             FROM          myTable AS z
> > > >                             WHERE      ((z.table_id =
> > (myTable.table_id -
> > > > 1)) AND (table_id.column2 = z.column2))
> > > >                             ORDER BY column2, table_id))
> > > > GROUP BY column2
> > > > ORDER BY column2

 
 
 

counting record only when field value is not equal to value of previous record

Post by Curtis Gaylor » Tue, 28 May 2002 00:50:07


Hmmm, in our case it appears that creating a temporary table where a
consequtive identity column can be guaranteed and then running the selection
that depends on it consequtive id's is MUCH faster than having to run your
excellent code that is able to deal with non-consecutive id's.

I used the following modified from your code to create a sample data table
with more records (still ending up with non-consequtive id's):
___________________________________
create table Curtis (
  Table_ID int,
  Column1 int,
  Column2 int
)


begin













end
______________________________________________________
Then using the following from your code:

--Calculate the counts
select
  Column2,
  count(*) as SteveCount
from Curtis C2
where Column2 <> isnull((
  select top 1 Column2
  from Curtis C1
  where C1.Column1 < C2.Column1
  or (C1.Column1 = C2.Column1 and C1.Table_id < C2.Table_id)
  order by Column1 desc, Table_ID desc
),-1)
group by Column2
order by Column2

Took 6:30 minutes
returning these results:
1    501
2    1000
3    1
5    500
7    500
8    1499
___________________________
Using this procedure from our previous code, but creating the temporary
table first took  < 1 SECOND! with the same resulting recordset.

IF OBJECT_ID('tempDB..#tmpDB2')  IS NOT NULL
drop table #tmpDB2

SELECT identity(int,1,1) as rowid, column2, column1
INTO #tmpDB2
FROM         curtis
ORDER BY Column1, Table_id

SELECT     column2, COUNT(*) AS CountOfCol2
FROM         #tmpDB2
WHERE     (NOT EXISTS
                          (SELECT     TOP 1 rowid
                            FROM          #tmpDB2 AS z
                            WHERE      (z.rowid = (#tmpDB2.rowid - 1)) AND
(#tmpDB2.column2 = z.column2)
                           )
           )

GROUP BY column2
ORDER BY column2

go

drop table #tmpDB2


> Thank you!  Very, very helpful.

> In our data we can assume that the records are written in order, but
> (exactly as you mentioned), records may have been deleted leaving gaps in
> the ID column sequence.  I resolved this by creating a #temporary table
> creating a new identity column, but that was very time consuming.



> > Curtis,

> >   I don't think the order by in the subquery makes any difference when
you
> > are doing EXISTS ... TOP 1.  If there is at least 1 row in the subquery,
> there
> > will be a TOP 1 row regardless of the order.  The order by will change
> which
> > row it is, but won't change the result of exists.

> >   If you run this script, you'll see the table with '*' indicating the
> items to be
> > counted, then a query that counts them, then your query, which gives
> different
> > answers.  There may be properties of your table that you didn't mention
> that
> > allow a simpler solution - for example, your query has trouble if equal
> > Column1 values do not have consecutive Table_ID values.

> > create table Curtis (
> >   Table_ID int,
> >   Column1 int,
> >   Column2 int
> > )
> > insert into Curtis values (1,4,5)
> > insert into Curtis values (6,4,1)
> > insert into Curtis values (3,5,7)
> > insert into Curtis values (2,6,8)
> > insert into Curtis values (4,5,8)
> > insert into Curtis values (5,6,2)
> > insert into Curtis values (7,7,1)
> > insert into Curtis values (9,9,8)
> > insert into Curtis values (10,9,8)
> > insert into Curtis values (13,9,2)
> > insert into Curtis values (12,8,3)
> > insert into Curtis values (11,9,2)

> > --Show the values that should be counted
> > select *, case when
> > Column2 <> isnull((
> >   select top 1 Column2
> >   from Curtis C1
> >   where C1.Column1 < C2.Column1
> >   or (C1.Column1 = C2.Column1 and C1.Table_id < C2.Table_id)
> >   order by Column1 desc, Table_ID desc
> > ),-1) then '*'+right(Column2,3) else ' '+right(Column2,3) end as Counted
> > from Curtis C2
> > order by Column1, Table_ID

> > go

> > --Calculate the counts
> > select
> >   Column2,
> >   count(*) as SteveCount
> > from Curtis C2
> > where Column2 <> isnull((
> >   select top 1 Column2
> >   from Curtis C1
> >   where C1.Column1 < C2.Column1
> >   or (C1.Column1 = C2.Column1 and C1.Table_id < C2.Table_id)
> >   order by Column1 desc, Table_ID desc
> > ),-1)
> > group by Column2
> > order by Column2

> > SELECT
> >   Column2,
> >   COUNT(*) AS CurtisCount
> > FROM Curtis
> > WHERE NOT EXISTS (
> >   SELECT TOP 1 table_id
> >   FROM Curtis AS z
> >   WHERE z.table_id = (Curtis.table_id -1)
> >   AND Curtis.column2 = z.column2
> >   ORDER BY column1,table_id
> > )
> > GROUP BY column2
> > ORDER BY column2
> > go

> > drop table Curtis

> > Steve


> > > oops.  typo in changing to the generic names when i posted the code.
> the
> > > "order by" in the in-line select should be "column1, table_id" not
> "column2,
> > > table_id".  Very perceptive!



> > > > Curtis,

> > > >   Are you sure this will work, since it doesn't refer to Column1
> > > > anywhere?  The "previous row" as you've described it in your
> > > > narrative is not the row with the next smallest table_id value.

> > > > Steve Kass
> > > > Drew University


> > > > > This appears to work:

> > > > > SELECT     Column2, COUNT(*) AS CountOfCol2
> > > > > FROM         myTable
> > > > > WHERE     (NOT EXISTS
> > > > >                           (SELECT     TOP 1 table_id
> > > > >                             FROM          myTable AS z
> > > > >                             WHERE      ((z.table_id =
> > > (myTable.table_id -
> > > > > 1)) AND (table_id.column2 = z.column2))
> > > > >                             ORDER BY column2, table_id))
> > > > > GROUP BY column2
> > > > > ORDER BY column2

 
 
 

1. counting records only when value is different than previous record (MSSQL 2000)

Any tips on this (using MSSQL 2000)

Table_ID   Field1  Field2
1                4            5
6                4            3
3                5            8
2                6            8
4                6            8
5                6            2
7                7            1
9                9            8
10              9            8
12              9            2
11              9            2

The recordset is sorted by Field1.  Then we only want to count a row when
Field2 is not equal to the preceding record.  In this case, we would get:

Field2   CountOfField2
1            1
2            2
3            1
5            1
8            2

2. NT: Got ora-1031 when creating database

3. Increment Number for next record from previous record value

4. is it possible to lookup a field value, based on a lookup that is based on another lookup, in a repeating field?

5. Update a record using a previous records value

6. Informix jobs in Wisconsin?

7. copying values from previous record into new record.....................

8. JDBC CallableStatement and MS-Access

9. Insert value from the same field in previous record

10. Count of records with field = <value>

11. Count of records for each value of a field

12. Finding records(two records have a single field value that is the same)

13. UPDATE Using Previous Record Value