[SQL] Re: need oracle ROWNUM equivalent
>> I need Oracle ROWNUM equivalent. For doing following operation --- - VIEW Y.XL VIEW Y.XRVIEW Y_XL_XR 1 + 5 = 15 6 10 610 12 20 12 20 With Oracle I can use ROWNUM as key to join the two views; Is there any other ways to do that? << Never use a row number. It is not part of a logical model, it is not relational, it is not portable. Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema. Here is what I am assuming: I have two tables. Rows in Table A have a column x which needs to be set to the primary key of a row from Table B. The first row of table A should point to the first row of table B, the second row of Table A should point to the second row of table B, etc. There are many more rows in Table A than in Table B and I would like to match column x to Table B in a round-robin fashion. CREATE TABLE A (keycol INTEGER NOT NULL PRIMARY KEY, x INTEGER) INSERT INTO A VALUES (1, NULL); INSERT INTO A VALUES (2, NULL); INSERT INTO A VALUES (3, NULL); INSERT INTO A VALUES (7, NULL); INSERT INTO A VALUES (10, NULL); CREATE TABLE B (keycol INTEGER NOT NULL PRIMARY KEY); INSERT INTO B VALUES (10); INSERT INTO B VALUES (24); INSERT INTO B VALUES (32); Warning: while this is portable, it is not going to be fast. UPDATE A SET x = (SELECT keycol FROM B WHERE ((SELECT COUNT(A1.keycol) FROM A AS A1 WHERE A1.keycol < A.keycol) % (SELECT COUNT(*) FROM B)) +1 = (SELECT COUNT(B1.keycol) FROM B AS B1 WHERE B1.keycol <= B.keycol)); SELECT * FROM A; (5 row(s) affected) keycol x --- --- 1 10 2 24 3 32 7 10 10 24 Basically, I use the keys to determining the size of the subset of key columns within a boundary established by the current row in each table via those correlated subquery expressions. The rest is MOD arithmetic; add one to get rid of the 0 in the modulus cycle, use < and <= in the comparisons to adjust the subset counts. --CELKO-- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] group by weirdness
Would this give you what you want? SELECT j.id, j.created, COUNT(mj.mid), SUM(CASE WHEN ml.state <> 11 THEN 1 ELSE 0 END) AS tally_1, SUM (CASE WHEN ml.state IN(2,5) THEN 1 ELSE 0 END)AS tally_2 FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Recursive select
The usual example of a tree structure in SQL books is called an adjacency list model and it looks like this: CREATE TABLE Personnel (emp CHAR(10) NOT NULL PRIMARY KEY, boss CHAR(10) DEFAULT NULL REFERENCES Personnel(emp), salary DECIMAL(6,2) NOT NULL DEFAULT 100.00); Personnel emp boss salary === 'Albert' 'NULL'1000.00 'Bert''Albert' 900.00 'Chuck' 'Albert' 900.00 'Donna' 'Chuck'800.00 'Eddie' 'Chuck'700.00 'Fred''Chuck'600.00 Another way of representing trees is to show them as nested sets. Since SQL is a set oriented language, this is a better model than the usual adjacency list approach you see in most text books. Let us define a simple Personnel table like this, ignoring the left (lft) and right (rgt) columns for now. This problem is always given with a column for the employee and one for his boss in the textbooks. This table without the lft and rgt columns is called the adjacency list model, after the graph theory technique of the same name; the pairs of nodes are adjacent to each other. CREATE TABLE Personnel (emp CHAR(10) NOT NULL PRIMARY KEY, lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), CONSTRAINT order_okay CHECK (lft < rgt) ); Personnel emp lft rgt == 'Albert' 1 12 'Bert'23 'Chuck' 4 11 'Donna' 56 'Eddie' 78 'Fred'9 10 The organizational chart would look like this as a directed graph: Albert (1,12) /\ /\ Bert (2,3)Chuck (4,11) /| \ / | \ /| \ / | \ Donna (5,6) Eddie (7,8) Fred (9,10) The first table is denormalized in several ways. We are modeling both the personnel and the organizational chart in one table. But for the sake of saving space, pretend that the names are job titles and that we have another table which describes the personnel that hold those positions. Another problem with the adjacency list model is that the boss and employee columns are the same kind of thing (i.e. names of personnel), and therefore should be shown in only one column in a normalized table. To prove that this is not normalized, assume that "Chuck" changes his name to "Charles"; you have to change his name in both columns and several places. The defining characteristic of a normalized table is that you have one fact, one place, one time. The final problem is that the adjacency list model does not model subordination. Authority flows downhill in a hierarchy, but If I fire Chuck, I disconnect all of his subordinates from Albert. There are situations (i.e. water pipes) where this is true, but that is not the expected situation in this case. To show a tree as nested sets, replace the nodes with ovals, then nest subordinate ovals inside each other. The root will be the largest oval and will contain every other node. The leaf nodes will be the innermost ovals with nothing else inside them and the nesting will show the hierarchical relationship. The rgt and lft columns (I cannot use the reserved words LEFT and RIGHT in SQL) are what shows the nesting. If that mental model does not work, then imagine a little worm crawling anti-clockwise along the tree. Every time he gets to the left or right side of a node, he numbers it. The worm stops when he gets all the way around the tree and back to the top. This is a natural way to model a parts explosion, since a final assembly is made of physically nested assemblies that final break down into separate parts. At this point, the boss column is both redundant and denormalized, so it can be dropped. Also, note that the tree structure can be kept in one table and all the information about a node can be put in a second table and they can be joined on employee number for queries. To convert the graph into a nested sets model think of a little worm crawling along the tree. The worm starts at the top, the root, makes a complete trip around the tree. When he comes to a node, he puts a number in the cell on the side that he is visiting and increments his counter. Each node will get two numbers, one of the right side and one for the left. Computer Science majors will recognize this as a modified preorder tree traversal algorithm. Finally, drop the unneeded Personnel.boss column which used to represent the edges of a graph. This has some predictable results that we can use for building queries. The root is always (left = 1, right = 2 * (SELECT COUNT(*) FROM TreeTable)); leaf nodes always have (left + 1 = right); subtrees are defined by the BETWEEN predicate; etc. Here are two common queries which can be used to build others: 1. An employee and all their Supervisors, no matter how deep the tree. SELECT P2.* FROM Personnel AS P1
Re: [SQL] Simple Query HELP!!!
Please write DDL and not narrative. here is my guess at what you are trying to do. What you posted was not a table because you had no key. TEXT is not the datatype to use for names -- unless they are thousand of characters long!! Recording age as an integer is useless -- give us the birthday and we can always compute their age. Is this what you meant to post? CREATE TABLE People (name CHAR(30) NOT NULL PRIMARY KEY, -- not big enough for TEXT age INTEGER NOT NULL, -- should be birthdate instead company CHAR(30) NOT NULL); >> ... create a query than get me a list with the seniors per company, for example :<< SELECT P1.name, P1.age, P1.company FROM People AS P1 WHERE NOT EXISTS (SELECT * FROM People AS P2 WHERE P1.company = P2.company AND P1.age < P2.age); This says there is nobody older than the P1 person in the same company. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] double linked list
>> I've got a table called 'link_t' containing a collection of seller - buyer relations between two parties. << That is not a real linked list, but let's ignore bad terminology. One way to do this is with cursors, but they will take time and trend to be proprietary. Anohter way is to build a tree, with the first seller as the root and the final buyer as a leaf node. The usual example of a tree structure in SQL books is called an adjacency list model and it looks like this: CREATE TABLE OrgChart (emp CHAR(10) NOT NULL PRIMARY KEY, boss CHAR(10) DEFAULT NULL REFERENCES OrgChart(emp), salary DECIMAL(6,2) NOT NULL DEFAULT 100.00); OrgChart emp boss salary === 'Albert' 'NULL'1000.00 'Bert''Albert' 900.00 'Chuck' 'Albert' 900.00 'Donna' 'Chuck'800.00 'Eddie' 'Chuck'700.00 'Fred''Chuck'600.00 Another way of representing trees is to show them as nested sets. Since SQL is a set oriented language, this is a better model than the usual adjacency list approach you see in most text books. Let us define a simple OrgChart table like this, ignoring the left (lft) and right (rgt) columns for now. This problem is always given with a column for the employee and one for his boss in the textbooks. This table without the lft and rgt columns is called the adjacency list model, after the graph theory technique of the same name; the pairs of emps are adjacent to each other. CREATE TABLE OrgChart (emp CHAR(10) NOT NULL PRIMARY KEY, lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), CONSTRAINT order_okay CHECK (lft < rgt) ); OrgChart emp lft rgt == 'Albert' 1 12 'Bert'23 'Chuck' 4 11 'Donna' 56 'Eddie' 78 'Fred'9 10 The organizational chart would look like this as a directed graph: Albert (1,12) /\ /\ Bert (2,3)Chuck (4,11) /| \ / | \ /| \ / | \ Donna (5,6) Eddie (7,8) Fred (9,10) The first table is denormalized in several ways. We are modeling both the OrgChart and the organizational chart in one table. But for the sake of saving space, pretend that the names are job titles and that we have another table which describes the OrgChart that hold those positions. Another problem with the adjacency list model is that the boss and employee columns are the same kind of thing (i.e. names of OrgChart), and therefore should be shown in only one column in a normalized table. To prove that this is not normalized, assume that "Chuck" changes his name to "Charles"; you have to change his name in both columns and several places. The defining characteristic of a normalized table is that you have one fact, one place, one time. The final problem is that the adjacency list model does not model subordination. Authority flows downhill in a hierarchy, but If I fire Chuck, I disconnect all of his subordinates from Albert. There are situations (i.e. water pipes) where this is true, but that is not the expected situation in this case. To show a tree as nested sets, replace the emps with ovals, then nest subordinate ovals inside each other. The root will be the largest oval and will contain every other emp. The leaf emps will be the innermost ovals with nothing else inside them and the nesting will show the hierarchical relationship. The rgt and lft columns (I cannot use the reserved words LEFT and RIGHT in SQL) are what shows the nesting. If that mental model does not work, then imagine a little worm crawling anti-clockwise along the tree. Every time he gets to the left or right side of a emp, he numbers it. The worm stops when he gets all the way around the tree and back to the top. This is a natural way to model a parts explosion, since a final assembly is made of physically nested assemblies that final break down into separate parts. At this point, the boss column is both redundant and denormalized, so it can be dropped. Also, note that the tree structure can be kept in one table and all the information about a emp can be put in a second table and they can be joined on employee number for queries. To convert the graph into a nested sets model think of a little worm crawling along the tree. The worm starts at the top, the root, makes a complete trip around the tree. When he comes to a emp, he puts a number in the cell on the side that he is visiting and increments his counter. Each emp will get two numbers, one of the right side and one for the left. Computer Science majors will recognize this as a modified preorder tree traversal algorithm. Finally, drop the unneeded OrgChart.boss column which used to represent the edges of a graph. This has some predictable results that we can use for building queries. The root is always (left = 1, righ
Re: [SQL] double linked list
>> The table at hand is more a kind of a collection of graphs where I
want to find all possible paths between a given starting point and a
given end point. <<
For the reachabiity index of a general graph, you need Warshal's
algorithm.
Let V = number of nodes in the graph
Let A[i,j] be the adjacency matrix for the undirected graph
FOR j:= 1 TO V
DO FOR i:= 1 TO V
DO IF A[i,j] = 1
THEN FOR k := 1 TO V
DO IF A[j,k]] = 1
THEN A[i,k]] := 1;
You can also do a summation to get the length of the path from i to j.
You can concatenate names of the nodes into a string that gives the
path, etc.
Her is a first attempt at some SQL; I am sure it can be done better
CREATE TABLE Graph
(i CHAR(2) NOT NULL,
j CHAR(2) NOT NULL,
flag CHAR(1) NOT NULL DEFAULT 'n'
CHECK (flag IN ('n', 'y')),
PRIMARY KEY (i,j));
INSERT INTO Graph (i, j, flag)
SELECT DISTINCT G1.i, G2.j, 'y'
FROM Graph AS G1, Graph AS G1
WHERE G1.i <> G2.j
AND EXISTS
(SELECT *
FROM Graph AS G3
WHERE G3.i = G1.j
AND G3.j = G2.i)
AND NOT EXISTS
(SELECT *
FROM Graph AS G3
WHERE (G3.i = G1.i AND G3.j = G1.j))
OR (G3.i = G2.i AND G3.j = G2.j));
You wll have to run this statement until the size of Graph does not
change -- no new rows are being added.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] double linked list
>> are you joe celko, guy who wrote those sql books? << Yes. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Suggestions on storing re-occurring calendar events
>> I'd like to figure out a way to add simple re-occurrances of events. I can think of two ways, [a] Using application logic, create a finite number of future occurrences --for example, for 10 occurrences, 10 entries into ftr_cal_events will be created. This seems like an ugly hack. << No, not really; go with [a]. This is SQL and it is designed for tables, not computations. For example, to pull out one I recently looked at, say I am setting out a payment plan. I generate a list of 100 payments made up of (client_id, payment_due_date, payment_due_amt, actual_payment) rows by some simple temporal math -- "give me $100 every 30 days!" Now I go to my Calendar table (lots of posting about why you need a Calendar table on Google). If a payment date falls on a holiday, I move it up to the next business day. I do not try to calculate Easter or Chinese News Years on the fly; I cannot calculate somethings like national emergencies and disasters. I can print the whole plan out for the guy; his future actual payments are defaulted zero, and I can easily recompute his future amounts due from his past payments. Other advantages: portable data instead of proprietary temporal computations. The rows in this payment schedule are pretty small, so it runs fast. You can immediately see conflicts by having all the enterprised events in the same place and in the same format. For example, summing up the expected payements for any given date is trivial with a full payment schedule on everyone. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] third edition of SQL FOR SMARTIES
I am getting ready to do the third edition of SQL FOR SMARTIES this month. If anyone has an SQL programming technique, trick or tip that they would like to see in the book, drop me an email. You get a virtual beer and your name in the book. Even better, if you have an improvement on something in the second edition, you get two virtual beers and a chance to humilate me in my own book! I know that this is not exactly the newsgroup for Standard SQL programming, but you guys seem to reference the book fairly often in postings. I am looking for code that is Standard SQL-92 or higher, but if you do not know Standard SQL, then use poprietary code that maps into Standard SQL (the most common example would be temporal functions) or it should be portable (i.e. The MOD() function is not part of SQL-92, but every SQL product has some version of it). ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
