Get a few values using a max in the same query

My table is like this:

audit cons articulo  date         bodega  amount    lote
---  |----|-----  |------------|--------|---------|------
11   | 2  | 10    | 04/03/2009 | BMP    | 399     |454
23   | 4  | 11    | 03/03/2009 | BMO    | 244     |787
31   | 2  | 10    | 04/03/2009 | BMP    | 555     |454
45   | 5  | 12    | 03/03/2009 | BNO    | 300     |786
23   | 7  | 11    | 03/03开发者_如何转开发/2009 | BIM1   | 200     |123
61   | 4  | 10    | 04/03/2009 | BIM1   | 500     |783
75   | 5  | 13    | 24/01/2008 | BMP    | 600     |567
75   | 1  | 13    | 24/01/2008 | BMP    | 700     |777

I need to select the amount of the last date for each articulo , bodega and lote

audit + cons = pk of the table

Example:

audit cons articulo  date        bodega  amount      lote
---  |----|-----  |------------|--------|--------- |
11   | 2  | 10    | 04/03/2009 | BMP    | 399      |  454
23   | 4  | 11    | 03/03/2009 | BMO    | 244      |  787
45   | 5  | 12    | 03/03/2009 | BNO    | 300      |  786
23   | 7  | 11    | 03/03/2009 | BIM1   | 200      |  123
61   | 4  | 10    | 04/03/2009 | BIM1   | 500      |  783
75   | 5  | 13    | 01/01/2009 | BMP    | 600      |  567
75   | 1  | 13    | 01/01/2009 | BMP    | 700      |  777

the problem is that i cant get the amount , when i use :

select amount, bodega, articulo, max(date) ,lote
 from table 
 group by amount, bodega, articulo,lote

this repeat a lot of rows , any help will be great

Oracle 9i+ supports ROW_NUMBER:

WITH example AS (
  SELECT t.*,
         ROW_NUMBER() OVER(PARTITION BY t.bodega, t.articulo
                               ORDER BY t.audit) AS rnk
    FROM YOUR_TABLE t)
SELECT e.*
  FROM example e
 WHERE e.rnk = 1

Otherwise, you can do a join to a derived table (but this will return duplicates if there's more than one bodega/articulo with the same highest date value):

SELECT a.*
  FROM YOUR_TABLE a
  JOIN (SELECT t.bodega, t.articulo, MAX(t.date) AS maxdate
          FROM YOUR_TABLE t
      GROUP BY t.bodega, t.articulo) b ON b.bodega = a.bodega 
                                      AND b.articulo = a.articulo 
                                      AND b.maxdate = a.date

Probably a combination of MIN/MAX using KEEP will work

SELECT 
  t1.amount, t1.bodega, t1.articulo, t1.date
FROM 
  table t1 join 
  (select 
     bodega, articulo, max(date) date
   from 
     table 
   group by 
     amount, bodega, articulo
  ) on t1.bodega=t2.bodega and t1.articulo=t2.articulo and t1.date=t2.date

Just try this...

select * from table 
where (audit,cons,articulo ,date,bodega,Lote )
in (select min(audit),min(cons),articulo ,max(date),bodega,Lote  
    from table 
    group by articulo , bodega ,lote)