Warning

 

Close

Confirm Action

Are you sure you wish to do this?

Confirm Cancel
Member Login

Site Notices
Posted: 1/7/2012 10:57:36 AM EDT
[Last Edit: 1/7/2012 12:10:10 PM EDT by abnk]
Hi all:

I'm having a bit of trouble with the following:

I have table A:

ID––––ITEM––––––-VALUE–––––-MONTH
1––––––X––––––––––5––––––––––1
1––––––X––––––––––10 ––––––––– 2
1–––––––– X–––––––––– 5 –––––––– 3
2–––––––– Y ––––––––- 6 –––––––– 1
2–––––––– Y –––––––––– 12––––––––––- 2


I want to pull the ID, ITEM, VALUE, MONTH, and MAX(MONTH) where value IN (5, 6). So:

ID ITEM VALUE MONTH MAX(MONTH)
1 X 5 1 3
1 X 5 3 3
2 Y 6 1 2


The trouble I am having that MAX is a group function and will not run with a single-row query.

Is there a way to do this without running MAX as a separatate query and than running a third query to add the MAX value?

Is there a good online resouce to get this answered?

Thanks.
Link Posted: 1/7/2012 12:13:51 PM EDT
[Last Edit: 1/7/2012 12:14:47 PM EDT by JosephK]
You will need to fix any syntax problems, but try something like this:

select a.ID, a.ITEM, a.VALUE, a.MONTH, b.max_month
from table_a as a
inner join (
select ID, ITEM, VALUE
, max(MONTH) as max_month
from table_a
group by ID, ITEM, VALUE
) as b
on a.id = b.id and a.item = b.item and a.value = b.value
where a.value IN (5, 6)

Your choice to display the max month on each row of the group is what makes this a bit more complicated.
Link Posted: 1/7/2012 12:34:20 PM EDT
[Last Edit: 1/7/2012 12:35:20 PM EDT by abnk]
Thank you very much.

I might be able to workaround without displaing the MAX in each row by importing the data in Excel and filling the MAX column later, but they would have to be grouped by a unique MAX. Does that make it easier?
Link Posted: 1/7/2012 8:20:15 PM EDT
Originally Posted By abnk:
Thank you very much.

I might be able to workaround without displaing the MAX in each row by importing the data in Excel and filling the MAX column later, but they would have to be grouped by a unique MAX. Does that make it easier?


That would make it easier on the SQL side, but not on the Excel side. :)

Why do you want to see both the individual "max" and the aggrigated "max" at the same time?
Link Posted: 1/8/2012 4:25:00 AM EDT
Originally Posted By JosephK:
Originally Posted By abnk:
Thank you very much.

I might be able to workaround without displaing the MAX in each row by importing the data in Excel and filling the MAX column later, but they would have to be grouped by a unique MAX. Does that make it easier?


That would make it easier on the SQL side, but not on the Excel side. :)

Why do you want to see both the individual "max" and the aggrigated "max" at the same time?


The individual MAX is all I need. I am short on time so I decided to go with the workaround for now. I ran a single row query to retrieve the records I wanted. Then I ran the MAX query against the IDs selected in the first query. Then I ran a third query against the first two to add the MAX column.

I will revisit this once I am done because I am sure it will come up again.

Thanks for the help.
Link Posted: 1/8/2012 7:14:04 AM EDT
[Last Edit: 1/8/2012 7:15:10 AM EDT by abnk]
Solution from orafaq.com:


SELECT id, item, value, month, max(month) OVER (PARTITION BY id, item, value)
FROM a
WHERE value IN (5, 6);
Top Top