Hi I am trying to achieve rows auto numbering in cycles in a mysql view. I did start with Pure-SQL Technique for Auto-Numbering Rows in Result Set and got it to work. Then I tried tweaking it and got ALMOST there.... and now I am stuMainmped
the view lists dogs (CaneID) and their ascendants(AvoID). What I would like to achieve is for the numbering to resart every time the main dog (CaneID) changes so I would have each ascendant belonging to a dog number from 1 on.
So far it is working only for the first dog, the following are still numbered in sequence but do not start from 1.
This is my view query:
SELECT
`avi1`.`CaneID`,
`avi1`.`Cane`,
`avi1`.`PedPos`,
`avi1`.`AvoID`,
`avi1`.`Avo`,
`avi1`.`Ripetuto`,
(
SELECT
count(0)
FROM
`v_avi_ripetuti` `Avi2`
WHERE
(
(`avi2`.`Ripetuto` > 1)
AND (`avi2`.`CaneID` <= `avi1`.`CaneID`)
AND (`avi2`.`AvoID` <= `avi1`.`AvoID`)
)
ORDER BY
`avi2`.`CaneID`,
`avi2`.`AvoID`
) AS `RowNumber`
FROM
`v_avi_ripetuti` `Avi1`
WHERE
(`avi1`.`Ripetuto` > 1)
ORDER BY
`avi1`.`CaneID`,
`avi1`.`AvoID`
What I am getting at this point is:
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 2
3 Ambra Gaya 657 Carmen v. Westfalenzwinger 6
3 Ambra Gaya 658 Leu v. Stammhaus Eike 8
3 Ambra Gaya 684 Chipsy v. Theresienhof 10
3 Ambra Gaya 718 Leo v. guten Löwen 11
3 Ambra Gaya 1022 Zara v. Ratibor u. Corvey 12
3 Ambra Gaya 1024 Ruth v. Ratibor u. Corvey 13
3 Ambra Gaya 1050 Dunja v. Hofoldinger Forst 14
3 Ambra Gaya 1200 Icksle v. Ratibor u. Corvey 15
3 Ambra Gaya 1227 Troldegaards Tanja 16
3 Ambra Gaya 1228 Anemone v. Ratibor u. Corvey 17
-----------------------> New Main dog<-------------------------------
3371 Always Habit 600 Absinthe v. Kallenberg 4
3371 Always Habit 750 Mathoaka's Relaxing Boy 13
3371 Always Habit 2560 Leijonamielen Luvaton Lyyli 21
I am very new to mysql (installed it a week ago) and trying very hard to leave access behind....please be patient.
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