CodeIgniter技术问询：如何按指定列数值及列最大值排序？

Hey there! Let's tackle your two questions one by one, using your provided table structure and code as context.

First, let's look at your current all_company function and the issue with sorting by the REV column.

Your Existing Context #

Your company table structure:

ID | Name | Rev
1  | name1| 65
2  | name2| 15
3  | name3| 96

Your original CodeIgniter code:

public function all_company($limit, $offset) { 
    $query = $this->db 
        ->select(['id','name']) 
        ->from('company') 
        ->order_by('MAX(rev)', 'desc') 
        ->get(); 
    return $query->result(); 
}

Fixing the REV Column Sort #

The problem here is that you're using MAX(rev) in your order_by clause—but your REV column already stores the total score for each individual company row. MAX() is an aggregate function meant to calculate the highest value across multiple rows, not for sorting individual rows by their own column value.

This will cause unexpected results (or even a SQL error, depending on your database engine) because you're using an aggregate function without a GROUP BY clause.

Here's the corrected code to sort directly by the REV column in descending order:

public function all_company($limit, $offset) { 
    $query = $this->db 
        ->select(['id','name', 'rev']) // Added rev if you need to display it, optional
        ->from('company') 
        ->order_by('rev', 'desc') // Removed MAX() - sort directly by the column
        ->limit($limit, $offset) // Don't forget you had limit/offset parameters!
        ->get(); 
    return $query->result(); 
}

Note: I added the limit() call since your function accepts $limit and $offset parameters—you weren't using them in your original code, which would defeat the purpose of pagination!

How to Sort by the Maximum Value of a POINTS Column #

Now, for your second question: sorting by the maximum value of a POINTS column. The approach depends on where the POINTS data lives:

Case 1: POINTS is a column in the company table

If POINTS is directly in the company table (like your REV column), it's straightforward—just sort by the column directly, same as we did with REV:

public function all_company_sorted_by_points($limit, $offset) { 
    $query = $this->db 
        ->select(['id','name', 'points'])
        ->from('company') 
        ->order_by('points', 'desc')
        ->limit($limit, $offset)
        ->get(); 
    return $query->result(); 
}

Case 2: POINTS is in a related table (e.g., company_points with multiple entries per company)

If POINTS comes from a child table where each company has multiple points entries (and you need the highest point value per company), you'll need to use GROUP BY with MAX():

public function all_company_sorted_by_max_points($limit, $offset) { 
    $query = $this->db 
        ->select(['company.id', 'company.name', 'MAX(company_points.points) as max_points'])
        ->from('company')
        ->join('company_points', 'company.id = company_points.company_id', 'left') // Join the related table
        ->group_by('company.id') // Group by company to calculate max points per company
        ->order_by('max_points', 'desc') // Sort by the calculated max value
        ->limit($limit, $offset)
        ->get(); 
    return $query->result(); 
}

This will give you each company with their highest points value, sorted from highest to lowest.

内容的提问来源于stack exchange，提问作者Ashraf