1

Good Afternoon,

I'm trying to build a simple inventory system and i wish i could have a query able to select the last entry for each product (component ; ref) and also the one just before the last one for each product (to compare).

This is what i have so far :enter image description here

My query so far is :

SELECT u1.*
FROM $usertable u1
JOIN ( 
SELECT component, ref, MAX(date) date
FROM $usertable
GROUP BY component, ref 
) u2
USING(component, ref, date) ORDER BY component ASC, ref ASC

Can you please help me to figure out the solution ?

Bastien Bastiens
  • 419
  • 6
  • 16
  • You need `WITH` to make this elegant, but [as near as I can tell](http://stackoverflow.com/q/1382573/2589202), its [not yet supported](http://bugs.mysql.com/bug.php?id=16244). – crthompson Apr 04 '14 at 21:34
  • http://stackoverflow.com/questions/324935/mysql-with-clause... if its mysql then WITH wont work – John Ruddell Apr 04 '14 at 21:41

2 Answers2

1

You can do so by using a subquery in where clause

SELECT u1.*
FROM $usertable u1
WHERE (
        SELECT  COUNT(*) 
        FROM $usertable u2
        WHERE u2.component= u1.component
         AND u2.ref= u1.ref
         AND  u2.date>= u1.date
        ) <= 2
ORDER BY component ASC, ref ASC
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
0

It should work. http://www.sqlfiddle.com/#!2/288980/1

You could try specifying the Join condition.

SELECT u1.*
FROM $usertable u1
JOIN ( 
    SELECT component, ref, MAX(date) date
    FROM $usertable
    GROUP BY component, ref 
      ) u2
  on u2.component = u1.component
  and u2.ref = u1.ref
  and u2.date = u1.date
ORDER BY component ASC, ref ASC

http://www.sqlfiddle.com/#!2/288980/1

Ron Smith
  • 3,241
  • 1
  • 13
  • 16