New answers tagged

4

You just have your nesting a little wrong (and introduced an unnecessary EXISTS clause): SELECT * FROM ( SELECT node.category_id, ... ) AS nested WHERE depth = 2; You could also try (I think): SELECT node.category_id, (COUNT(parent.name) - 1) AS depth ... group by node.category_id, node.lft HAVING (COUNT(parent.name)-1) = 2; But please ...


0

PostgreSQL's DISTINCT ON is very elegant and performs very well (often better than aggregates): select DISTINCT ON (foo.id, foo.baz) foo.id, foo.baz, bar.boom as min_boom from foo join bar on foo.id = bar.foo_id ORDER BY foo.id, foo.baz, bar.boom; Or select foo.id, foo.baz, x.min_boom from foo join (select DISTINCT ON (foo_id) ...


4

If id is defined as the primary key, you can omit grouping by all the foo columns you want for the output as long as you are grouping by the id. This special case of grouping is in accordance with the current SQL standard and has also been covered in the PostgreSQL manual, starting from version 9.1: When GROUP BY is present, or any aggregate functions ...


0

Thanks all, I suppose there's multiple ways to skin the cat on this one. @spthorn's didn't work on the production data set since it was too big. I found that similar to ypercube's suggestion, the LEFT JOIN works much more efficiently. My final query was slightly different than suggested but will add it here as a third (or fourth) option: SELECT m.id, ...


2

@spthorn's answer is correct but IN gives sometimes unexpected results when the columns involved are nullable. If you want to avoid such surprises, it's better to use NOT EXISTS or the LEFT JOIN / IS NULL structure you already have. The change you need is minimal. Just add the s.status_message = 'Delivered' condition in the joining ON clause: SELECT ...


1

Something like this: SELECT id FROM main WHERE id NOT IN ( SELECT main_id FROM status_history WHERE status_message IN ('Delivered') ) will do the trick.


1

EXISTS don't actually return any rows. They check for the existence and then move on. Your problem is probably a duplicate caused by the JOINs to the second two tables. Try running this: SELECT p.FirstName, p.LastName, e.JobTitle, COUNT(1) AS Cnt FROM Person.Person AS p JOIN HumanResources.Employee AS e ON e.BusinessEntityID = p.BusinessEntityID ...


1

When doing your last test trying to pop out the errant row with an except, you need to do a select of all columns (*) not just the few you want. This will almost certainly reveal something. Based on the table name it will likely be that an employee was in one department and then moved to another department, and so with the joins query you were accidentally ...


0

You have WHERE content.cinemaId = '2' but content is LEFT-JOINED to the profile so it may contain NULLs when no rows exist matching profile.content_ptr_id = content.id and NULL <> '2'. So you get "first" 10 profiles (without an ORDER BY inside the subquery you may get "random" 10 in reality) and none of these has any matching content so WHERE is not ...


3

I think the inner query is right. The MAX without a matching GROUP BY in the outer query will give a single value from the whole dataset. In your example the dataset would be the inner query. This is not what you want, so you have to add group by state. Now you have the problem of vendor name. It can't be in the select list unless it is also in the GROUP ...


2

As suggested by Cody Konior, you need to find rank of each vendor in a state partition by vendor state and then find all records in which ranks were 1. I will try to connect his solution to your attempt: SELECT t2.VendorState [State], t2.VendorName VendorWithMostSales FROM ( SELECT VendorState, VendorName, NumInvoicesState, ROW_NUMBER() OVER (PARTITION BY ...


2

WITH Counts AS ( SELECT VendorState, VendorName, Count(InvoiceID) AS NumInvoicesState FROM Vendors v JOIN Invoice i ON v.VendorID = i.VendorID GROUP BY VendorState, VendorName ), Ranks As ( SELECT VendorState, VendorName, NumInvoicesState, ROW_NUMBER() ...


4

You want to filter out groups of rows rather than individual rows. That is, you want to keep only the groups that have ue.user_id = 1. Therefore use HAVING, rather than WHERE or ON, to add that condition, because HAVING is used for group filtering: SELECT SUM(owe) FROM ( SELECT (expenses.amount/count(*)) AS owe FROM expenses ...


7

You can do it like this: select * from dbo.Invoice I1 join dbo.Vendors V1 on V1.VendorID = I1.VendorID where I1.InvoiceTotal > ( SELECT Avg(I2.InvoiceTotal) from dbo.Invoices I2 join dbo.Vendors V2 on V2.VendorID = I2.VendorID where V1.VendorState = V2.VendorState ...


0

LOB Rules and Restrictions ... You cannot specify LOB columns in the ORDER BY clause of a query, the GROUP BY clause of a query, or an aggregate function. You cannot specify a LOB column in a SELECT... DISTINCT or SELECT... UNIQUE statement or in a join. However, you can specify a LOB attribute of an object type column in a SELECT... ...


2

The AND (a,b) IN (SELECT x,y FROM ... WHERE) is standard SQL but not yet implemented in SQL Server. You can replace it with a correlated EXISTS subquery: AND EXISTS (SELECT * FROM ... WHERE ... AND x=a AND y=b) Your EXISTS subquery is not correct because it is not correlated. In your specific query, it should be written: AND EXISTS (SELECT * ...


1

You need to break apart your AND (FP.Age, FP.PersonType) INpart. The following code parses correctly and should provide the data you want. AND ( FP.Age IN ( SELECT FP.Age FROM FactPerson FP WHERE (FP.PersonType = 'A') AND (FP.Age BETWEEN 18 AND 21) ) AND FP.person IN ( SELECT ...


0

If you're selecting from tables that include any single-value key (indexed) that is unique for the result (e.g. table1.id), perhaps the following will perform better for your data: Where table1.id IN ( Select table1.id From table1 Join t1 ...etc... Where ...etc... AND fld1 IN ('X', 'Y', 'Z') Group by table1.id Having ...


5

Hard to say for sure without seeing the full schema and queries, to work on the execution plans. But you could try: Where (Select Count(Distinct fld1) From t1 ...etc... where ...etc... and fld1 In ('X', 'Y', 'Z')) = 3



Top 50 recent answers are included