GROUP BY give priority to in MySQL

I have the following query.

$query_assignments = "SELECT * FROM tb_scheduler_assignments 
                      WHERE company_id = '".$company_id."' OR 
                      dept_id = '".$dept_id."' OR 
                      user_id = '".$user_id."' ORDER BY 
             开发者_StackOverflow         due_date GROUP BY purchase_id";

What I'd like is a single query solution that would keep the results for user_id over dept_id and dept_id over company_id.

For example:

• if the same purchase_id occurs for rows that were gotten via dept_id and user_id, then I only want the result for the user_id;
• if the same purchase_id occurs for rows that were gotten via company_id and user_id, then I only want the result for the user_id

First, you're interpolating variables in your SQL, which suggests you might be vulnerable to SQL injection. Just to make sure. PHP should offer prepared statements, or some escaping function.

Second, your SQL statement won't compile because you're using GROUP BY a but selecting * which includes at least three more columns.

Third, it sounds like you're misunderstanding SQL in thinking that it might, in a query such as you're trying to formulate (without UNION ALL), retrieve duplicate rows, i.e. the same row multiple times because it matches multiple criteria. This is not so.

The "single query" solution that I was looking for doesn't seem to exist, or if it does, it would be way slower than just handling all the sorting in php. So, I ran 3 separate queries, put each of them into arrays, and then in order to put them all into a final array with the hierarchy that I needed, I did the loops below to see if the purchaseID existed for the levels up the hierarchy. If it didn't, then I put it in to the array.

$finalArray = array();
foreach ($companyArray as $purchaseID => $companyData) {
    if (empty($deptArray[$purchaseID]) && empty($userArray[$purchaseID])) {
        $finalArray[] = $companyData;
    }
}
foreach ($deptArray as $purchaseID => $deptData) {
    if (empty($userArray[$purchaseID])) {
        $finalArray[] = $deptData;
    }
}
foreach ($userArray as $purchaseID => $userData) {
    $finalArray[] = $userData;
}

Then I can sort that array however I want and loop through that to echo what I need to. Not sure if that's the best way, but it worked well and is lightning fast for me.

$query_assignments = "SELECT *,
                      IF(user_id = {$user_id}, 30,
                        IF(dept_id = {$dept_id}, 20,
                          IF(company_id = {$company_id}, 10, 0)
                        )
                      ) as priority
                      FROM tb_scheduler_assignments 
                      WHERE company_id = {$company_id} OR 
                      dept_id = {$dept_id} OR 
                      user_id = {$user_id}
                      GROUP BY purchase_id
                      ORDER BY due_date, priority DESC";

You can make a virtual field with the if statement.

user_id:      30 pts
dept_id:      20 pts
company_id:   10 pts
else:          0 pts

WARNING: can not be Indexed!

Syntax FIX: GROUP BY and ORDER BY reordered