Apologies for the cross-post from StackExchange DBA - I wasn't sure which site was best for this question.  I have a hierarchical query in Oracle 11gR2 that returns something like this:   Parent (Level 1)  Child (Level 2)  Grandchild (Level 3)  Child (Level 2)  Grandchild (Level 3) Grandchild (Level 3)  Child (Level 2)    The query I would like to write should get all the rows matching some predicate, for the minimum level; i.e. nearest the parent. For example, if one of the child rows matches the predicate, it should return just that row, irrespective of whether any grandchild rows match. If multiple child rows match, it should return all of them, again irrespective of grandchild rows. If no child rows match, it should return any grandchild rows that match, etc. (In the real system I have a lot more than three levels, and lots more rows per level.)  I assume this is possible with analytic functions, but I'm not sure which one to use, or how to integrate it into my query. I've seen similar problems solved using min (level) keep (dense_rank last order by level), but that doesn't seem to do quite what I want.
123689
I have a hierarchical query in Oracle 11gR2 that returns something like this:   Parent (Level 1)  Child (Level 2)  Grandchild (Level 3)  Child (Level 2)  Grandchild (Level 3) Grandchild (Level 3)  Child (Level 2)    The query I would like to write should get all the rows matching some predicate, for the minimum level; i.e. nearest the parent. For example, if one of the child rows matches the predicate, it should return just that row, irrespective of whether any grandchild rows match. If multiple child rows match, it should return all of them, again irrespective of grandchild rows. If no child rows match, it should return any grandchild rows that match, etc. (In the real system I have a lot more than three levels, and lots more rows per level.)  I assume this is possible with analytic functions, but I'm not sure which one to use, or how to integrate it into my query. I've seen similar problems solved using min (level) keep (dense_rank last order by level), but that doesn't seem to do quite what I want.
32770
How would I be able to get N results for several groups in an oracle query.  For example, given the following table:  |--------+------------+------------| | emp_id | name       | occupation | |--------+------------+------------| |      1 | John Smith | Accountant | |      2 | Jane Doe   | Engineer   | |      3 | Jack Black | Funnyman   | |--------+------------+------------|   There are many more rows with more occupations.  I would like to get three employees (lets say) from each occupation.  Is there a way to do this without using a subquery?
47998
Our team is developing a new system and it has a Warehouse table that must have a hierarchical relationship between entries since a customer can have 1st, 2nd, 3rd and Nth degree warehouses.  We first thought that  was the best way to do it. By creating a Parent and a Child column on the table and then making Parent the FK to Child (on the same table) but friend DBA told us that there is a different way.  So, for instance, we had thought about making a table like this:  CREATE TABLE Warehouse( filterId INT PRIMARY KEY IDENTITY, parentId INT NOT NULL, active BOOL DEFAULT 1, warehouseName VARCHAR(30) NOT NULL, FOREIGN KEY parentId REFERENCES Warehouse(filterId)  | FILTERID   | PARENTID | ACTIVE | WAREHOUSENAME | -------------------------------------------------- |          1 |        0 |      1 | N WAREHOUSE 1 | |          2 |        1 |      1 | ROOM 1        | |          3 |        1 |      1 | ROOM 2        | |          4 |        1 |      1 | ROOM 3        | |          5 |        1 |      1 | ROOM 4        | |          6 |        2 |      1 | SHELF 1       | |          7 |        2 |      1 | SHELF 2       | |          8 |        2 |      1 | SHELF 3       | |          9 |        2 |      1 | SHELF 4       | |         10 |        2 |      1 | SHELF 5       | |         11 |        2 |      1 | SHELF 6       | |         12 |        2 |      0 | SHELF 7       | |         13 |        3 |      1 | BOX 1         | |         14 |        3 |      0 | BOX 2         | |         15 |        3 |      1 | BOX 3         | |         16 |        3 |      1 | BOX 4         | --------------------------------------------------   But this DBA said that we should work on something like this:  CREATE TABLE Warehouse( filterId VARCHAR(20) PRIMARY KEY, active BOOLEAN DEFAULT 1, warehouseName VARCHAR(30) NOT NULL)   | FILTERID   | ACTIVE | WAREHOUSENAME | --------------------------------------- | 1.00       |      1 | N WAREHOUSE 1 | | 1.01       |      1 | ROOM 1        | | 1.02       |      1 | ROOM 2        | | 1.03       |      1 | ROOM 3        | | 1.04       |      1 | ROOM 4        | | 1.01.01    |      1 | SHELF 1       | | 1.01.02    |      1 | SHELF 2       | | 1.01.03    |      1 | SHELF 3       | | 1.01.04    |      1 | SHELF 4       | | 1.01.05    |      1 | SHELF 5       | | 1.01.06    |      1 | SHELF 6       | | 1.01.07    |      0 | SHELF 7       | | 1.01.01.01 |      1 | BOX 1         | | 1.01.01.02 |      0 | BOX 2         | | 1.01.01.03 |      1 | BOX 3         | | 1.01.01.04 |      1 | BOX 4         | ---------------------------------------   Which way should we follow?   On one hand we have a relationship between parentId and filterId; on the other we do not have any relationship, but it is easy to understand where does the entry belong.  Not all the warehouses are of the same level - that's what I mean by degree. They should be ordered like: For Customer A, 1 is parent of 1.1 and 1.2, 2 is parent of 2.1, 3 is parent of 3.1 and 4 is parent of 4.1 and 4.2 - but 1, 2, 3 and 4 are of the same level (1st level), and 1.1, 1.2, 2.1, 3.1, 4.1 and 4.2 are also of the same level (2nd level) but not of the same parents.
23741
states:  "In practice, explicit right outer joins are rarely used, since they can always be replaced with left outer joins and provide no additional functionality."  Can anyone provide a situation where they have preferred to use the RIGHT notation, and why? I can't think of a reason to ever use it. To me, it wouldn't ever make things more clear.  Edit: I'm an Oracle veteran making the New Year's Resolution to wean myself from the (+) syntax. I want to do it right
14312
What are the ways that you use to model and retrieve hierarchical info in a database?
58715
Given a hierarchical table like this:  CREATE TABLE [dbo].[btree] (   id INT PRIMARY KEY , parent_id INT REFERENCES [dbo].[btree] ([id]) , name NVARCHAR(20) );   I would like to obtain the whole tree structure.  For instance, using this data:  INSERT INTO [btree] VALUES (1, null, '1 Root'); INSERT INTO [btree] VALUES (2,    1, '1.1 Group'); INSERT INTO [btree] VALUES (3,    1, '1.2 Group'); INSERT INTO [btree] VALUES (4,    2, '1.1.1 Group'); INSERT INTO [btree] VALUES (5,    2, '1.1.2 Group'); INSERT INTO [btree] VALUES (6,    3, '1.2.1 Group'); INSERT INTO [btree] VALUES (7,    3, '1.2.2 Group'); INSERT INTO [btree] VALUES (8,    4, '1.1.1.1 Items'); INSERT INTO [btree] VALUES (9,    4, '1.1.1.2 Items'); INSERT INTO [btree] VALUES (10,   5, '1.1.2.1 Items'); INSERT INTO [btree] VALUES (11,   5, '1.1.1.2 Items'); INSERT INTO [btree] VALUES (12,   6, '1.2.1.1 Items'); INSERT INTO [btree] VALUES (13,   6, '1.2.1.2 Items'); INSERT INTO [btree] VALUES (14,   7, '1.2.2.1 Items');   I would like to obtain:  +----+-----------+---------------------+ | id | parent_id | description         | +----+-----------+---------------------+ |  1 |    NULL   | 1 Root              | |  2 |     1     |   1.1 Group         | |  4 |     2     |     1.1.1 Group     | |  8 |     4     |       1.1.1.1 Items | |  9 |     4     |       1.1.1.2 Items | |  5 |     2     |     1.1.2 Group     | | 10 |     5     |       1.1.2.1 Items | | 11 |     5     |       1.1.2.2 Items | |  3 |     1     |   1.2 Group         | |  6 |     3     |     1.2.1 Group     | | 12 |     6     |       1.2.1.1 Items | | 13 |     6     |       1.2.1.2 Items | |  7 |     3     |     1.2.2 Group     | | 14 |     7     |       1.2.2.1 Items | +----+-----------+---------------------+   I'm fetching records using a recursive query like this:  ;WITH tree AS (     SELECT c1.id, c1.parent_id, c1.name, [level] = 1     FROM dbo.[btree] c1     WHERE c1.parent_id IS NULL     UNION ALL     SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1     FROM dbo.[btree] c2 INNER JOIN tree ON tree.id = c2.parent_id ) SELECT tree.level, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name AS description FROM tree OPTION (MAXRECURSION 0) ;   And this is the current result:  +----+-----------+---------------------+ | id | parent_id | description         | |  1 |    NULL   | 1 Root              | |  2 |     1     |   1.1 Group         | |  3 |     1     |   1.2 Group         | |  6 |     3     |     1.2.1 Group     | |  7 |     3     |     1.2.2 Group     | | 14 |     7     |       1.2.2.1 Items | | 12 |     6     |       1.2.1.1 Items | | 13 |     6     |       1.2.1.2 Items | |  4 |     2     |     1.1.1 Group     | |  5 |     2     |     1.1.2 Group     | | 10 |     5     |       1.1.2.1 Items | | 11 |     5     |       1.1.1.2 Items | |  8 |     4     |       1.1.1.1 Items | |  9 |     4     |       1.1.1.2 Items | +----+-----------+---------------------+   I can't figure out how to order it by levels.   Is there a way to set a rank for each sub-level?  I've set up a
80451
I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped.  The table hierarchy is as follows:   Order table contains OrderReference(PO), OrderID OrderItems table contains Product/Lot/Qty requested (OrderID is parentID that joins these tables) Shipping contains OrderReference(PO), ShippingID ShippingArchive contains Product/Lot/Qty that was shipped (ShippingID is parent that joins these tables)   I want to create a report that looks as follows:  OrderReference#  Order Requested / Shipped Items / Missing Pieces  PC/Lot/Qty    -   PC/Lot/Qty    - PC/Lot/Qty   I have used a temp table to get some of the data but I realize I'm taking the wrong approach. I should be able to do all of this with a single query, though it will seemingly be a bit complex.   I get held up when I try to think about how to join where pieces don't exist, and where to perform the mathematics in my sorting and filtering. Please help!  Sincere thanks :)
15142
note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.  I have a self-referencing table with a primary key, id and a foreign key parent_id.  +------------+--------------+------+-----+---------+----------------+ | Field      | Type         | Null | Key | Default | Extra          | +------------+--------------+------+-----+---------+----------------+ | id         | int(11)      | NO   | PRI | NULL    | auto_increment |  | parent_id  | int(11)      | YES  |     | NULL    |                |  | name       | varchar(255) | YES  |     | NULL    |                |  | notes      | text         | YES  |     | NULL    |                |  +------------+--------------+------+-----+---------+----------------+   Given a name, how can I query the top-level parent?  Given a name, how can I query all of the id's associated with a record of name = 'foo'?  context: I am not a dba, but am planning to ask a dba to implement this type of hierarchical structure and would like to test some queries. The motivation for doing so is described by .    Here is an example of the relationships among ids in the table:    -- ----------------------------------------------------- -- Create a new database called 'testdb' -- ----------------------------------------------------- SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';  CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `testdb` ;  -- ----------------------------------------------------- -- Table `testdb`.`observations` -- ----------------------------------------------------- CREATE  TABLE IF NOT EXISTS `testdb`.`observations` (   `id` INT NOT NULL ,   `parent_id` INT NULL ,   `name` VARCHAR(45) NULL ,   PRIMARY KEY (`id`) ) ENGINE = InnoDB;  SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;  -- ----------------------------------------------------- -- Add Example Data Set -- -----------------------------------------------------   INSERT INTO observations VALUES (1,3), (2,5), (3,NULL), (4,10),     (5,NULL), (6,1), (7,5), (8,10), (9,10), (10,3);
58142
I have a database table that contains a list of contacts, some of those contacts might have multiple records, e.g.  CustomerID, CustomerName, Vehicle 1, Dan, Mazda 1, Dan, Suzuki 2, John, Ford 3, Dasha, Volvo 3, Dasha, Ford   Can I write a select query to return the distinct customerID and CustomerName, and a list of vehicles in 1 record? i.e.  1, Dan, Mazda+Suzuki 2, John, Ford 3, Dasha, Volvo+Ford   Thanks
28370
I have a table similar to this:  CREATE TABLE example (   id integer primary key,   name char(200),   parentid integer,   value integer);   I can use the parentid field to arrange data into a tree structure.  Now here's the bit I can't work out. Given a parentid, is it possible to write an SQL statement to add up all the value fields under that parentid and recurse down the branch of the tree ?  UPDATE: I'm using posgreSQL so the fancy MS-SQL features are not available to me. In any case, I'd like this to be treated as a generic SQL question.  BTW, I'm very impressed to have 6 answers within 15 minutes of asking the question! Go stack overflow!
98891
I am looking for a way in LINQ to match the follow SQL Query.  Select max(uid) as uid, Serial_Number from Table Group BY Serial_Number   Really looking for some help on this one.  The above query gets the max uid of each Serial Number because of the Group By Syntax.
74540
I have a query pattern that must be very common, but I don't know how to write an efficient query for it.  I want to look up the rows of a table that correspond to "the most recent date not after" the rows of another table.  I have a table, inventory say, which represents the inventory I hold on a certain day.  date       | good | quantity ------------------------------ 2013-08-09 | egg  | 5 2013-08-09 | pear | 7 2013-08-02 | egg  | 1 2013-08-02 | pear | 2   and a table, "price" say, which holds the price of a good on a given day  date       | good | price -------------------------- 2013-08-07 | egg  | 120 2013-08-06 | pear | 200 2013-08-01 | egg  | 110 2013-07-30 | pear | 220   How can I efficiently get the "most recent" price for each row of the inventory table, i.e.  date       | pricing date | good | quantity | price ---------------------------------------------------- 2013-08-09 | 2013-08-07   | egg  | 5        | 120 2013-08-09 | 2013-08-06   | pear | 7        | 200 2013-08-02 | 2013-08-01   | egg  | 1        | 110 2013-08-02 | 2013-07-30   | pear | 2        | 220   I know one way of doing this:  select inventory.date, max(price.date) as pricing_date, good from inventory, price where inventory.date &gt;= price.date and inventory.good = price.good group by inventory.date, good   and then join this query again to inventory. For large tables even doing the first query (without joining again to inventory) is very slow.  However, the same problem is quickly solved if I simply use my programming language to issue one max(price.date) ... where price.date &lt;= date_of_interest ... order by price.date desc limit 1 query for each date_of_interest from the inventory table, so I know there is no computational impediment.  I would, however, prefer to solve the whole problem with a single SQL query, because it would allow me to do further SQL processing on the result of the query.  Is there a standard way to do this efficiently? It feels like it must come up often and that there should be a way to write a fast query for it.  I'm using Postgres, but an SQL-generic answer would be appreciated.
19642
Is there a grep-like utility that will enable me to do grep searches with logic operators. I want to be able to nest and combine the logical constructs freely. For example, stuff like this should be possible:  grep (term1 &amp;&amp; term2) || (term1 &amp;&amp; (term3 xor term4)) *   I realize this can be done with vanilla grep and additional bash scripting, but my goal here is to avoid having to do that.
102917
How can I distinguish between the levels of , say , querying ? For example, Mr. Jon Skeet has all the gold, silver and bronze badges for LINQ, so the query select * from Badges b with (nolock) where     b.Name = 'linq' and b.UserId = 22656  returns 3, with no distinction between them. Is it possible to include such information into the dump, for example, keep a unique badge ID (40 for the gold  badge)?
102621
Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the parent_id column this is a good and normalized technique i agree but there is a disadvantage also, it’s slow and inefficient. This is mainly caused by the recursion like for each parent we have to run the query again and again to make a tree  SELECT id FROM `table` WHERE parent_id=something   I have looked at the solutions some might try to do it with any programming language by running query again and again which makes a loads on server , Some have provided the stored procedure but that also involves the recursion.  So my question is can we do it with one database query for the tree(joins or subqueries) if we know the depth or if we don't know if it is possible so how can we get the top most parent(i.e parent_id=0) of any child if it is not possible then why this technique is so famous while it has the flaws or we have another solution for this? . i have added the sql fiddle but it only has the schema
45432
I've asked the same question on , but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice).  Suppose I have 3 tables: posts, post_categories and categories. I'm implementing some kind of page with filters where I can filter posts by categories. User can select multiple categories. When he selects more than one, they should be summed. I can't get it working. What I have now is simple IN() SQL clause:  SELECT posts.id, post_categories.category_id FROM posts  JOIN post_categories ON posts.id = post_categories.post_id  WHERE post_categories.category_id IN (1,2,3) LIMIT 10;   But it is not matching all ids, it is OR and I need AND. What I need here is to find all posts that have categories with id=1 AND id=2 AND id=3, but instead this query returns posts that have at least one category from the IN() list.
58214
I'm trying to create a query activity in order to show, from a Data Extension, only the last 5 articles ids. I tried with the formula ORDER BY xxx DESC LIMIT 0,5 as in SQL but it is not working and it is returning this exception:     The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified   How can I use ORDER BY in a Data Extension?