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: 
Assuming you want to find all rows where the max dx for a given p\_id has a location of 'A1'

```
SELECT t.* 
FROM   table1 t 
       INNER JOIN (SELECT p_id, 
                          Max(dx) dx 
                   FROM   table1 
                   GROUP  BY p_id) max_dx 
               ON t.dx = max_dx.dx 
WHERE  t.location_id = 'A1' 

```

[SQL Fiddle](http://sqlfiddle.com/#!2/50565/6)

If you don't like sub queries for some reason you can do an ANTI-JOIN on an inequality on the PK

```
SELECT t.* 
FROM   table1 t 
       LEFT JOIN table1 t2
       ON t.p_id = t2.p_id 
          and t.dx < t2.dx
WHERE
     t2.dx IS NULL
     and t.location_id = 'A1'

```

[SQL Fiddle](http://sqlfiddle.com/#!2/50565/11)
The dumbest way:

```
SELECT TOP 1 PRODUCT_ID
FROM YOUR_TABLE
WHERE
 LOCATION_ID = "A1" -- of course use parameter in real application!
ORDER BY
 ID DESC

```

HOWEVER! This is incorrect because one can not safely rely on sequencing of IDS,(even if it works in 99% of cases). Why don't you add a timestamp column? 

You can also use a MAX() function to get a subquery returning max ID:

```
SELECT PRODUCT_ID
 FROM YOUR_TABLE
 WHERE
 LOCATION_ID = "A1" -- of course use parameter in real application!
AND
 ID = SLECT MAX(ID) FROM YOUR_TABLE WHERE LOCATION_ID = "A1"

```