little confused on query

[Cookies]

I have this query which works just fine:

SELECT MAX(mp.lmp_nbr) AS mp_price, 
       mp.mkt_loctn_id
  FROM MARKET_PRICE mp
    WHERE mp.mkt_price_dt > SYSDATE-1
      GROUP BY mp.mkt_loctn_id; 

output is this:

  MP_PRICE MKT_LOCTN_ID
---------- ----------------
     55.66 4000
     50.65 4001
     55.62 4002
     58.08 4003
     57.64 4004
     53.77 4005
     53.73 4006
     56.14 4007
     54.57 4008

I want to also display the date for those values (date is also in the same table). I thought I could use the below query by it is not working. It keeps giving me an error when I try to match on the group-by function MAX(mp.lmp_nbr).

SELECT MAX(mp.lmp_nbr) AS mp_price, 
       mp.mkt_loctn_id,
       (SELECT mkt_price_dt 
                  FROM MARKET_PRICE 
                       WHERE lmp_nbr = mp_price 
                          AND mkt_loctn_id = mp.mkt_loctn_id) AS the_date
FROM MARKET_PRICE mp
WHERE mp.mkt_price_dt > SYSDATE-1
GROUP BY mp.mkt_loctn_id; 

[Cookies]

Only solution I could come up with was:

SELECT m.lmp_nbr AS mp_price,
       m.mkt_loctn_id AS mp_loc,
       MAX(m.mkt_price_dt)  AS mp_date
FROM MARKET_PRICE m
WHERE 
       lmp_nbr IN 
          (SELECT MAX(mp.lmp_nbr)
             FROM MARKET_PRICE mp
              WHERE mp.mkt_price_dt 
                    BETWEEN TO_DATE('2003/07/14 4:00:00', 'YYYY/MM/DD HH24:MI:SS') 
                    AND TO_DATE('2003/07/15 04:00:00', 'YYYY/MM/DD HH24:MI:SS')
              AND mp.mkt_loctn_id = m.mkt_loctn_id)
GROUP BY lmp_nbr, mkt_loctn_id; 

[nandu]

Try this query below.

If you have duplicate price for the same location Id in the market price table for the given condition then you may get redundant values in the output.

SELECT a.*, b.mkt_price_dt
FROM market_price b,
(SELECT MAX(mp.lmp_nbr) AS mp_price, mp.mkt_loctn_id Location,
FROM MARKET_PRICE mp
WHERE mp.mkt_price_dt > SYSDATE-1
GROUP BY mp.mkt_loctn_id) a
WHERE a.mp_price = b.lmp_nbr
AND a.location = b.mkt_loctn_id
AND b.mkt_price_dt > SYSDATE-1

- Nandu

[slimdave]

Sounds like a case for analytic functions ...

Select
   mkt_loctn_id,
   mkt_price_dt,
   Max(lmp_nbr) over 
      (partition by mkt_loctn_id)
      mp_price
From
   market_price
Where
   mkt_price_dt > SysDate-1

Does that work for you?