How to increment in a select query

Question

I've got a query I'm working on and I want to increment one of the fields and restart the counter when a key value is different.

I know this code doesn't work. Programmatically this is what I want...

declare @counter int, @id
set @counter = 0
set @id = 0

select distinct 
  id, 
  counter = when id = @id 
              then @counter += 1
            else @id = id  
               @counter = 1     

...with the end result looking something like this:

ID    Counter
3     1
3     2 
3     3
3     4
6     1
6     2
6     3
7     1

And yes, I am stuck with SQL2k. Otherwise that row_number() would work.

Olander

Depending on the rest of what's available in your query, perhaps the Row_Number() would help? – Topgallant

@reallyJim: If they're using SQL2K, as the tags suggest, then ROW_NUMBER won't be available. – Escapee

@Escapee TOTALLY missed that point! – Topgallant

Answer 1

Assuming a table:

CREATE TABLE [SomeTable] (
  [id] INTEGER,
  [order] INTEGER,
  PRIMARY KEY ([id], [order])
);

One way to get this in Microsoft SQL Server 2000 is to use a subquery to count the rows with the same id and a lower ordering.

SELECT *, (SELECT COUNT(*) FROM [SomeTable] counter 
           WHERE t.id = counter.id AND t.order < counter.order) AS row_num
FROM [SomeTable] t

Tip: It's 2010. Soon your SQL Server will be old enough to drive.

If you use SQL Server 2005 or later, you get wonderful new functions like ROW_NUMBER() OVER (PARTITION...).

Oxygenate

SQL 2000 is still fun. At 13+, SQL Server 7.0 is sullen most of the time and plays video games all day. – Mucro

We're actually working on SQL2k's first car. If he keeps his nose clean and gets good grades it will be his 16 birthday present. ;o) – Olander

They grow up so fast! sniff – Oxygenate

Pfft - SQL 2000 & 7.0 keep telling me to get off their lawn when I try to use CTEs or analytic functions... – Halcyon

Doesn't this method assume that there is an 'order' field in the table? And this field would have to be maintained. OP never mentioned having this field in his table. That is why my method uses an identity column in a temp table to number the rows. Am I missing something? Thanks for help in understanding. – Oria

@Jamie: Sure, the table needs some column that can be used to compare for less-than. It could be an auto-incrementing pseudokey for example. But every table must have a column (or columns) that form a primary key, whether it is to solve this problem or just in general. – Oxygenate

Answer 2

Yes you want ROW_NUMBER().

I would try:

SELECT id, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Counter

Vicinity

Can't - SQL Server didn't support analytic/rank/windowing functions until v2005 – Halcyon

Answer 3

One way to do this is to throw the data into a temp table with an identity column that is used as a row number. Then make the counter column a count of the other rows with the same Id and a lower row number + 1.

CREATE TABLE #MyData(
Id INT
);

INSERT INTO #MyData VALUES(3);
INSERT INTO #MyData VALUES(3);
INSERT INTO #MyData VALUES(3);
INSERT INTO #MyData VALUES(3);
INSERT INTO #MyData VALUES(6);
INSERT INTO #MyData VALUES(6);
INSERT INTO #MyData VALUES(6);
INSERT INTO #MyData VALUES(7);

CREATE TABLE #MyTempTable(
RowNum INT IDENTITY(1,1),
Id INT,
Counter INT
);

INSERT INTO #MyTempTable
SELECT Id, 0
FROM #MyData
ORDER BY Id;

SELECT Id, (SELECT COUNT(*) + 1 FROM #MyTempTable WHERE Id = t1.Id AND RowNum < t1.RowNum) AS 'Counter'
FROM #MyTempTable t1;

You should get the following output based on your example:

Id  Counter
3   1
3   2
3   3
3   4
6   1
6   2
6   3
7   1

Oria

Answer 4

Having row_number() means you have to deal with far, far fewer correlated subqueries. @Bill Karwin's solution works (+1); here's another version that does the same thing but that might be a bit easier to follow. (I used datetimes to determine ordering.)

--  Test table
CREATE TABLE Test
 ( Id      int       not null
  ,Loaded  datetime  not null
 )

--  Load dummy data with made-up distinct datetimes
INSERT Test values (3, 'Jan 1, 2010')
INSERT Test values (3, 'Jan 2, 2010')
INSERT Test values (3, 'Jan 5, 2010')
INSERT Test values (3, 'Jan 7, 2010')
INSERT Test values (6, 'Feb 1, 2010')
INSERT Test values (6, 'Feb 11, 2010')
INSERT Test values (7, 'Mar 31, 2010')


--  The query
SELECT t1.Id, count(*) Counter
 from Test t1
  inner join Test t2
   on t2.Id = t1.Id
    and t2.Loaded <= t1.Loaded
 group by t1.Id, t1.Loaded


--  Clean up when done
DROP TABLE Test

It is important to note that, without good indexes (and perhaps even with them), these kinds of queries can perform very poorly, particularly on large tables. Check and optimize carefully!

Mucro

Answer 5

For MySql, I was able to make it with this query.

SELECT (SELECT COUNT(id) +1 FROM sku s WHERE t.item_id = s.item AND s.id < t.sku_id) AS rowNumber, t.*
FROM
(select item.Name as itemName ,item.id as item_id , sku.Name as skuName ,sku.id as sku_id from item
    INNER JOIN sku ON item.id = sku.item
    WHERE item.active = 'Y' 
) t


1   Roasted Pistachios (Salted, In Shell)   84  1 Pound Bags    84
3   Roasted Pistachios (Salted, In Shell)   84  25 Pound Cases  1174
5   Roasted Pistachios (Salted, In Shell)   84  12 x 2.6 Ounce Bags 5807
2   Roasted Pistachios (Salted, In Shell)   84  5 Pound Bags    814
4   Roasted Pistachios (Salted, In Shell)   84  Samples 4724
6   Roasted Pistachios (Salted, In Shell)   84  12 x 3.2 Ounce Bags 18145
4   Star Fruit  981 5 Pound Bags    17462
1   Star Fruit  981 1 Pound Bags    2125
3   Star Fruit  981 11 Pound Bags   2226
2   Star Fruit  981 44 Pound Cases  2156

Viewer