Question: I have a table structure like the following

```
idx   p_id    location_id  

1     CTX      A1
2     CTX      A2
3     ABC      A3
4     ABC      A1

```

idx is auto increment number and indicating the latest location
(for product CTX case, it transfered from A1 to A2)

NOW, what I want to find is any product in location A1.
I should find only product ABC, since product CTX moved from A1 to A2, it no longer in the A1

Is there easy way to perform with SQL query?

Answer: 
Okay, after re-reading your question, I think this is more what you need:

```
SELECT idx, p_id, location_id
FROM tablename t0 JOIN (
  SELECT MAX(idx) AS m
  FROM tablename
  GROUP BY p_id) t1
  ON t0.idx = t1.m
WHERE t0.location_id = 'A1';

```
You can use ROW\_NUMBER to get the latest location without using a correlated subquery. 

```
SELECT * FROM
(
    SELECT p_id, location_id, ROW_NUMBER() OVER(PARTITION BY p_id ORDER BY idx DESC) RowNumber
) x
WHERE RowNumber = 1 AND location_id = 'A1'

```