Return last record of each object [closed]

Question

Return last record of each object [closed]

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I have the following table:

         imovel
ano | id |  item | valor

Records:

2000| 1 |  201 | null
2001| 1 |  211 | 10
2002| 1 |  202 | null
2000| 2 |  201 | null
2001| 2 |  221 | 17
2002| 2 |  211 | 14
2000| 3 |  201 | null

What I need and always return from each id only the last record filtered by the last year and the item to be filtered, the first 2 characters of the item refers to the same and the last character refers to the option of the item (ex: The query of item 201 should return 2 records referring to id 2 and 3. The id 1 should not be returned because its last item update was made in 2002 where item 20 is with option 2).

mysql sql

asked by anonymous 03.08.2017 / 15:53

2 answers

1

I do not know if I understand your question very well, but you can do insert with a counter

select count(id) from imovel where ano = 2000

So you need to save the result to a +1 increment variable that will always have a string.

03.08.2017 / 16:07

Deserialize json string array to string array Doubt over struct and list in c

score 1 · Accepted Answer

If I pretended what I needed was to get the value of each id where the year was greatest, if it is, try the following code.

try this code:

SELECT * FROM imovel 
WHERE id=1 AND item=201 
ORDER BY year DESC
LIMIT 1

Returned lines:

         imovel
ano | id | item | valor

2000| 1 |  201 | null

EDIT1 - SURE ANSWER

I reread your question again, and I think I have a better understanding of the doubt test the following code:

SELECT * FROM imovel
WHERE id=1 AND mid(item, 1, 2)=20
ORDER BY mid(item, 3, 1) DESC
LIMIT 1

I make a select all register with id=1 where the 2 first characters of the item field match the first 2 of the query < strong "1" then I will sort the list in descending way through the last (00 0 ) character of the item field, to conclude I limit the query to only one row of record.

You will then be given the following answer:

         imovel
ano | id | item | valor
2002| 1  | 202  | null

The result does not interest you, so ignore, for the remaining id just change in where where it has id=1 would pass id=2 ; id=3 ....

I hope I have helped you more this time.

EDIT2 - LOOP

Here are two sites with examples of how to make a loop , which will serve to add the id:

It will also be necessary to not show the values "202" a condition can be made if :

mysql.com