[Info-ingres] Fw: One up unique number for an attribute - in SQL - does anyone know the trick of doing this?
Allan Biggs
Allan.Biggs at GBR.dupont.com
Mon Feb 6 17:03:42 UTC 2017
----- Forwarded by Allan Biggs/GB/CONT/DPT on 06/02/2017 17:03 -----
From: Allan Biggs/GB/CONT/DPT
To: martin.bowes at ndph.ox.ac.uk
Date: 06/02/2017 16:51
Subject: Re: [Info-ingres] One up unique number for an attribute -
in SQL - does anyone know the trick of doing this?
Martin,
In the example below is there a way of making it order by test_id
[ascending or descending] as you can see the sorting breaks down at
sort_order 4,5,6 and later as well'
I did try order by in the insert knowing that is illegal - it is :-)
I also tried heapsort instead of btree on test_id - but that gave a
similar result !!
Thanks
Allan
drop wrr2tests_w_asd;
Executing . . .
continue
* * * * * * * * * * * * * * * * * * * *
create table wrr2tests_w_asd
( page_id integer not null with default,
test_id integer not null with default,
sort_order integer generated always as identity (start with 1 increment
by 1)
);
modify wrr2tests_w_asd to btree on test_id ;
insert into wrr2tests_w_asd
( page_id ,
test_id )
select
page_id ,
test_id
from
sspgcerttests_t_asd
;
Executing . . .
(0 rows)
(32 rows)
continue
* * * * *
select * from wrr2tests_w_asd order by sort_order ;
Executing . . .
+-------------+-------------+-------------+
|page_id |test_id |sort_order |
+-------------+-------------+-------------+
| 1696| 1688| 1|
| 1696| 1687| 2|
| 1696| 1638| 3|
| 1696| 1637| 4|
| 1696| 1410| 5|
| 1696| 1405| 6|
| 1696| 1407| 7|
| 1696| 1402| 8|
| 1696| 1409| 9|
| 1696| 1404| 10|
| 1696| 1399| 11|
| 1696| 1398| 12|
| 1696| 1397| 13|
| 1696| 1396| 14|
| 1696| 1395| 15|
| 1696| 1356| 16|
| 1696| 1355| 17|
| 1696| 1354| 18|
| 1696| 1332| 19|
| 1696| 1394| 20|
| 1696| 1393| 21|
| 1696| 1392| 22|
| 1696| 1391| 23|
| 1696| 1390| 24|
| 1696| 1353| 25|
| 1696| 1352| 26|
| 1696| 1351| 27|
| 1696| 1331| 28|
| 1696| 1114| 29|
| 1696| 1112| 30|
| 1696| 1102| 31|
| 1696| 1101| 32|
+-------------+-------------+-------------+
(32 rows)
continue
* * * * *
From: Allan Biggs <Allan.Biggs at GBR.dupont.com>
To: martin.bowes at ndph.ox.ac.uk
Cc: "info-ingres at lists.planetingres.org"
<info-ingres at lists.planetingres.org>
Date: 06/02/2017 16:10
Subject: Re: [Info-ingres] One up unique number for an attribute -
in SQL - does anyone know the trick of doing this?
Sent by: info-ingres-bounces at lists.planetingres.org
Martin,
Thanks for this - I don't have 10.2 documentation on my PC and the ESD
site seems to be down or very busy this afternoon so was unable to look it
up.
The number of times I have needed to do this and struggled with internal
procedures :-)
cheers
Allan
From: Martin Bowes <martin.bowes at ndph.ox.ac.uk>
To: Allan Biggs <Allan.Biggs at GBR.dupont.com>
Cc: "info-ingres at lists.planetingres.org"
<info-ingres at lists.planetingres.org>
Date: 06/02/2017 15:47
Subject: RE: [Info-ingres] One up unique number for an attribute -
in SQL - does anyone know the trick of doing this?
Create the table using an identity column or have the column take a column
default which is the next value from a sequence.
Martin Bowes
From: Allan Biggs [mailto:Allan.Biggs at GBR.dupont.com]
Sent: 06 February 2017 15:42
To: info-ingres at lists.planetingres.org; openroad-users at googlegroups.com
Subject: [Info-ingres] One up unique number for an attribute - in SQL -
does anyone know the trick of doing this?
in command line sql I am populating a new table - one of the columns is a
sort order
each row of this field must be a 'one up' unique number starting at 1 and
ending as the number of rows in the table
I know that I could do this in OpenROAD , and in another guise this is
done in OpenROAD, but in this case it has to be done in SQL or maybe in
report writer.
It does not matter which rows contain 1 ,2 etc although one of the
columns does have a set of unique numbers with gaps which could be used to
determine the order of row 1, 2 etc.
Does anyone know of a 'trick' to do this?
In mysql it is possible to define unique one up numbers when creating a
table does modern Ingres have this feature?
thanks
Allan
This communication is for use by the intended recipient and contains
information that may be Privileged, confidential or copyrighted under
applicable law. If you are not the intended recipient, you are hereby
formally notified that any use, copying or distribution of this e-mail,
in whole or in part, is strictly prohibited. Please notify the sender by
return e-mail and delete this e-mail from your system. Unless explicitly
and conspicuously designated as "E-Contract Intended", this e-mail does
not constitute a contract offer, a contract amendment, or an acceptance
of a contract offer. This e-mail does not constitute a consent to the
use of sender's contact information for direct marketing purposes or for
transfers of data to third parties.
Francais Deutsch Italiano Espanol Portugues Japanese Chinese Korean
http://www.DuPont.com/corp/email_disclaimer.html
This communication is for use by the intended recipient and contains
information that may be Privileged, confidential or copyrighted under
applicable law. If you are not the intended recipient, you are hereby
formally notified that any use, copying or distribution of this e-mail,
in whole or in part, is strictly prohibited. Please notify the sender by
return e-mail and delete this e-mail from your system. Unless explicitly
and conspicuously designated as "E-Contract Intended", this e-mail does
not constitute a contract offer, a contract amendment, or an acceptance
of a contract offer. This e-mail does not constitute a consent to the
use of sender's contact information for direct marketing purposes or for
transfers of data to third parties.
Francais Deutsch Italiano Espanol Portugues Japanese Chinese Korean
http://www.DuPont.com/corp/email_disclaimer.html
_______________________________________________
Info-ingres mailing list
Info-ingres at lists.planetingres.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.planetingres.org_mailman_listinfo_info-2Dingres&d=DwICAg&c=zRqMG_fghhK--2M6Q5UUdA&r=NR3Ih16COg3iVzf1V6AnysHgZxJHe-pkPnBl5lbRPSY&m=3TRWI_SdqoEFSGsaGPVNrYFAj-iNOI5Hh5ejTQBhgw0&s=dtdelW38-9MDir9yps2RH3oMCDRk6AMy6P3NvlOOuVE&e=
This communication is for use by the intended recipient and contains
information that may be Privileged, confidential or copyrighted under
applicable law. If you are not the intended recipient, you are hereby
formally notified that any use, copying or distribution of this e-mail,
in whole or in part, is strictly prohibited. Please notify the sender by
return e-mail and delete this e-mail from your system. Unless explicitly
and conspicuously designated as "E-Contract Intended", this e-mail does
not constitute a contract offer, a contract amendment, or an acceptance
of a contract offer. This e-mail does not constitute a consent to the
use of sender's contact information for direct marketing purposes or for
transfers of data to third parties.
Francais Deutsch Italiano Espanol Portugues Japanese Chinese Korean
http://www.DuPont.com/corp/email_disclaimer.html
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20170206/54b076f5/attachment.html>
More information about the Info-ingres
mailing list