Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Newbie question I'm looking for a function ROW_INDEX 1

Status
Not open for further replies.
Baukep

Baukep

Programmer
Joined
Sep 28, 2006
Messages
44
Location
NL
Hello,

I couldn't find a function that numbers the resultset of a query. First row should be 1, second row 2 etc.

I searched for rownum, rowcount, rowindex etc with and without underscore's.

I must be overlooking something and feel rather stupid.

Can someone help me here?

Bauke
 
Sort by date Sort by votes
Hi

Not with natural MySQL feature, but there is a nice trick :
Code: 
[blue]mysql>[/blue] [b]select[/b] day,@row_index:=@row_index+1 row_index [b]from[/b] work,([b]select[/b] @row_index:=0) ri;
+------------+-----------+
| day        | row_index |
+------------+-----------+
| 2006-10-02 |         1 |
| 2006-10-01 |         2 |
| 2006-10-00 |         3 |
+------------+-----------+
3 rows in set (0.01 sec)

Feherke.
 
Upvote 0 Downvote
Thank you (again)!

Where do I put the second part?
It should go in this query:

SELECT w_seizoen AS Seizoen
, CONCAT(l_naam, ' (',speler,')') AS Naam
, COUNT(w_wnr) AS "Gespeeld"
, SUM(berpunten) AS "Tot. punten"
, SUM(berpunten)/COUNT(w_wnr) AS Winstperc
, ROUND(SUM(car)/SUM(w_brtn),4)- ROUND(temaken/25,4) AS Moyenne
, MAX(HSerie)/temaken AS "HSerie (%%)"
, @row_index:=@row_index+1 row_index
FROM (
SELECT w_seizoen
, w_speler1 AS speler
, w_wnr
, IF(ROUND(w_s1_car/w_s1_temaken*100,3) >= 100 AND ROUND(w_s2_car/w_s2_temaken*100,3) >= 100, 1,
IF(ROUND(w_s1_car/w_s1_temaken*100,3) = ROUND(w_s2_car/w_s2_temaken*100,3), 1,
IF(ROUND(w_s1_car/w_s1_temaken*100,3) > ROUND(w_s2_car/w_s2_temaken*100,3) AND ROUND(w_s1_car/w_s1_temaken*100,3) >= 100, 3,
IF(ROUND(w_s1_car/w_s1_temaken*100,3) > ROUND(w_s2_car/w_s2_temaken*100,3) AND ROUND(w_s1_car/w_s1_temaken*100,3) < 100,2,0)))) AS berpunten
, w_s1_car AS car
, w_brtn
, w_s1_temaken AS temaken
, w_s1_hserie AS HSerie
FROM wedstrijden WHERE w_seizoen="2006/07" AND SUBSTRING(w_ronde,1,1)<>"G"
UNION ALL
SELECT w_seizoen
, w_speler2 AS speler
, w_wnr
, IF(ROUND(w_s2_car/w_s2_temaken*100,3) >= 100 AND ROUND(w_s1_car/w_s1_temaken*100,3) >= 100, 1,
IF(ROUND(w_s2_car/w_s2_temaken*100,3) = ROUND(w_s1_car/w_s1_temaken*100,3), 1,
IF(ROUND(w_s2_car/w_s2_temaken*100,3) > ROUND(w_s1_car/w_s1_temaken*100,3) AND ROUND(w_s2_car/w_s2_temaken*100,3) >= 100, 3,
IF(ROUND(w_s2_car/w_s2_temaken*100,3) > ROUND(w_s1_car/w_s1_temaken*100,3) AND ROUND(w_s2_car/w_s2_temaken*100,3) < 100,2,0)))) AS berpunten
, w_s2_car AS car
, w_brtn
, w_s2_temaken AS temaken
, w_s2_hserie AS HSerie
FROM wedstrijden WHERE w_seizoen="2006/07" AND SUBSTRING(w_ronde,1,1)<>"G", (select @row_index:=0)
) AS d
INNER JOIN leden ON l_id= d.speler
GROUP BY w_seizoen, speler
ORDER BY w_seizoen, Winstperc DESC, Moyenne DESC


Bauke
 
Upvote 0 Downvote
Hi

If you list the value in the outer [tt]select[/tt], then put it in the [tt]from[/tt] clause of the same outer [tt]select[/tt]. I suggest to put it to the end of list.
Code: 
SELECT w_seizoen AS Seizoen
            , CONCAT(l_naam, ' (',speler,')') AS Naam
            , COUNT(w_wnr) AS "Gespeeld"
            , SUM(berpunten) AS "Tot. punten"
            , SUM(berpunten)/COUNT(w_wnr) AS Winstperc
            , ROUND(SUM(car)/SUM(w_brtn),4)- ROUND(temaken/25,4) AS Moyenne
            , MAX(HSerie)/temaken AS "HSerie (%%)"
            , @row_index:=@row_index+1 row_index
