Re: extracting a sample of each condition

On Jul 24, 10:22 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Michel Cadot wrote:
> > "jobs" <j..._at_webdos.com> a écrit dans le message de news: 1185328395.235116.113..._at_w3g2000hsg.googlegroups.com...
> > | 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

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

--
@home.com is bogus.
Release notes?  What release notes?