Warning

 

Close

Confirm Action

Are you sure you wish to do this?

Confirm Cancel
BCM
Durkin Tactical Franklin Armory
User Panel

Posted: 1/7/2012 10:57:36 AM EDT
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
[#1]
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
[#2]
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
[#3]
Quoted:
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
[#4]
Quoted:
Quoted:
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
[#5]
Solution from orafaq.com:


SELECT id, item, value, month, max(month) OVER (PARTITION BY id, item, value)
FROM a
WHERE value IN (5, 6);
Close Join Our Mail List to Stay Up To Date! Win a FREE Membership!

Sign up for the ARFCOM weekly newsletter and be entered to win a free ARFCOM membership. One new winner* is announced every week!

You will receive an email every Friday morning featuring the latest chatter from the hottest topics, breaking news surrounding legislation, as well as exclusive deals only available to ARFCOM email subscribers.


By signing up you agree to our User Agreement. *Must have a registered ARFCOM account to win.
Top Top