0

I have here a price list table. What I need to get is the latest amount of item.

Here's my current query. SELECT item, price, max(date) FROM price;

Here's what I got with my query.

Results:

item    | max(price)  | max(date)
----------------------------------
ballpen |   300       | 2014-12-11

Table Structure

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+  ID  +    Item   +   Code    +  Price    +   Date     +  
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+  1   +  Ballpen  +  itm001   +  300      + 2014-07-29 +
+-------------------------------------------------------+
+  2   +  Ballpen  +  itm001   +  701      + 2013-08-29 +   
+-------------------------------------------------------+
+  3   +  Ballpen  +  itm001   +  700      + 2014-12-11 +
+-------------------------------------------------------+
+  4   +  Pencil   +  itm002   +  250      + 2014-12-11 +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Expected Output

item    | max(price)  | max(date)
----------------------------------
ballpen |   700       | 2014-12-11
Pencil  |   250       | 2014-12-11

Any help will appreciate.

user3631428
  • 131
  • 3
  • 14

1 Answers1

1
SELECT 
  item,
  price, 
  `date`
FROM price
ORDER BY `date` DESC 
LIMIT 1

SQLFiddle here, also note that date is a reserved keyword.

Ende Neu
  • 15,581
  • 5
  • 57
  • 68
  • Sorry but to make it clear. I need the current/latest price of item 'ballpen'. – user3631428 Sep 08 '14 at 06:48
  • I make an update. What I need to get is like in my expected output. – user3631428 Sep 08 '14 at 06:55
  • Two things, first the date returned by your query is not in the structure you showed in your question (2014-09-08 is not in the dataset), second, is your date field a date column or a varchar? – Ende Neu Sep 08 '14 at 06:59
  • @edneNeu Sorry, I changed the field date to current_date. And (2014-08-09) is the current date right now. So it means current_date is also a reserve keyword? So I changed it again into 'dates' and it gets the right date. But the price wasn't. – user3631428 Sep 08 '14 at 07:12
  • I made an edit and added an SQL fiddle. – Ende Neu Sep 08 '14 at 07:19
  • the expected output was right. But the problem is the LIMIT 1. If I use LIMIT, how about my other item if I LIMIT to show only 1? – user3631428 Sep 08 '14 at 07:33
  • Then you want what Barmar put in as a similar question, from your question it was not clear. – Ende Neu Sep 08 '14 at 10:13