Pure-SQL Technique for Auto-Numbering Rows in Result Set

Pure-SQL Technique for Auto-Numbering Rows in Result Set

To have a meaningful row number you need to order your results. Then you can do something like this:

SELECT id, name
    , (SELECT COUNT(*) FROM people p2 WHERE name='Spiewak' AND p2.id <= p1.id) AS RowNumber
FROM people p1
WHERE name = 'Spiewak'
ORDER BY id

Note that the WHERE clause of the sub query needs to match the WHERE clause or the primary key from the main query and the ORDER BY of the main query.

SQL Server has the ROW_NUMBER() OVER construct to simplify this, but I don't know if MySQL has anything special to address it.

---

Since my post here was accepted as the answer, I want to also call out Dan Goldstein's response, which is very similar in approach but uses a JOIN instead of a sub query and will often perform better

Autonumbering Rows in mysql view in cycles

I DID IT!!!!!!!
I used the function SUM() instead of COUNT().
This is my mysql View Code in case someone else can use it:

SELECT
`avi1`.`CaneID`,
`avi1`.`Cane`,
`avi1`.`PedPos`,
`avi1`.`AvoID`,
`avi1`.`Avo`,
`avi1`.`Ripetuto`,
(
    SELECT
        sum(

            IF (
                (
                    (
                        `avi2`.`CaneID` = `avi1`.`CaneID`
                    )
                    AND (`avi2`.`AvoID` <= `avi1`.`AvoID`)
                ),
                1,
                0
            )
        )
    FROM
        `v_avi_ripetuti` `Avi2`
    WHERE
        (`avi2`.`Ripetuto` > 1)
) AS `NumRiga`
  FROM
`v_avi_ripetuti` `Avi1`
 WHERE
(`avi1`.`Ripetuto` > 1)

and this gives me the desired result of:

 CaneID  Cane         AvoID  Avo                             RowNumber
 ---------------------------------------------------------------------
 2       Antigua      472    Anika v. Stammhaus Eike         1
 2       Antigua      527    Britta v.d. Römerlinde          2
 2       Antigua      642    Ares v. Nettenberg              3
 2       Antigua      657    Carmen v. Westfalenzwinger      4
 2       Antigua      658    Leu v. Stammhaus Eike           5
 2       Antigua      684    Chipsy v. Theresienhof          6
 2       Antigua      1662   Astrit v.d. Burrlinde           7
 -----------------------> New Main dog<-------------------------------
 3       Ambra Gaya   472    Anika v. Stammhaus Eike         1
 3       Ambra Gaya   657    Carmen v. Westfalenzwinger      2
 3       Ambra Gaya   658    Leu v. Stammhaus Eike           3
 3       Ambra Gaya   684    Chipsy v. Theresienhof          4
 3       Ambra Gaya   718    Leo v. guten Löwen              5
 3       Ambra Gaya   1022   Zara v. Ratibor u. Corvey       6
 3       Ambra Gaya   1024   Ruth v. Ratibor u. Corvey       7
 3       Ambra Gaya   1050   Dunja v. Hofoldinger Forst      8
 3       Ambra Gaya   1200   Icksle v. Ratibor u. Corvey     9
 3       Ambra Gaya   1227   Troldegaards Tanja              10
 3       Ambra Gaya   1228   Anemone v. Ratibor u. Corvey    11
 -----------------------> New Main dog<-------------------------------
 3371    Always Habit 600    Absinthe v. Kallenberg          1
 3371    Always Habit 750    Mathoaka's Relaxing Boy         2
 3371    Always Habit 2560   Leijonamielen Luvaton Lyyli     3

how to store a *sorted* SELECT result in another table?

Maybe you could wrap the select into another select:

SET @counter := 0;

INSERT INTO resultsetdata
  SELECT *, @counter := @counter + 1
  FROM (
      SELECT "12345", a.ID
      FROM sometable a
      JOIN bigtable  b
      WHERE a.foo = b.bar
      ORDER BY a.whatever DESC
  ) AS tmp

... but you are still at the mercy of the dumbness of MySQL's optimizer.

That's all I found about this topic, but I couln't find a hard guarantee:

Pure-SQL Technique for Auto-Numbering Rows in Result Set

http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/

http://www.xaprb.com/blog/2005/09/27/simulating-the-sql-row_number-function/

MySQL nested tables record numbering

SELECT CASE WHEN @prev = x.parent_id THEN @i:=@i ELSE @i:=@i+1 END parentrank 
     , x.parentname
     , CASE WHEN @prev = x.parent_id THEN @j:=@j+1 ELSE @j:=1 END childrank
     , x.childname
     , @prev:=parent_id
  FROM ( SELECT p.passportid parent_id
            , p.parentname
            , c.passportid child_id
            , c.childname
         FROM parents p
         JOIN children c
           ON c.parentid = p.passportid
       ) x
  JOIN ( SELECT @prev:=null,@i:=0,@j:=0) vars
 ORDER 
    BY x.parent_id
     , x.child_id;
+------------+------------+-----------+-----------+------------------+
| parentrank | parentname | childrank | childname | @prev:=parent_id |
+------------+------------+-----------+-----------+------------------+
|          1 | John       |         1 | Gabriel   |         12345678 |
|          1 | John       |         2 | Angela    |         12345678 |
|          1 | John       |         3 | Philip    |         12345678 |
|          2 | Kate       |         1 | Eleanor   |         98765432 |
|          2 | Kate       |         2 | Betty     |         98765432 |
|          3 | Mary       |         1 | Peter     |        111222333 |
|          3 | Mary       |         2 | Lara      |        111222333 |
|          3 | Mary       |         3 | Michael   |        111222333 |
|          3 | Mary       |         4 | Robert    |        111222333 |
|          3 | Mary       |         5 | Amanda    |        111222333 |
+------------+------------+-----------+-----------+------------------+

Show row number column from result set in oracle sql view

in oracle use ROW_NUMBER oracle docs

How to insert multiple rows into MySQL table (with auto increment) and skip duplicates?

It looks like you want to not insert values where the A and C values match an existing table1 row. I would use a temporary table for that, first inserting into the temporary table, then inserting into table1 only the appropriate rows:

create temporary table df (A varchar(1), B int, C varchar(1));
insert into df values ('a',2,'A'),('b',1,'B'),('d',6,'D'),('e',7,'E');
insert into table1 (A,B,C) select df.* from df left join table1 using (A,C) where table1.id is null;

An index on (A,C) in table1 will make this efficient.

fiddle

How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?

You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields:

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

---

Related Topics