Tag Info

Hot answers tagged

17

What's happening here is the subquery is looking at each individual salary and essentially ranking them, then comparing those salaries to the outer salary (a separate query against the same table). So in this case if you said N = 4 it is saying: WHERE 3 = (number of salaries > outer salary) So looking at the data you have, let's rank them in order, and ...


6

The problem is that it might (and knowing spatial indexes, probably will) assume that the spatial filter will be a lot more selective than the time filter. But if you have a few million records within 200km, then it could be significantly worse. You're asking it to find records within 200km, which returns data ordered by some spatial order. Finding the ...


5

Another way to write this query would be using the 2012+ OFFSET / FETCH syntax to find the Nth salary: ; WITH Nth AS -- To find the Nth highest salary, ( SELECT DISTINCT Salary -- get all the distinct salary values FROM Employee ORDER BY Salary DESC -- order them from high to low OFFSET 3 ROWS ...


4

The problem is the subquery, where you combine in the select list both an aggregated (max(cr.posted_dt)) and non-aggregated expressions/columns from the tables. MySQL allows you to run this kind of inconsistent queries - with default settings - which basically means it depends on the developer to write consistent queries and not fire themselves on the ...


3

If N=4, it returns the salary where there are 4-1=3 higher salaries, in other words, it returns the 4th highest. Example: Salaries (500, 400, 400, 300, 250, 200). Desired result is (250) (the fourth as we count '400' only once due to the DISTINCT). N-1=3 means there are 3 distinct salaries greater than 250, which are (500, 400, 300). In your example, ...


3

Yes, it's possible, The query you already tried was on the right lines but had the wrong join condition (b.INVOICES_ID = a.INVOICES_ID should be b.ORDER_NO = a.ORDER_NO) try this SELECT DISTINCT a.order_no FROM x_invoic a INNER JOIN x_invoic b ON a.order_no = b.order_no WHERE a.item_code = 'DONE' AND b.item_code = 'LAPMACPRO15.4' ...


2

Having clause can only exclude rows which exist in data - in your case only possibility to get count(*)=0 means having no matching rows - so nothing to return in the first place. You probably want to count existing matches in the left join - so COUNT(m.MATCH_ID) will only count rows where match_id is not null. Edit: One more thing I noticed - you have lot ...


2

You don't need to (and shouldn't) concatenate a string just to join on multiple columns. You can use SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.accountID = t2.accountID AND t1.state = t2.state AND t1.product = t2.product; Or - if the columns are nullable and you want nulls treated equal. ...


1

Sounds like you have neither index: On calls an INDEX or PRIMARY KEY starting with first_id Ditto for planned.another_id You need one or the other to keep from doing 60k times 80k operations. With an index, it will be only 60k plus 80k operations. Please provide SHOW CREATE TABLE to confirm, and to let us check for other issues such as dissimilar ...


1

You are doing DDL along with the SELECT Doing CREATE TABLE AS SELECT mechanically does two commands CREATE TABLE INSERT INTO ... SELECT This will produce locks on both calls and planned I have written about this behavior in some of my older posts Mar 23, 2012 : MySQL Locks while CREATE TABLE AS SELECT Aug 08, 2014 : MySQL consistent nonlocking reads ...


1

@ypercube YperCube's answer did the job. Easy. Effective. A special thanks to him. Anyway, after some research I have came up with an answer of my own. This is an alternate way, Just thought of sharing it. SELECT com.id AS complaint_id, com.member_id AS parent_mem_id, crep.mem_id AS last_replier, crl.last_posted_dt FROM complaints com ...


1

This is tricky because MySQL doesn't care about the data consistency in regards with GROUP-BY clause, but it cares only about the grouping data, so after a group-by we can trust only the group-by columns, but not the data associated. As an example, consider table (id, name, sex, age): +----+-------+-----+-----+ | id | name | sex | age | ...


1

PROPOSED MySQL QUERY To get the 5th Largest Salary SET @nth = 5; SET @ndx = 0; SELECT @nth nth,EmpID,salary FROM ( SELECT (@ndx:=@ndx+1) ndx,EmpID,salary FROM employee ORDER BY salary DESC ) A WHERE ndx = @nth; SAMPLE DATA DROP DATABASE IF EXISTS thinker2305; CREATE DATABASE thinker2305; USE thinker2305 CREATE TABLE employee (EmpID int not null ...


1

I'm really not sure if this is going to work in this Database of yours, but I made tests with much simpler values and tables in my Oracle XE database and it worked just fine, I would give it a go. SELECT * FROM ENTR2012 INNER JOIN ITST2012 ON ENTR2012.ABREV=ITST2012.ABREV AND ENTR2012.NOTA=ITST2012.NOTA AND ENTR2012.VALTOTAL=ITST2012.VALTOTAL INNER JOIN ...



Only top voted, non community-wiki answers of a minimum length are eligible