joel garry wrote:
> 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?
Sounds like an excellent opportunity for me to plug Oracle's wonderful
FLASHBACK DATABASE capabilities. <g>
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Jul 27 2007 - 01:54:53 CDT