FROM (
        SELECT w_seizoen
            , w_speler1 AS speler
            , w_wnr
            , IF(ROUND(w_s1_car/w_s1_temaken*100,3) >= 100 AND ROUND(w_s2_car/w_s2_temaken*100,3) >= 100, 1,
                IF(ROUND(w_s1_car/w_s1_temaken*100,3) = ROUND(w_s2_car/w_s2_temaken*100,3), 1,
                    IF(ROUND(w_s1_car/w_s1_temaken*100,3) > ROUND(w_s2_car/w_s2_temaken*100,3) AND ROUND(w_s1_car/w_s1_temaken*100,3) >= 100, 3,
                        IF(ROUND(w_s1_car/w_s1_temaken*100,3) > ROUND(w_s2_car/w_s2_temaken*100,3) AND ROUND(w_s1_car/w_s1_temaken*100,3) < 100,2,0)))) AS berpunten
            , w_s1_car AS car
            , w_brtn
            , w_s1_temaken AS temaken
            , w_s1_hserie AS HSerie
        FROM wedstrijden WHERE w_seizoen="2006/07" AND SUBSTRING(w_ronde,1,1)<>"G"
    UNION ALL
        SELECT w_seizoen
            , w_speler2 AS speler
            , w_wnr
            , IF(ROUND(w_s2_car/w_s2_temaken*100,3) >= 100 AND ROUND(w_s1_car/w_s1_temaken*100,3) >= 100, 1,
                IF(ROUND(w_s2_car/w_s2_temaken*100,3) = ROUND(w_s1_car/w_s1_temaken*100,3), 1,
                    IF(ROUND(w_s2_car/w_s2_temaken*100,3) > ROUND(w_s1_car/w_s1_temaken*100,3) AND ROUND(w_s2_car/w_s2_temaken*100,3) >= 100, 3,
                        IF(ROUND(w_s2_car/w_s2_temaken*100,3) > ROUND(w_s1_car/w_s1_temaken*100,3) AND ROUND(w_s2_car/w_s2_temaken*100,3) < 100,2,0)))) AS berpunten
            , w_s2_car AS car
            , w_brtn
            , w_s2_temaken AS temaken
            , w_s2_hserie AS HSerie
        FROM wedstrijden WHERE w_seizoen="2006/07" AND SUBSTRING(w_ronde,1,1)<>"G", (select @row_index:=0)
            ) AS d
INNER JOIN leden ON l_id= d.speler[red],
(select @row_index:=0) ri[/red]
GROUP BY w_seizoen, speler
ORDER BY w_seizoen, Winstperc DESC, Moyenne DESC

Feherke.
 
Upvote 0 Downvote
Thanks, at least I get some numbers....

But all mixed and not the right sequence. Probably the reason here is the union all I use. When I put the last part of your code in the higher branches the data even looks more terrible ( 1 to 6 and than 17 to 22). Putting it in one branch everything is NULL.

So, it should be somewhere else....... but where?

Bauke

The result sofar:

2006/07 Bauke de Vries (1) 6 15 2.5000 0.2111 0.4865 4
2006/07 Dick Hooijsma (8) 6 12 2.0000 -0.0286 0.4667 10
2006/07 Douwe Wijnia (13) 5 9 1.8000 -0.0173 0.2778 11
2006/07 Geert Scholtens (4) 7 11 1.5714 -0.1508 0.3182 6
2006/07 Jan Koornstra (10) 4 6 1.5000 0.3250 0.2571 8
2006/07 Dick Zomer (3) 7 10 1.4286 -0.0222 0.4500 7
2006/07 Jannes Herskamp (6) 7 10 1.4286 -0.1210 0.2400 5
2006/07 Feike Muurling (5) 6 7 1.1667 -0.1469 0.3158 1
2006/07 Bertus Schut (7) 5 5 1.0000 -0.0941 0.3077 12
2006/07 Trientsje Stoffelsma (11) 7 7 1.0000 -0.1399 0.2667 3
2006/07 Minne Hoekstra (9) 6 2 0.3333 -0.0626 0.3846 2
2006/07 Piet de Wit (2) 6 0 0.0000 -0.3300 0.1852 9
 
Upvote 0 Downvote
Hi

