New answers tagged

1

try this - you might get the best of both select manyfield from ( select manyfield , row_number() over (partition by t1.id order by t1.timestamp desc) as rownum from inserted left join sometable t1 on inserted.id = t1.id ) tt where rownum = 1 or t1.id is null


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

This might run faster due to "lazy evaluation". Note that you want to fetch some large columns, yet thousands of rows need to be looked at before deciding which 10 are desired. Instead of gathering all the columns needed, let's get just the PRIMARY KEYs, then reach back into posts only 10 times to get the bulky columns. Note that bulky columns are stored ...


1

Let's say that your tables are called (in order of you mentioning them): Segment, FK, JC_type_A, JC_type_B, etc The main problem that I can see is that you don't know which junc_type will be for which junc. So you will have to left join back to their respective tables and select the one that is not null in your select statement. Something along the ...


-1

I hope I am not reading this wrong but if you want the top 10 most recent posts of a single user wouldn't it be better to run your query on the relations table and join the posts to that query? You would restrict your search to only the posts for the signle user you are attempting to look up then sort the data. So it would look like this: SELECT p.id, ...


1

ORA-00913 too many values Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT. Action: ...


2

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise. You could resort to using a stored procedure, and accumulate results going back in time, say daily or weekly, looping until obtaining at least 10 records, and then ...


2

You can make use of the FOR XML PATH() construct to create the list of features. For instance: USE tempdb; IF OBJECT_ID('dbo.Features') IS NOT NULL DROP TABLE dbo.Features; IF OBJECT_ID('dbo.Versions') IS NOT NULL DROP TABLE dbo.Versions; GO CREATE TABLE dbo.Versions ( VersionID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1) , ...


0

Start by changing IN ( SELECT max... ) to = ( SELECT max... ). Change f.Food_name to ( SELECT Food_name FROM Food WHERE Food_therm_id = t.Therm_id ) AS Food_name and get rid of the LEFT JOIN Food ... (I am unsure whether the LEFT JOIN before JOIN is causing trouble.) If those suggestions do not speed it up enough, then... Please provide EXPLAIN SELECT ...


2

What you are looking for is a 0-many relationship. This is usually referenced by a third table. Publication_Scientists : Publication_Id (int) | Scientist_Id (int) with 1:1 relationships back to the other two tables. Preferably using something like an enforced foreign key.


1

In your first query, in the where clause, you're restricting results to those where y.TransType = 'used'. This turns the left join into an inner join because you're throwing away results where y.TransType is null (aka where the other ticket doesn't exist). An easy fix for this is to move that condition to the on clause like this: select x.* from factI as ...


10

Your first query works as an inner join because the y.TransType = 'used' condition which uses the right table is in the where clause. Your second query can be rewritten without derived tables by simply moving that condition to the on clause: select x.*, y.* from factI as x left join factI as y on x.tickedId = y.tickedId and ...


2

You can use variables: delimiter // CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime, _hashid int, _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1)) begin START TRANSACTION; SELECT @fn:=`first_name`, @ln:=`last_name`, ...


0

did you try outer join? select sales_people.person_id, last_name, first_name, sales_region.Region_id, coalesce(trim(sales_region.name),'NULL') AS 'Region Name' from sales_region right outer join sales_people_region on sales_people_region.region_id = sales_region.region_id right outer join sales_people on sales_people_region.`person_id` = ...


0

This will return all current TableA with previous Group info. Select *, (Select top 1 B From TableB Where started < b.started Order by started desc) as PriorGroup, (Select top 1 Started From TableB Where started < b.started Order by started desc) as PriorGroupStarted, From ...


3

Do the percentages and query cost even mean anything? Not really--They have meaning, but they're unreliable. are they referring to the estimated data from the query plan, and do not reflect the real cost? Otherwise how could the same join make up 39% of the "cost" for both, but one takes 2 seconds and one takes 47 seconds? Exactly--The cost is ...


1

The actual execution plans produced by a DBMS optimizer does not need to follow exactly the SQL code. It only has to produce the same results as if it followed the code. The optimizer is free to do any optimizations and is usually better at it when the code is simpler. Some optimizers (for example MYSQL before 5.6) would create a better execution plan with ...


0

Third option is preferable, however with braces like: SELECT book_name, book_type FROM books b INNER JOIN authors a ON (b.author_id = a.id) WHERE a.user_id = ? AND a.male = ?;


2

I would opt for the third option purely for readability and simplicity. Have you looked at the execution plans for all three, I expect they may give the same result for each. I would have expected the bracket query with the * would have been slower.


1

The first query you have used returns Cartesian product of the rows from these three tables. For details: Join The following is the simple example to join three tables. SQL> select * from student; STUDENT_ID FIRSTNAME LASTNAME ---------- -------------------- -------------------- 1 Will Smith SQL> select * from module; ...


2

There are several problems with the query The select list is missing. The join syntax needs ON, not WITH: a JOIN b ON <some condition> There is a missing on condition (what you correctly identified as arbitrary/random join between Contractor and ContractorJob). There are column names mismatches between the code and the design: (Job.contractor_job ...


1

My own hint with the Write Set Cache Size (aka Galera Cache Size) nailed it (I should have asked earlier, it seems some supernatural voice told me the answer.) However I do not quite understand why 15 GBytes were too few for 45 KBytes/sec for an 18 hours transfer after a next try, obviously it was only a bit too short... (In my question I wrote about 10 ...


2

As Patrick7 has already said, you need your GROUP BY to include all the non-aggregated columns that are in your SELECT list. In your case, however, there is a way to avoid duplicating all the SELECT columns in GROUP BY. You could first aggregate the PTC_DIAGNOSIS rows separately: SELECT patient_id FROM PTC_DIAGNOSIS WHERE create_date > '20151201' ...


2

very close only the GROUP BY Columns forgotten Try this: select PTC_DIAGNOSIS.PATIENT_ID, PT_BASIC.PATIENT_CODE, PT_BASIC.NAME_FIRST, PT_BASIC.NAME_LAST from PTC_DIAGNOSIS inner join PT_BASIC on PTC_DIAGNOSIS.PATIENT_ID=PT_BASIC.PATIENT_ID where PTC_DIAGNOSIS.create_date>'12/01/2015' group by PTC_DIAGNOSIS.PATIENT_ID, PT_BASIC.PATIENT_CODE, ...



Top 50 recent answers are included