-
little confused on query
I have this query which works just fine:
PHP Code:
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:
Code:
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).
PHP Code:
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:
PHP Code:
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;
- Cookies
-
Is this of any help?!
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
Never give up !
Nanda Kumar - Vellore
-
Re: little confused on query
Sounds like a case for analytic functions ...
Code:
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|