CODEWITHSUNDEEP
mysql
Paresh
Paresh

Reputation: 3749

Want Row Number on Group of column in MY SQL?

I have one table in MYSQL It has data like 

Id Name 
1  test
1  test
1  test123
2  test222
3  test333

I want the data like 

Id Name     RowNum
1  test       1
1  test       2
1  test123    1
2  test222    1
3  test333    1

Means i want assign row number on group of Id and Name ?

what should the script for same?

Upvotes: 7

Views: 11239

Answers (5)

Peter Lindqvist
Peter Lindqvist

Reputation: 10200

This table definition will achieve what you want.

CREATE TABLE  `test` (
  `Id` int(10) unsigned NOT NULL,
  `Name` varchar(45) NOT NULL,
  `RowNum` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Id`,`Name`,`RowNum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Populate table with data

INSERT INTO test VALUES
(1,"test",null),
(1,"test",null),
(1,"test123",null),
(2,"test222",null),
(3,"test333",null);

Select data from table

SELECT * FROM test;

Result

1, 'test', 1
1, 'test', 2
1, 'test123', 1
2, 'test222', 1
3, 'test333', 1

For doing it in a query here is a rather crude way of doing it.

select g.id,g.name,g.rownum 
from (
    select t.id,t.name,
        @running:=if(@previous=concat(t.id,t.name),@running,0) + 1 as rownum,
        @previous:=concat(t.id,t.name) 
    from test t
    order by concat(t.id,t.name) 
) g;

Upvotes: 8

dernasherbrezon
dernasherbrezon

Reputation: 887

select id, name, (select count(*) + 1 from table as i where i.id = p.id and i.id < p.id ) from table p;

Upvotes: 2

tiho
tiho

Reputation: 6925

Here's another way with a query that is simpler (at least for me):

SELECT
a.Id, a.Name,
(SELECT COUNT(*) FROM test
 WHERE Id = a.Id AND `Name` = a.Name AND row_id < a.row_id) AS RowNum
FROM test AS a
ORDER BY a.row_id;

This supposes there exists a global row_id (for instance an auto-increment primary key in your table).

Upvotes: 6

erikkallen
erikkallen

Reputation: 34391

Oracle and MSSQL would support

SELECT Id, Name, row_number() OVER(partition by Id, Name order by Id) AS RowNum FROM table

(answer doesn't help the OP who is on MySQL, but added for completeness)

Upvotes: 2

alemjerus
alemjerus

Reputation: 8268

UPDATE tablename SET RowNum=(SELECT COUNT(Id) FROM tablename WHERE Id=Name)

Upvotes: 1

Related Questions