SUN SUN - 4 months ago 6
SQL Question

simplifying non-parametrized mysql queries

I have a problem with mySql query below

SELECT name, hospitalID, currentAvgRating, rank
FROM ( SELECT name,hospitalID,currentAvgRating,city,
@curRank := @curRank + 1 AS rank
FROM hospitals h, ( SELECT @curRank := 0) r
ORDER BY currentAvgRating DESC
) toplist
WHERE toplist.hospitalID = @hospitalID
and city = @city


This query I am using to find the rank of particular item & it is working properly. But while runining in program I get Fatal errors of Parameter '@curRank' must be defined. But that is mysql syntax then how can I get it's parameters?

UPDATE

string str = "SELECT name, hospitalID, currentAvgRating, rank FROM (SELECT name,hospitalID,currentAvgRating,city,@curRank := @curRank + 1 AS rank FROM hospitals h, (SELECT @curRank := 0) r ORDER BY currentAvgRating DESC) toplist WHERE toplist.hospitalID = @hospitalID and city = @city";
con.Open();
MySqlCommand cmd = new MySqlCommand(str, con);
cmd.Parameters.AddWithValue("@hospitalID", generalID.Text);
cmd.Parameters.AddWithValue("@city", cityName.Text);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();

Answer

You can get the rank for a particular hospital/city pair without a rank. Here is a close approximation to your query:

select count(*) + 1 as ranking
from hospitals h cross join
     (select h.currentAvgRating
      from hospitals h
      where h.hospitalID = @hospitalID and h.city = @city
     ) hh
where h.currentAvgRating > hh.currentAvgRating;

Unlike your code, this gives all hospitals with the same rating, the same ranking.

If you don't want to change the code, then refer to this answer. Actually, I'll quote the relevant part:

I have to add

;Allow User Variables=True

to the connection string