How to select all the last records that have a column with the same value?

Question

How to select all the last records that have a column with the same value?

Navigation

#1 by (3 votes)

2

I have a table where I insert an id from another table ('Idstock') and I wanted to select all the last records that have an identical id in that column

For example if I have 6 records the columns would be:

'idMov' / 'Idstock',/'Descripcion'/

  1     /       1   /     producto1

  2     /      1     /   producto1

  3     /      1     /   producto1

  4    /       2    /    producto2

  5    /       2    /    producto2

  6    /       3    /    producto3

I want to select all the last records that have the same value in 'Idstock' to come out like this:

'idMov' / 'Idstock', 'Descripcion'

3 / 1 / Producto1

5 / 2 / Producto2

6 / 3 / Producto3

I made this selection but it only brings me the last record as long as it has the same value in Idstock:

SELECT m.idMov,st.Idstock,st.Descripcion FROM movimientos m  inner join stock_inicial st on m.idStockI=st.idStockI WHERE m.idMov = (SELECT MAX(m.idMov) from movimientos m WHERE m.Idstock=2)

I would just bring this

'IdMov' / 'Idstock' / 'Descripcion' 

5 / 2 / Producto2

Could someone help me?

mysql

asked by Luis 08.05.2018 в 17:50

source

1 answer

Error to instantiate! Help with that error Doubts about pointer arrays

score 3 · Accepted Answer

Because of what you show in your data, the Idstock and Description columns are always the same.

Guiding me by the data that you are showing, the query is solved simply grouping by them two, and showing the max of the idMov column. It is important to understand that the concept of "last" does not exist in a database, unless you mark it in some way (a timestamp?).

SELECT MAX(m.idMov),st.Idstock,st.Descripcion 
FROM movimientos m inner join stock_inicial st on m.idStockI=st.idStockI
group by st.idstock, st.descripcion