2

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?

King Skippus
  • 3,801
  • 1
  • 24
  • 24
user1084885
  • 77
  • 1
  • 1
  • 6

4 Answers4

3

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

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

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
1

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';
King Skippus
  • 3,801
  • 1
  • 24
  • 24
  • After re-reading the question, I think this is not what the OP is looking for. I'm working on a refinement... – King Skippus Dec 28 '12 at 18:56
  • And to be fair, you should probably award Conrad Frix the answer since he read your question correctly and came up with the query before I did. :) – King Skippus Dec 28 '12 at 19:06
0

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"
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
itadapter DKh
  • 596
  • 3
  • 7
  • If you have four spaces before each line you post will be nicely formatted. If you select your code and press the `{}` button it will format your code. Also TOP 1 won't work in mysql – Conrad Frix Dec 28 '12 at 19:48
0

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'
jlnorsworthy
  • 3,914
  • 28
  • 34
  • Unfortunately mySQL doesn't support Row_Number(). Although you could do it [with this](http://stackoverflow.com/a/1895127/119477) but its hard to get the (PARTITION BY to work) – Conrad Frix Dec 28 '12 at 18:47
  • if more than one product id have same location id then also it will give only the latest result. – sourcecode Dec 28 '12 at 18:50
  • @ConradFrix oops, I missed the mysql tag. I'll leave the answer as it may be useful for others. – jlnorsworthy Dec 28 '12 at 18:55
  • @sourcecode I disagree. The Partition By clause will reset the row counter for each unique p_id. Each unique p_id will have a record with RowNumber = 1. If that row also hapopens to lave a location_id of "A1" then it will be returned in the result set. – jlnorsworthy Dec 28 '12 at 18:58
  • 1
    as an aside you can still write this without a subquery or row_number [see here](http://sqlfiddle.com/#!2/50565/11) – Conrad Frix Dec 28 '12 at 19:46
  • @ConradFrix Nice, I like this solution. – jlnorsworthy Dec 28 '12 at 20:36