0

i have a question about to select all data with distinct item column and max date column.

table itemprice

Based on figure above(table itemprice), item '010070056001' has 3 row with different effect_date and others column. My question are how can i select all column, in the same time i distinct 'item' column and the effect_date must be the max?

GagakAngkasa
  • 39
  • 1
  • 9
  • Values in column brk_cost##1 for item 010070056001 is different. So you could not distinct into a single row. Go with **group by** . – PriVictor Jun 09 '16 at 04:53
  • It depends a little on which RDBMS you're using but either way, lots of answers, some of which are correct – Strawberry Jun 09 '16 at 04:57
  • @PriVictor i just want to distinct item column and make sure after distinct the effect_date must be the latest(max) – GagakAngkasa Jun 09 '16 at 05:05
  • use 'group by' and 'having' clauses. 'groupby' groups the rows with itemid and having clause used to filter the max effect date. i.e., having max(effect_date). – PriVictor Jun 09 '16 at 05:13
  • Select item, vend_num, effect_date, brk_qty##1, brk_qty##2, brk_qty##3, brk_qty##4, brk_qty##5, min(brk_cost##1) from itemprice group by item, vend_num, effect_date, brk_qty##1, brk_qty##2, brk_qty##3, brk_qty##4, brk_qty##5 having max(effect_date); – PriVictor Jun 09 '16 at 05:14
  • Have you tried a GROUP BY and MAX()? – Adrian Jun 09 '16 at 05:19
  • @Adrian i've tried group by and max like this, "select item , max(effect_date) as effect_date from itemvendprice_mst group by item" but how can i select * with GROUP BY AND MAX()?? – GagakAngkasa Jun 09 '16 at 06:11
  • SELECT all the columns you need and GROUP BY all your non-aggregate columns should work. – Adrian Jun 09 '16 at 06:53

0 Answers0