MYSQL - Select multiple rows in one query

Question

claimId is foreign key

Table  (statusTable)
Id_          _chaseStatus_       _claimId_
1               Open               CL001
2               Close              CL002    
3               Open               CL001
4               Open               CL003
5               Open               CL001
6               Open               CL003



$query =    "SELECT * FROM statusTable ";
$query .=   "WHERE (`chaseStatus` = 'Open') ";
$query .=   "AND (id = (SELECT MAX(id) FROM statusTable))";





while($row = mysqli_fetch_assoc($result)){      
        $items[] = $row;
    }

    //$items = array_reverse($items ,true);

    foreach($items as $item){
      $claimId  = $item["claimId"];
      echo $claimId;
    }

My query gives me only one column which is highest id.

But I am trying to get only 'Open' from 'chaseStatus' for each 'claimId' (with highest id) like;

How can I get like this

for id = 5 : CL001

AND

for id = 6 : CL003

Any ideas?

Robbert · Accepted Answer · 2014-12-16 23:47:34Z

4

You can retrieve the highest id of the claimid using group by.

$query =    "SELECT max(Id) as Id,claimId FROM statusTable ";
$query .=   "WHERE (`chaseStatus` = 'Open') ";
$query .=   "GROUP BY claimId";

THis should result in the following table

Id  claimId
5   CL001
6   CL003

Here's a SQL Fiddle: http://sqlfiddle.com/#!2/b000e/1

edited Dec 16, 2014 at 23:47

answered Dec 16, 2014 at 23:38

Robbert

6,6226 gold badges37 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-12-17 01:23:10Z

0

You can do what you want by including the 'Open' condition in the subquery:

SELECT *
FROM statusTable
WHERE `chaseStatus` = 'Open' AND
      id IN (SELECT MAX(id) FROM statusTable WHERE chaseStatus = 'Open' GROUP By ClaimId);

I think it is redundant to have the open condition in the outer query, so this should work for you:

SELECT *
FROM statusTable
WHERE id IN (SELECT MAX(id) FROM statusTable WHERE chaseStatus = 'Open' GROUP BY ClaimId);

edited Dec 17, 2014 at 1:23

answered Dec 16, 2014 at 23:41

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Gordon Linoff Over a year ago

@Royertan . . . This would work better if you had more columns that you wanted to pull from each row. With just three columns, his approach is very reasonable.

ErcanE Over a year ago

I tried but only get one result! which is 'max id'.I need each claimId (Only 'Open') but highest one.Thanks again

Collectives™ on Stack Overflow

MYSQL - Select multiple rows in one query

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related