sql - w3toppers.com

sql query to modify the selected value from database [closed]

update table1 set numberfield = numberfield – 7 if numberfield is 30, then numberfield = 30 – 7, difference will be the new value of the field

Why do I get an error from this sub query script?

This is what your actual query is SELECT effectiveDate, CASE WHEN note=”REGULAR LOGGED” THEN log END FROM schedules LEFT JOIN timesheet ON schedules.effectiveDate = timesheet.date WHERE schedules.empid=’40’ AND YEAR(effectiveDate) = YEAR(CURDATE()) AND MONTH(effectiveDate) =’1′ GROUP BY effectiveDate. Not sure why you’re using a subquery

Select fields from two tables with Speedment

Speedment (as of the latest release 3.0.13) does not support JOINs. However, you can create a VIEW in the database that JOINs the two tables together and then generate code from that using Speedment.

Counts on Changed data

Try this: SELECT CONCAT(‘Change from ‘, OrgMatch, ‘ to ‘, ReMatchConfidence) AS Data, Count(IF(Date=”2017″, 1, NULL)) as ‘2017’, Count(IF(Date=”2016″, 1, NULL)) as ‘2016’ FROM tables GROUP BY OrgMatch, ReMatchConfidence;

Is it possible to write single query on two table which are not connected to each other?

A Cartesian join (note there is no JOIN condition). All possible combinations of records are in the results: tableA (charfield Char(2)) tableB (numberfield Number(1)) INSERT ‘A’ INTO tableA; INSERT ‘B’ INTO tableA; INSERT 1 INTO tableB; INSERT 2 INTO tableB; SELECT * FROM tablea CROSS JOIN tableb Results: charfield|numberfield ===================== A |1 A |2 B … Read more

SQL Query To Create View [closed]

CREATE VIEW EmpList AS SELECT a.id_user, b.category_province_content, c.category_city_content, d.category_job_content FROM user_detail a INNER JOIN category_province b ON a.detail_province = b.id_category_province INNER JOIN category_city c ON a.detail_city = c.id_category_city INNER JOIN category_job d ON a.detail_job = d.id_category_job To further gain more knowledge about joins and MySQL views, kindly visit the links below: Visual Representation of SQL … Read more

Whats wrong in this sql query? It throws error [closed]

in requires parenthesis: in (…)

How to get a particular preceding value of a column in SQL [closed]

You can use a running, conditional MAX to get the latest datetime value when value = 2. You can exclude the current row by using ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING SELECT t.id, t.value, t.datetime, datetime2 = MAX(CASE WHEN t.value = 2 THEN t.datetime END) OVER (PARTITION BY t.id ORDER BY t.datetime ROWS BETWEEN … Read more

get first occurrence of last changed value of a column [closed]

Maybe you can try with something like this: select t.value into @lastValue from test t order by t.date desc limit 1; select t.* from test t where t.value = @lastValue and t.date > (select max(tt.date) from test tt where tt.value <> @lastValue) order by t.date asc limit 1; You can test on this db<>fiddle

Convert text to system date format [duplicate]

EDIT From SQL Server 2012 and later you might use the FORMAT function if you want to force a date into a formatted text: https://msdn.microsoft.com/en-us/library/hh213505.aspx With earlier versions you might use something like this: DECLARE @d DATETIME=GETDATE(); DECLARE @TargetFormat VARCHAR(100)=’DD/MM/YYYY’; SELECT CONVERT(VARCHAR(100),@d, CASE @TargetFormat WHEN ‘MM/DD/YYYY’ THEN 101 WHEN ‘DD/MM/YYYY’ THEN 103 –add all formats … Read more