Tag Info

Hot answers tagged

3

This looks like a candidate for window functions. First calculate the per-id sums, then do a running sum ordered by ID to get the desired final result. with t_report_code_temp(id, t_code) as ( select id, sum(no) from table_a group by id ) SELECT id, sum(t_code) OVER (ORDER BY id ASC) FROM t_report_code_temp;


2

The table simply does not exist. How can you verify this ? Goto the operating system and run the following cd /var/lib/mysql/crs ls -l CRS_PAIR.frm If that file does not exist, the table does not exist. If that is the case, then what's all that 246G bloated space ? You need to look the following InnoDB Architecture Take a closer look at ibdata1 ( ...


2

InnoDB Architecture Please keep in mind what goes into the InnoDB Buffer Pool 16KB Data Pages for Tables that have been accessed 16KB Index Pages for Indexes that have been accessed Changes to Secondary Indexes (could take up to 50% of buffer pool in a high-write envrironment) MySQL's idea of a JOIN Believe it or not, whether you have one big table ...


2

Try this: select patients.name AS patient_name ,NULL As nurse_primary ,m.`name` As nurse_morning ,a.name As nurse_afternoon ,e.name As nurse_evening FROM patients left join (SELECT patient_id, n.`name` AS `name` FROM patient_nurse pn INNER JOIN nurses n ON pn.nurse_id = n.id where pn.shift='MORNING' AND pn.id = (SELECT MIN(id) FROM patient_nurse ...


2

It is better to use something like this: select distinct p.* from Property p inner join PropertyHasFeature fp on (fp.p_id = p.id) inner join Feature f on (fp.f_id = f.id) where f.id in (<ID of first feature>, <ID of second feature>) In this case you will not need to add the joins when the feature list is getting larger. This will select all ...


1

I assume you're talking about Relational Algebra here. A SELECT operation returns whole tuples (rows) based on some predicate. The predicate is defined in the SQL statement by the WHERE clause. The PROJECT algebra operation is where you select a subset of attributes from each tuple. The PROJECT operation is defined in the SQL statement by the columns ...


1

First consider a query that computes which rows are actually relevant from tablethree. With the assumption that with "most recently entered result" you mean "most recent enddate" the following query would gather the appropriate rows: SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid Now you can build a join to retrieve not only sid, but all of the ...


1

Every row of the result depends on the previous row. A recursive CTE comes to mind, I tried that. But one would need to refer to the worktable in an OUTER JOIN or a subquery expression which is not allowed. This does not work (building on the table layout in my fiddle): WITH RECURSIVE t0 AS (SELECT *, COALESCE(array_length(opp_log,1), 0) AS len FROM tbl) ...


1

From Craig Ringer, ypercube. Here my testing: create table table_a (id int, no int); insert into table_a (1) select a, a from generate_series(1, 1000000) a Craig Ringer's query with t_report_code_temp(id, t_code) as ( select id, sum(no) from table_a group by id ) SELECT id, sum(t_code) OVER (ORDER BY id ASC) FROM t_report_code_temp; ...


1

Improving (?) on Craig Ringer's answer. Less code but not sure if it is more readable or more confusing: SELECT id, SUM(SUM(no)) OVER (ORDER BY id ASC) AS sum_of_no FROM table_a GROUP BY id ORDER BY id ; Tested at SQL-Fiddle You are right in your comment, when a window function (or an aggregate with OVER()) has an ORDER BY, then the default window ...



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