Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: extracting a sample of each condition

Re: extracting a sample of each condition

From: DA Morgan <>
Date: Thu, 26 Jul 2007 23:54:53 -0700
Message-ID: <>

joel garry wrote:
> On Jul 24, 10:22 pm, DA Morgan <> wrote:

>> Michel Cadot wrote:
>>> "jobs" <> a écrit dans le message de news:
>>> | Hello.
>>> |
>>> | Say i have a table that looks like this:
>>> |
>>> | name date plan type1 type2 type3 amount
>>> |
>>> | it's unique by name
>>> |
>>> | I'd like the first record of every of every combination of
>>> |
>>> | plan type1 type2 type3
>>> |
>>> | Can somebody help with the SQL for that?
>>> |
>>> | Thanks for any help or information.
>>> |
>>> Have a look at row_number/rank/dense_rank functions.
>>> First_value one may also help.
>>> Regards
>>> Michel Cadot
>> And in the future, jobs, run this query before creating objects:
>> SELECT keyword
>> FROM gv$reserved_words
>> WHERE keyword IN ('NAME', 'DATE', 'PLAN');
>> 3 of out 3.
>> I'd suggest you google "Joe Celko" and "column names" as not a single
>> one of your column names is correctly named.
>> Name is name of what? person_name? place_name? species_name?
>> Date is date of what? registration_date, termination_date? date_deceased?
>> And you should apply the same logic to the rest of them too.
>> --
>> Daniel A. Morgan
>> University of Washington
>> (replace x with u to respond)
>> Puget Sound Oracle Users

> Funny you should post this, I just spent several hours trying to
> figure out a problem related to this. A vendor has a worked-for-years
> upgrade routine, and it blew up for me in the middle of last night.
> Turns out, they have this "domain" like thing, where if you name
> columns the same in different tables, there are implicit joins
> created. So all over the place they use a column name of SEQUENCE.
> Well, it seems someone decided that wasn't a good idea for one
> particular table only, and so as part of a major upgrade they rename
> it to something more appropriate, as well as not null. Then the
> upgrade routine tries to create a unique index on the new field,
> before it transfers the sequence data, barfing on all the duplicate 0
> value sequences. This is all hidden behind a bunch of obscure trace
> files, so all I see digging into the trace files is ORA-1422 on a
> table that has had no duplicates or null since forever, and doesn't
> show any with a having query or by creating a unique constraint with
> exceptions into.
> Thank you for letting me vent.
> Oh, and then later, when this specific-to-unix routine finally
> transfers the data and tries to recreate the index, it blows up with
> an MS-SQL-specific storage parameter instead of a proper tablespace.
> All hail database-independent productivity tools.
> jg
> --
> is bogus.
> Release notes? What release notes?

Sounds like an excellent opportunity for me to plug Oracle's wonderful FLASHBACK DATABASE capabilities. <g>

Daniel A. Morgan
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Fri Jul 27 2007 - 01:54:53 CDT

Original text of this message