CODEWITHSUNDEEP
mysql
GGio
GGio

Reputation: 7643

Simple incremental counter in mysql query

My Query:

SET @rank = 0;
SELECT Jobs.ID, Jobs.StatusID, (@rank:=@rank+1) AS Rank
FROM Jobs
INNER JOIN JobStatuses ON Jobs.StatusID = JSID
INNER JOIN JobStatusGroups ON Jobs.SGrID = JSGID
WHERE Jobs.StatusID = 3
ORDER BY JobTitle DESC
LIMIT 5

Results in:

1010 3 1
1011 3 2
1013 3 4
1014 3 5
1016 3 7

Should result in:

1010 3 1
1011 3 2
1013 3 3
1014 3 4
1016 3 5

How can I make @rank only increment if row is actually being inserted? I tried cross join did not work. I also tried the following:

SELECT Jobs.ID, Jobs.StatusID, (@rank:=@rank+1) AS Rank
FROM Jobs, (SELECT @rank := 0) tempRank
INNER JOIN JobStatuses ON Jobs.StatusID = JSID
INNER JOIN JobStatusGroups ON Jobs.SGrID = JSGID
WHERE Jobs.StatusID = 3
ORDER BY JobTitle DESC
LIMIT 5

But gives me an error saying Jobs.StatusID does not exist.

Upvotes: 0

Views: 45

Answers (2)

Karunakar
Karunakar

Reputation: 2349

Try This updated one:

SET @rank = 0;
INSERT INTO MyTable(MyID, UserID, JobID, StatusID, SortOrder)
SELECT myid, userid, Jobs.ID, Jobs.StatusID, (@rank:=@rank+1) AS Rank
FROM Jobs
INNER JOIN JobStatuses ON Jobs.StatusID = JobStatuses.JSID
LEFT JOIN JobStatusGroups ON Jobs.SGrID = JobStatusGroups.JSGID
 ON DUPLICATE KEY update MyID=IF((@rank:=@rank-1) <> NULL IS NULL, VALUES(MyID), NULL);

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25842

instead of setting it outside cross join it in the select (i prefer cross join just so its easier to read aka i know where im creating the variable) that way the count wont be messed up

INSERT IGNORE INTO MyTable(MyID, UserID, JobID, StatusID, SortOrder)
SELECT :myid, :userid, Jobs.ID, Jobs.StatusID, (@rank:=@rank+1) AS Rank
FROM Jobs
INNER JOIN JobStatuses ON Jobs.StatusID = JSID
CROSS JOIN (SELECT @rank := 0) temp
LEFT JOIN JobStatusGroups ON Jobs.SGrID = JSGID
WHERE ...
ORDER BY JobTitle DESC

on terms of performance I haven't ever noticed performance issues with using the variables instantiation inside a join on the table

one thing you can try is maybe the ORDER BY is messing up the order / count so you can try encapsulating it in a subselect

SELECT ID, StatusID, (@rank:=@rank+1) AS Rank
FROM
(   SELECT Jobs.ID, Jobs.StatusID
    FROM Jobs
    INNER JOIN JobStatuses ON Jobs.StatusID = JSID
    INNER JOIN JobStatusGroups ON Jobs.SGrID = JSGID
    WHERE Jobs.StatusID = 3
    ORDER BY JobTitle DESC
    LIMIT 5
)t
CROSS JOIN (SELECT @rank := 0) temp

Upvotes: 1

Related Questions