Jump to content
Sign in to follow this  
imgrooot

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

Recommended Posts

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

Share this post


Link to post
Share on other sites

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 by imgrooot

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

@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 by benanamen

Share this post


Link to post
Share on other sites

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 by Jacques1

Share this post


Link to post
Share on other sites

@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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.