Return last record of each object

Asked

Viewed 1,140 times

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 is that each id always returns only the last record filtered by the last year and the item that will filter, and the first 2 characters of the item references the same and the last character references the item option (ex: The query of item 201 should return 2 records referring to id 2 and 3. id 1 should not be returned because its last item update was made in 2002 where item 20 is with option 2).

mysql sql

2 answers

1

I don’t know if I understand your question very well, but you can insert it with a counter

select count(id) from imovel where ano = 2000

Hence you need to save the result in an increment variable +1 that will always have a sequence.

  • No no. Filter will be the last item change per year

1


If what I needed was to get the value of each id where the year was higher, if that’s what 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 - The right answer

I reread your question and I think I understand the doubt better 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 records with id=1 where the 2 first characters of the item field match the first 2 of consultation "201" then I will sort the list down through the last (000) item character, to conclude I limit the query to only one record line.

You will then give him the following answer:

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

The result doesn’t interest you, so ignore it for the rest of us id just change in the where where you have id=1 would pass to id=2; id=3....

I hope I’ve helped more this time.

EDIT2 - LOOP

I leave here two sites with examples of how to make one loop, which will serve to go adding the id:

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

  • So. More can not come registration if the property is with item 202 with year later.

  • 1

    you can tell me from the 7 lines you have with records which ones you are interested in, for example: line 3, 6 and 7 where the year is the largest of each id. I will only mean what you need to help you better

  • Misunderstood. The first 2 characters of the item is what identifies the item. The last character and the option it selects in the system. Then within item 20 can be 1,2,3,4,5 and would be 201, 202, 203, etc. And can only return the line that has for example 201 of all Ids as last edition. If you have 201 in the last year of this id this id returns the line. More if you have 201 in the year 2002 and 202 in 2003 can not return.

  • I saw your reissue. But this should do for all Ids so I have the return of all at the same time. I know it’s very strange this table. More is 1998 this system.

  • The line 4 and 7 must be returned. The first line even having 201 and being another ID cannot be returned because line 3 has 202. The Indica line 3 which is a year later is the main one that 20 and the item and 2 and the option of this item.

  • 1

    I understand your need, and I think my code can already help you do what you want, if only you could find a way to show them all at the same time. I will try to take a look to see if I can get the query better. How will select be done? ->right in the database or ->will have some software/page to query, if it is the second option you can always do something like a FOR "for i = 1 to 3 do (select * fr.... id=i ....limit 1) if item = 201 writetxt then"

  • It’s going straight to the bank. I have several tables doing this, plus all with the same layout. I need to make a report since the software doesn’t have this report. And a very confusing table, when I saw today in the morning until it took me to understand how it worked.

Show 2 more comments

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.