0

I need to select the last register for each budget_id, which is a foreign key.

For example, if my table is:

id | budget_id | comment
1  | 8 | Test 1
2  | 8 | Test 2
3  | 5 | Test 1
4  | 5 | Test 2

I need to retrieve like this:

id | budget_id | comment
4  | 5 | Test 2
2  | 8 | Test 2

But it is always showing the first register for each budget_id. Intead of Test 2, it shows Test 1.

Here is my SQL so far:

SELECT *
  FROM note
 GROUP BY budget_id
 ORDER BY id DESC

I have tried adding HAVING, sub selects selecting the MAX field created_at, but nothing.

EDIT

After doing what I was told, I kind of managed to make it work, but now the query only returns the SECOND register of each budget_id. Here is my SQL:

SELECT  MAX(n.id),
        c.name, 
        MAX(n.comment)
  FROM  note n,
        budget b,
        customer c
 WHERE  n.budget_id = b.id
   AND  b.customer_id = c.id
 GROUP  BY n.budget_id
 ORDER  BY n.id DESC
  • Possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Vincent Savard Dec 15 '15 at 19:15

3 Answers3

0

Select the maximum id for each budget_id by using group by:

SELECT MAX(id) as id, budget_id, MAX(comment) as comment
FROM table GROUP BY budget_id

While MySQL will not require the MAX on comment column, proper SQL does require an aggregate for every column not in the group by (if you tried the same query on Oracle with out the aggregate function on comment column, you would get an error)

DBug
  • 2,502
  • 1
  • 12
  • 25
0

You must select max on id and comment then group by budget_id.

SELECT max(id) id, max(comment) as comment, budget_id from table group by budget_id order by max(id)

Melkikun
  • 46
  • 1
  • 7
0

I think the query is this

select id, budget_id,[comment]
from note 
where id in 
(select max(id)
from note 
group by budget_id)
order by id desc

In your question , I only see that you want get the max id for every budget_id , if your purpose is not this ,please give more sample or describe clearly!

Walker.li
  • 114
  • 8