How to number Duplicate rows in sql

Question

I'm trying to figure out the best/easiest way to number duplicate rows accordingly.I have a set of data that I am uploading to the database table. I have uploaded it, and auto incremented it, now I want to generate the order_id in the fashion shown in my question. For example

    ----ID-------NAME-----------ORDER_ID----------
        1        Bob Smith        1
        2        Steve Jones      2
        3        Bob Smith        1
        4        Billy Guy        3
        5        Steve Jones      2
    ----------------------------------------------

I was thinking I could use a statement such as select NAME from table where name= duplicate_name but I can't seem to figure out how I would realistically go about that, much less then enter the appropriate ORDER_ID afterwards. Is there an easy way to do this ?

In modern RDBMS supporting [window functions](http://en.wikipedia.org/wiki/Select_%28SQL%29#Window_function), this is very simple: `SELECT name, dense_rank() OVER (ORDER BY name) AS order_id FROM tbl`. Sadly, MySQL is not among those. — Erwin Brandstetter, Oct 28 '14 at 03:45
Well that sucks. Is there a way to do his in MySQL or am I out of luck ? — Fat Cat, Oct 28 '14 at 03:47
lol thats what I figured. I guess it's better to go complicated then not at all. suggestions? — Fat Cat, Oct 28 '14 at 03:49
I am guessing you build on a table with `(id, name)` and want to generate `order_id` additionally as displayed. Your question might be more clear about that. — Erwin Brandstetter, Oct 28 '14 at 04:14
I have a set of data that I am uploading to the database table. I have uploaded it, and auto incremented it, now I want to generate the `order_id` in the fashion shown in my question. Hope that clears up any confusion. — Fat Cat, Oct 28 '14 at 04:17
Yes. Please [edit](http://stackoverflow.com/posts/26600464/edit) your question to make it clear. Shouldn't be hidden in comments. — Erwin Brandstetter, Oct 28 '14 at 04:20

sudeep · Accepted Answer · 2014-10-28T04:36:24.177

1

You could do something like this:

SELECT 
  A.ID, 
  A.NAME, 
  (SELECT TOP 1 T.ID 
    FROM table AS T 
    WHERE T.NAME = A.NAME 
    ORDER BY T.ID) AS ORDER_ID 

  FROM table AS A

If your database engine does not support the TOP keyword, but does support the LIMIT keyword, you may be able to do this:

SELECT 
      A.ID, 
      A.NAME, 
      (SELECT T.ID 
        FROM table AS T 
        WHERE T.NAME = A.NAME 
        ORDER BY T.ID
        LIMIT 1) AS ORDER_ID 

      FROM table AS A

edited Oct 28 '14 at 04:36

answered Oct 28 '14 at 04:03

sudeep

404
2
8

had to take out TOP because it would not work with mysql. Tried replacing it with LIMIT but, it didn't like that either. When I try running it without `TOP 1`, it gives me an error saying `error code 1242 subquery returns more than 1 row` – Fat Cat Oct 28 '14 at 04:28
I'm not sure about MySQL, but this is how I would use limit in PostGres. MySQL may be similar..Someone else may know this here. Also, The TOP/LIMIT 1 is essential in this query because you are selecting a single value from the subquery. SELECT A.ID, A.NAME, (SELECT T.ID FROM table AS T WHERE T.NAME = A.NAME ORDER BY T.ID LIMIT 1) AS ORDER_ID FROM table AS A – sudeep Oct 28 '14 at 04:30

score 0 · Answer 2 · edited May 23 '17 at 12:20

SELECT t.*, t1.order_id
FROM  (
   SELECT name, @rownum := @rownum + 1 AS order_id
   FROM  (
      SELECT name, min(id) AS min_id
      FROM   tbl
      GROUP  BY name
      ) t0
   ,  (SELECT @rownum := 0) r
   ORDER  BY min_id
   ) t1
JOIN tbl t USING (name);

In the subquery t0 aggregate all names and compute the minimum id per name. You seem to want to number names after first appearance according to id. This is subtly different from just adding dense_rank() like I commented.
Simulating the basic window function row_number(), tag a running number to each name according to this order in t1.
Join back to the base table.

How to number Duplicate rows in sql

2 Answers2