Getting only the highest value in the database

Question

public ActionResult List_of_Winners(int id=0) 
{
    var winners = (from cat in db.Events_Category_tbl
        join can in db.Candidates_Info_tbl 
        on cat.events_category_id equals can.events_category_id
        where cat.events_info_id == id
        select new Candidates
        {
            events_category_name = cat.events_category_name,
            candidates_fullname = can.candidates_fullname,
            candidates_info_id = can.candidates_info_id,
            events_category_id = cat.events_category_id,
            no_of_votes = can.no_of_votes.Value
        }).OrderBy(x => x.no_of_votes).Distinct();

    return PartialView(winners);
}

I have 2 tables, the Events_Category_tbl & Candidates_Info_tbl then in one category there are many candidates registered. Then, what I want to do is that I need to get only the highest votes in the category. And this serve as the winner of the category.

My Candidates table looks like this:

candidates_info_id,
candidates_fullname,
events_category_id,
no_of_votes

My Category table looks this way:

events_category_id,
events_category_name

Then, I want a result in my query that in one category it has one winner of the candidates the one got the highest votes.

How am I gonna do that? Above is my code.

I think this does what you want, you just have to adapt to your fields: http://stackoverflow.com/questions/157786/how-do-i-get-the-max-row-with-a-group-by-in-linq-query — DWright, Mar 27 '15 at 03:26

Getting only the highest value in the database

0 Answers0