I am trying to add one column Rownumber in my procedure..
My expected out put is same like Sql
SELECT ROW_NUMBER() OVER (PARTITION BY Column ORDER BY Column DESC) AS RowNumber
For this out put in mySQL I did like this
DELIMITER $$
DROP PROCEDURE IF EXISTS `Test2` $$
CREATE PROCEDURE `Test2`(
fromDate DATETIME,
toDATE DATETIME,
sat int,
sun int,
fromhr int,
tohr int
)
BEGIN
SELECT DISTINCT
g.name,
g.graphid,
i.hostid,
i.itemid,
@row_num := IF(@prev_value=g.graphid,@row_num+1,1) AS RowNumber,
@prev_value := g.graphid,
TRUNCATE((tu.value_max)/(1024*1024),2) AS value_max,
FROM_UNIXTIME(tu.clock,'%H') Hour,
FROM_UNIXTIME(tu.clock,'%Y-%m-%d %h:%i:%s:%p') Date,
ci.Bandwidth AS Capacity,
TRUNCATE((tu.value_min)/(1024*1024),2) AS value_min,
TRUNCATE((tu.value_avg)/(1024*1024),2) AS value_avg,
ci.NewInterfaceName,
lm.LocationName,
bfm.BusinessFunctionCode,
ltm.LinkTypeCode,
bm.BuildingCode,
spm.ProviderCode
FROM
(SELECT @row_num = 1) x,
(SELECT @prev_value := '') y,
trends_uint tu
INNER JOIN items i ON i.itemid=tu.itemid
INNER JOIN hosts_groups hg ON hg.hostid=i.hostid
INNER JOIN graphs_items gi ON gi.itemid=i.itemid
INNER JOIN graphs g ON g.graphid=gi.graphid
INNER JOIN hosts h ON h.hostid=hg.hostid
LEFT JOIN flip_linkutilisation.categorisedinterfaces ci on ci.graphid=g.graphid
LEFT JOIN flip_linkutilisation.buildingmaster bm ON bm.BuildingID=ci.BuildingID
LEFT JOIN flip_linkutilisation.businessfunctionmaster bfm ON bfm.BusinessFunctionID=bm.BusinessFunctionID
LEFT JOIN flip_linkutilisation.locationmaster lm ON lm.LocationID=bm.LocationID
LEFT JOIN flip_linkutilisation.linktypemaster ltm ON ltm.LinkTypeID=ci.LinkType
LEFT JOIN flip_linkutilisation.serviceprovidermaster spm ON spm.ServiceProviderID=ci.ServiceProviderID
WHERE g.graphid=1252
order by value_max DESC
;
END $$
DELIMITER ;
But In the out put the number of rows are doubled..
Row number coming correctly...but each row is repeating..
What mistake i did...
Please help me to solve
Thank you