Baukep said:
But all mixed and not the right sequence.
Click to expand...
Hmm... I did not used that trick properly, just played with it, but I did not saw such behavior. And can not reproduces it neither now.
Baukep said:
Probably the reason here is the union all I use.
Click to expand...
That certainly not. The [tt]union[/tt] is in the inner [tt]select[/tt] and its execution is finished when the outer [tt]select[/tt] receives the data.
Baukep said:
When I put the last part of your code in the higher branches the data even looks more terrible
Click to expand...
The only possibility I see is to put the numbering in another [tt]select[/tt], around the actual one :
Code: 
select therealselect.*,@row_index:=@row_index+1 row_index from (
[small]
SELECT w_seizoen AS Seizoen
            , CONCAT(l_naam, ' (',speler,')') AS Naam
            , COUNT(w_wnr) AS "Gespeeld"
            , SUM(berpunten) AS "Tot. punten"
            , SUM(berpunten)/COUNT(w_wnr) AS Winstperc
            , ROUND(SUM(car)/SUM(w_brtn),4)- ROUND(temaken/25,4) AS Moyenne
            , MAX(HSerie)/temaken AS "HSerie (%%)"
FROM (
        SELECT w_seizoen
            , w_speler1 AS speler
            , w_wnr
            , IF(ROUND(w_s1_car/w_s1_temaken*100,3) >= 100 AND ROUND(w_s2_car/w_s2_temaken*100,3) >= 100, 1,
                IF(ROUND(w_s1_car/w_s1_temaken*100,3) = ROUND(w_s2_car/w_s2_temaken*100,3), 1,
                    IF(ROUND(w_s1_car/w_s1_temaken*100,3) > ROUND(w_s2_car/w_s2_temaken*100,3) AND ROUND(w_s1_car/w_s1_temaken*100,3) >= 100, 3,
                        IF(ROUND(w_s1_car/w_s1_temaken*100,3) > ROUND(w_s2_car/w_s2_temaken*100,3) AND ROUND(w_s1_car/w_s1_temaken*100,3) < 100,2,0)))) AS berpunten
            , w_s1_car AS car
            , w_brtn
            , w_s1_temaken AS temaken
            , w_s1_hserie AS HSerie
        FROM wedstrijden WHERE w_seizoen="2006/07" AND SUBSTRING(w_ronde,1,1)<>"G"
    UNION ALL
        SELECT w_seizoen
            , w_speler2 AS speler
            , w_wnr
            , IF(ROUND(w_s2_car/w_s2_temaken*100,3) >= 100 AND ROUND(w_s1_car/w_s1_temaken*100,3) >= 100, 1,
                IF(ROUND(w_s2_car/w_s2_temaken*100,3) = ROUND(w_s1_car/w_s1_temaken*100,3), 1,
                    IF(ROUND(w_s2_car/w_s2_temaken*100,3) > ROUND(w_s1_car/w_s1_temaken*100,3) AND ROUND(w_s2_car/w_s2_temaken*100,3) >= 100, 3,
                        IF(ROUND(w_s2_car/w_s2_temaken*100,3) > ROUND(w_s1_car/w_s1_temaken*100,3) AND ROUND(w_s2_car/w_s2_temaken*100,3) < 100,2,0)))) AS berpunten
            , w_s2_car AS car
            , w_brtn
            , w_s2_temaken AS temaken
            , w_s2_hserie AS HSerie
        FROM wedstrijden WHERE w_seizoen="2006/07" AND SUBSTRING(w_ronde,1,1)<>"G", (select @row_index:=0)
            ) AS d
INNER JOIN leden ON l_id= d.speler
GROUP BY w_seizoen, speler
ORDER BY w_seizoen, Winstperc DESC, Moyenne DESC
[/small]
) therealselect,(select @row_index:=0) ri
But this could slow down the query, depending on the amount of data.

Feherke.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question Question
function argument value, type or count is invalid
Replies
3
Views
939
bharons
bharons
EdLentz
  • Locked
  • Question Question
Looking for suggestions on how to do this
Replies
4
Views
828
Chris Miller
Chris Miller
EdLentz
  • Locked
  • Question Question
How do I use a keyword in a query??
Replies
6
Views
1K
EdLentz
EdLentz
boylesg
  • Locked
  • Question Question
Can't sort my table by a particular column, records are returned in autinc order.
Replies
4
Views
907
spamjim
spamjim
AbidingDude
  • Locked
  • Question Question
One Constraint for Multiple Columns
Replies
2
Views
644
AbidingDude
AbidingDude

Part and Inventory Search

Sponsor

Back
Top