If there a way to find last row from foreach loop, for multiple results?

[imgrooot]

Say I have a records table with multiple records. There could be multiple records with the same name but different amounts.

 

For Eg. Looking at the table below, the results should retrieve newest record-1 and record-2 because their amount is equals to or greater than 5.00. Record-3 is not selected because it falls below 3.00. 

record_id     record_name     record_amount
1             record-1        4.00
2             record-1        3.00
3             record-2        2.00
4             record-1        5.00
5             record-2        6.00
6             record-3        3.00

$get_records = $db->prepare("SELECT record_id, record_name FROM records WHERE record_amount >= :record_amount ORDER BY record_id DESC");
$get_records->bindValue(':record_amount', 5.00);
$get_records->execute();
$result_records = $get_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0) {
  foreach($result_records as row) {
    $record_id 	  = $row['record_id'];
    $record_name  = $row['record_name'];
  
    echo $record_name;
  }
}

Currently the query above outputs ALL the rows that matches the >=. What I want to do is select only ONE row of each unique record name that matches the criteria. And that row is typically the last row that was inserted. So the output would be like only these two.  How do I do that?

4             record-1        5.00
5             record-2        6.00

[benanamen]

SELECT
	record_id,
	record_name,
	Max( record_amount ) 
FROM
	table 
WHERE
	record_amount > 4 
GROUP BY
	record_name

http://sqlfiddle.com/#!9/8763e1/1

Edited July 14, 2017 by benanamen

[imgrooot]

Nice. "Group By" gives me single results of same name.

 

One thing. The "Max" won't work if i have it like this "Max(records.record_amount)". I want to make sure it works  if I have multiple tables joining.

Edited July 14, 2017 by imgrooot

[Jacques1]

You've been here long enough to understand the difference between SQL and PHP. Thread moved.

 

The solution you've acknowledged also contradicts your problem description. You said you want the latest record, now you're selecting the row(s) with the biggest amount. I also see no reason why the amount should be unique, so that won't even work.

[imgrooot]

You've been here long enough to understand the difference between SQL and PHP. Thread moved.

 

The solution you've acknowledged also contradicts your problem description. You said you want the latest record, now you're selecting the row(s) with the biggest amount. I also see no reason why the amount should be unique, so that won't even work.

 

The last record WILL have the biggest amount. 

 

Anyways, I have solved this issue.

[benanamen]

@Jaques1,

 

The OP just said the record is typically the last one, not that he necessarily wanted the last one but I do see how it could read that way.

 

And that row is typically the last row that was inserted.

 

If you read the example again with emphasis on the "because" the OP does making sense of what he wants.

 

For Eg. Looking at the table below, the results should retrieve newest record-1 and record-2 because their amount is equals to or greater than 5.00. Record-3 is not selected because it falls below 3.00. 

 

Based on the OP's attempted query and the desired results he posted, the query is correct.

 

OP, you're mistaken about not being able to use the table.column name.

Edited July 14, 2017 by benanamen

[Jacques1]

The query is semantically invalid and not even deterministic. In other words, you get purely random results, and if the OP (or his hoster) repairs the MySQL configuration and enables standards-compliant mode, the whole thing blows up:

ERROR 1055 (42000): 'test.records.record_id' isn't in GROUP BY

Take the following subset from the example data:

record_id     record_name     record_amount
1             record-1        4.00
2             record-1        3.00
4             record-1        5.00

You group by the record name, you calculate the biggest amount within this group (which is 5.0), and then you select “the record ID”. Well, what ID? The group name doesn't determine any ID. At this point, all proper database systems blow up. MySQL in sloppy mode let's you do it, but it will pick an ID at random. You might get 1 or 2 or 4. If you got 4, that's just luck.

 

And who said the amount is unique? Is it really unique per column definition, or is it just let's-hope-it-will-be-unique?

 

The OP is obviously too clueless and lazy to understand those problems. But you should.

 

 

 

The last record WILL have the biggest amount.

 

Then you either cannot count, or your example is bogus.

record_id     record_name     record_amount
1 <--         record-1        4.00 <--
2 <--         record-1        3.00 <-- 
3             record-2        2.00
4             record-1        5.00
5             record-2        6.00
6             record-3        3.00

Edited July 14, 2017 by Jacques1

[benanamen]

@Jaques1, after looking it over I see what you mean. The MAX amount would be correct but the ID could be from a completely different record.

 

OP, The query is no good.

[gizmola]

Everything Jacques pointed out is valid.

 

These types of queries against the same table have to be built from the inside out.

 

Step #1: filter the raw data by the criteria

 

SELECT * FROM mytable WHERE record_amount > 2

 

This query is going to eliminate any rows that we aren't interested in.

 

Step #2 GROUP and summarize.

 

In this case, we can GROUP BY record_name AND get the MAX(record_id) for each group, which makes it easy to get the id's we want.

 

SELECT MAX(record_id) as id, record_name FROM 
(SELECT *
FROM
	mytable 
WHERE
	record_amount > 2) a
GROUP BY record_name) b

At this point you have the last id allocated per group from the set of rows that fit the original criteria.

 

Finally

 

Add a join back to the original table on ID, and we have the desired result:

 

 

SELECT * FROM mytable
JOIN
    (SELECT MAX(record_id) as id, record_name FROM 
        (SELECT *
            FROM mytable 
            WHERE
	    record_amount > 2) a
    GROUP BY record_name) b
ON mytable.record_id = b.id

Based on Benamen's SQLFiddle: http://sqlfiddle.com/#!9/8763e1/15/0

[benanamen]

Now that we have gone through the OP's question, I can't help wondering if this was an XY Problem. I had the XY itch from the start but didn't scratch it.

 

OP, what is the actual overall task or problem you are trying to solve? (Not your attempt at it)

[Jacques1]

If wouldn't expect the OP to come back, he never seemed to actually care about this task.

[gizmola]

He is Groot