Welcome Guest, Not a member yet? Register   Sign In
problem in finding second maximum value
#1

[eluser]Bigil Michael[/eluser]
can u help me???
iam in a majour problem,no one responding
i want to find out the second maximum value
Quote:Select candidate_votes,max(candidate_votes) from candidate_details where candidate_votes < (Select max(candidate_votes) from candidate_details) And elect_year = $elect_year And constituency_id = $consti

this code is running smoothly as localhost

when i upload it to a server
it shows error

can anyone help me?
please....
#2

[eluser]John_Betong_002[/eluser]
&nbsp;
What is the server error?
&nbsp;
&nbsp;
&nbsp;
#3

[eluser]Bigil Michael[/eluser]
thanks for your reply

this is the code i used
Quote:function select_top2($consti,$elect_year)
{
$result_category = $this->db->query("Select candidate_votes,max(candidate_votes) from candidate_details where candidate_votes < (Select max(candidate_votes) from candidate_details) And elect_year = $elect_year And constituency_id = $consti");
return $result_category->row();
}

this is the error
Quote:A Database Error Occurred

Error Number: 1140

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Select candidate_votes,max(candidate_votes) from candidate_details where candidate_votes < (Select max(candidate_votes) from candidate_details) And elect_year = 2005 And constituency_id = 2
#4

[eluser]Bigil Michael[/eluser]
can anyone help me??

urgent
#5

[eluser]John_Betong_002[/eluser]
&nbsp;
Look at the error message "there is no GROUP BY clause"

I think you should Google for something like PHP MYSQL "GROUP BY" Tutorial and see if you can find where to insert the missing GROUP BY.

Best of luck Smile

Code:
function select_top2($consti,$elect_year)
{
  $qry =
        “ SELECT candidate_votes,max(candidate_votes)
          FROM    candidate_details
          WHERE   candidate_votes < (Select max(candidate_votes)
          FROM    candidate_details)  
          AND     elect_year = $elect_year And constituency_id = $consti”;
          
  $result_category = $this->db->query($qry);
  
  echo $this->db->last_query();
          
  return $result_category->row();
}//
&nbsp;
&nbsp;
&nbsp;
#6

[eluser]Bigil Michael[/eluser]
when i execute this query directly on database it shows error like this
Quote:#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

i think the problem is here

select candidate_votes,max(candidate_votes)
#7

[eluser]Bigil Michael[/eluser]
now i changed my query to
Quote:SELECT max(candidate_votes) as cdv
FROM candidate_details
WHERE candidate_votes < (Select max(candidate_votes)
FROM candidate_details)
AND elect_year = '2011' And constituency_id = '1'

here error removed

but some times result is not correct
#8

[eluser]John_Betong_002[/eluser]
Can you try this:

Code:
SELECT *
FROM     candidate_details
ORDER BY candidate_votes
DESC
LIMIT    0,5
&nbsp;
If that gives consistent satisfactory results then write some code to select the values from the result.
&nbsp;
&nbsp;
#9

[eluser]Bigil Michael[/eluser]
i used this code and it is running smoothly
Quote:SELECT *
FROM candidate_details WHERE elect_year = '2005' And constituency_id = '1'
ORDER BY candidate_votes
DESC

how i select the second maximum value????
#10

[eluser]John_Betong_002[/eluser]
Try this:

Code:
$sql=
"
  SELECT *
  FROM   candidate_details WHERE elect_year = ‘2005’ And constituency_id = ‘1’
  ORDER BY candidate_votes
  DESC
  LIMIT    1,1
";
$query = $this=>db->query($sql);

if($query->num_rows() > 0)
{
  $row = $query->row();

  // display results
  echo '<pre>';
    print_r($query->row());
  echo '</pre>';
}
EDIT: spelling
&nbsp;
&nbsp;
&nbsp;




Theme © iAndrew 2016 - Forum software by © MyBB