Tag Info

New answers tagged

0

You want 3 different searches and the results combined. I'd use UNION: SELECT u.id AS user_id, u.name AS name, mm.value AS mail, mt.value AS tel FROM `user` AS u LEFT JOIN user_media AS umt JOIN media AS mt ON mt.id = umt.media_id AND mt.type = 'T' ON u.id = umt.user_id LEFT JOIN user_media AS umm ...


0

You can first insert the results from the stored procedure into a temporary table (see http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table), and then simply query that table with your conditions. Let's say that you put the results in a temporary table called #procData. In that case, your final query would ...


0

I'm avoiding using RAND() because it has been said it doesn't work for large tables. I wonder how large is large? As for "doesn't work", I say that is false. RAND() does not repeat after 64K, like some random number generators. As far as "how large", time the first query below -- if it is "too slow" then the table is "too large". Then skip to the ...


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. ...


0

I'm not certain I understand your requirements for this, however the following will return a row for each row in Table1. Assuming [accountID] is an integer, you'd need to: Select [lookup] = CONVERT(VARCHAR(30), accountID) + state + product From dbo.Table1 ORDER BY accountID + state + product; You should always specify the schema (I used dbo since it is ...


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 | ...


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 ...


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 ...


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 ...


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 ...


0

-- Another way with TSQL declare @n set @n = 9 -- example for nth position set @n=@n -1 select top 1 * from Employees where Salary not in (select top (@n) salary from Employees order by Salary desc) order by Salary desc


0

Declare @nth varchar(4) = '10' ; Declare @inner varchar(max) = 'Select top ' + @nth + ' * from employee order by salary desc' Declare @outer varchar(max) = 'Select top 1 * from (' + @inner +') a order by salary'; --exec (@inner) exec (@outer); This is how I approached this with TSQL. I half-thought about paramaterizing the order by as well so it ...


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 ...


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 ...


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 ...


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, ...


0

Please try the following. If you need most recent date for every song over all meeting instances: select song_id, max(meeting_date) from song_instances si join meeting_instances mi on mi.meeting_instance_id = si.meeting_instance_id group by song_id If you're interested only in particular meeting (meeting_id = 4): select song_id, max(meeting_date) ...



Top 50 recent answers are included