Home » SQL & PL/SQL » SQL & PL/SQL » how to select a non-group by value from a group by clause (10.2.0.1)  () 1 Vote
how to select a non-group by value from a group by clause [message #287833] Thu, 13 December 2007 13:20 Go to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
Hello,
I swear I knew at one point in time how to do this but I just can't remember.
I have a table with 3 columns
tstamp - timestamp of type Date
yyyymm - integer of the format YYYYMM (for the year, month)
somevalue - an integer

I want to group by yyyymm, and within each group select the somevalue associated with the last-occurring tstamp

So for example:
| TSTAMP | YYYYMM | somevalue
| 1/01/07 | 200701 | 9 |
| 1/25/07 | 200701 | 1 |
| 2/04/07 | 200702 | 8 |
| 9/12/07 | 200709 | 5 |
| 2/01/07 | 200702 | 2 |

Needs to give
| YYYYMM | somevalue
| 200701 | 1 |
| 200702 | 8 |
| 200709 | 5 |

How would I go about doing this?
Re: how to select a non-group by value from a group by clause [message #287834 is a reply to message #287833] Thu, 13 December 2007 13:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually you want the first somevalue for each YYYYMM when you sort these ones in tstamp descending order.
Have a look at RANK function.

Regards
Michel
Re: how to select a non-group by value from a group by clause [message #287842 is a reply to message #287834] Thu, 13 December 2007 14:25 Go to previous messageGo to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
Yes michel, that is what I want. I'm sorry, perhaps it is just lack of experience with analytic functions, but I do not see how RANK helps.
Re: how to select a non-group by value from a group by clause [message #287843 is a reply to message #287842] Thu, 13 December 2007 14:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You want to (logically) partition your table by YYYYMM and in each partition you want the first one when ordering by tstamp in descending order.

Regards
Michel
Re: how to select a non-group by value from a group by clause [message #287847 is a reply to message #287833] Thu, 13 December 2007 15:11 Go to previous messageGo to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
Sorry I still need some help:
I can do
SELECT yyyymm, tstamp, somevalue
FROM (
SELECT yyyymm,
tstamp,
somevalue,
RANK() OVER (PARTITION BY yyyymmORDER BY tstamp DESC) myrank
FROM myTable;
)
WHERE myrank=1;

Which would give me the correct values but 2 issues:
1) With no GROUP BY clause how do I resolve issues if 2 rows had the same tstamp and therefore the same rank (not likely, but I want to be sure)?

2) Is there no way to do this without nested select queries? I have to preform a full outer join with these results but because of oracle bug #5765958 nested select queries + a full outer join generate an ORA-00600 [qcscpqbtxt]

Thanks a lot,
Toga
Re: how to select a non-group by value from a group by clause [message #287850 is a reply to message #287847] Thu, 13 December 2007 15:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) In this case which one do you want to choose? Only you can answer this.

2) Are you sure this will happen in this case?
Try to rewrite it in another way, with "with" clause for instance. I don't have the full information so I can't say more.

Regards
Michel
Re: how to select a non-group by value from a group by clause [message #287854 is a reply to message #287850] Thu, 13 December 2007 16:29 Go to previous messageGo to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
1)Oh yes, that is kind of embarassing to ask

2)Yes, I am absolutely certain of it. Look up the bug # on metalink.com . In my case it happens every time I have more than 3 nested queries outer joined together. My current work-around is to use global temp tables but I like to avoid doing that if I can. I had never heard of the with clause before, it looks like it might help quite a bit, thank you very much!
Re: how to select a non-group by value from a group by clause [message #287856 is a reply to message #287854] Thu, 13 December 2007 16:57 Go to previous messageGo to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
The WITH works great, and I was able to refactor my query to be far more readable!

One quick followup regarding issue 1, is there a simple way to ensure if there are identical rows that only one of them is selected?
Re: how to select a non-group by value from a group by clause [message #287858 is a reply to message #287833] Thu, 13 December 2007 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
You do not post your code, but expect us to answer questions about it.
WHY?

You're On Your Own (YOYO)!
Re: how to select a non-group by value from a group by clause [message #287862 is a reply to message #287858] Thu, 13 December 2007 18:51 Go to previous messageGo to next message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
Well that is just rude!
If you actually read the post you would know that
a) There is no code to post
b) I made perfectly clear what I was asking by describing it 2 different ways and giving examples
c) That the question was already answered by Michel and all that is left is another simple question that doesn't require code.

Common forum courtesy my friend, if you're going to accuse someone of being an idiot make sure that they actually are being one.
Re: how to select a non-group by value from a group by clause [message #287884 is a reply to message #287856] Thu, 13 December 2007 23:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
togakangaroo wrote on Thu, 13 December 2007 14:57



One quick followup regarding issue 1, is there a simple way to ensure if there are identical rows that only one of them is selected?


If you don't care which row, then just add another column or pseudo-column to your order by clause that will make it unique. ROWID is unique, so you could use "order by timestamp desc, ROWID".
Re: how to select a non-group by value from a group by clause [message #288074 is a reply to message #287884] Fri, 14 December 2007 12:31 Go to previous message
togakangaroo
Messages: 18
Registered: November 2007
Junior Member
Excellent idea barbara, thanks a lot! Works perfectly.
Previous Topic: error
Next Topic: Sequencing help
Goto Forum:
  


Current Time: Fri Dec 09 11:35:27 CST 2016

Total time taken to generate the page: 0.04959 seconds