New answers tagged query
1
You absolutely do not want to change the id for any row when inserting new data.
Your Primary Key (id) value should be set when each record is created, remain the same throughout the entire lifetime of that record, right up to the point where that record is finally destroyed.
Imagine if banks "renumbered" people's bank accounts every time someone [...
0
As you are using the user-defined variables to iterate and so calculating the balnace, you have to set the initial variable ti the right amount
SET @variable = 0;
SELECT
-SUM(debit) + SUM(credit) INTO @variable
FROM billing_ledger where date < '2021-01-01';
SELECT id, date, particulars, debit, credit
, @variable := @variable + (COALESCE(`credit`,0) - ...
1
I found the solution. All you need to do is add an ORDER BY with the TAG field. So the end query becomes:
SELECT last(Temperature)
FROM raw_measure
where ID =~ /4372502|4399699|4406512|4407840/
ORDER BY ID
0
An UPDATE can be done by Joining the SELECT that you have, only you need some aliases for the columns.
The logic isd simple you can join both tables with the ddate and the time which is for example always 01:00:00 as You group by data and hour
UPDATE calculation_result cr
INNER JOIN
(SELECT
MAX(ddate) ddate,
STR_TO_DATE(...
0
If your event table is huge, what takes a while is reading it. Once the data is read, computing a few extra aggregates won't hurt a lot. And with one row per second in the event table and one row per hour in the aggregate table, you'll reduce data size substantially anyway. So you should basically store everything you'll need.
For example if you need avg(a-b)...
0
If you only need to check if a table was updated on a daily basis, then you probably can use this query. You need to alter it a bit to give you the required results. Such as a CASE statement like Akina is referring to. And don't forget to change the database name in the WHERE clause or remove that completely.
SELECT T1.database_id, T1.object_id, T1....
1
Maybe you need in
CREATE TRIGGER tr_bu_check_gap
BEFORE UPDATE
ON {tablename}
FOR EACH ROW
SET NEW.Status = CASE WHEN DATEDIFF(NEW.Time, OLD.Time) > 1
THEN 'not expected'
ELSE 'expected'
END;
?
PS. This is solution for MySQL only. For SQL Server it can be similar, but syntax adaptation ...
2
Yes, using PERCENTILE_CONT(0.50) is a way of finding the median. You can check the Basic syntax example section of the PERCENTILE_CONT doc to see an example.
About the GROUP BY, the PARTITION BY clause is already creating groups of years, so you don't need to use a group by on the subquery. Also, you shouldn't use ORDER BY on that subquery, or you'll get the ...
0
I think I have found a good solution for this.
Could you tell me if there are better ways other than this one?
select a.customer_num, count(distinct b.datekey) num_of_days
from (select t.customer_num,
t.card_number,
t.card_issue_date,
t.card_expire_date
from test_table t
where t....
2
This should get you started:
declare
s date := date'2020-01-01';
e date := date'2020-01-04';
ndays number;
begin
ndays := e-s+1;
for i in 1..ndays loop
dbms_output.put_line (s);
s := s+1;
end loop;
end;
/
01-JAN-2020
02-JAN-2020
03-JAN-2020
04-JAN-2020
PL/SQL procedure successfully completed.
Just use simple date arithmetic: the ...
-2
Using just SQL and using Tom's as_many_rows trick(adjust as required):
with as_many_rows_as_you_need
as
( select level l
from dual
connect by level <= 100
)
select customer_num cust, card_number card, (a.card_issue_date + l) dat
from as_many_rows_as_you_need
, test_table a
where a.card_issue_date + l <= a.card_expire_date
and a....
1
Assuming the [Name] column is unique, and that's the field you want to sort on (since a table is unsorted by default) this query using the ROW_NUMBER() window function should do the UPDATE you're looking for:
WITH CTE_NewDept AS
(
SELECT
[Name],
Dept + CAST(ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY [Name]) AS VARCHAR(10)) AS Dept, --...
0
Sorry, unfortunately this kind of question is not fitting (mostly because it's an opinionated question) for DBA.StackExchange so it'll likely be closed. But to hopefully offer up some helpful advice anyway, you should start looking into those technologies now and try reaching yourself to get an idea of how difficult your future in that position might be.
MS ...
0
What you're looking to do is denormalize your data into a JSON object. This is usually not a standard thing to do from a relational database, but PostgreSQL has a cool function that makes it possible called row_to_json(). You should be able to distinctify your JSON object using that function accordingly on top of your query's result set.
0
You can use sub selects instead of With clauses
But it does the same as the others.
If first picks the last date for every ID and test it, if the status is "open", if that is true, The outer Select will get all data for that ID
CREATE TABLE table1
("ID" int, "date" varchar(10), "status" varchar(14))
;
INSERT ...
0
You can accomplish this with a window function like ROW_NUMBER() like so:
WITH CTE_Test_Sorted AS
(
SELECT ID, date, status, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date DESC) AS SortId -- Generates a unique ID (assuming each date is unique for each ID) for each row within an ID sorted by date descending
FROM Test
),
CTE_Test_Latest AS
(
...
0
try with this:
with
last_row as (
select
ID,
max(date) as date
from test
group by ID
),
last_row_open as (
select
ID
from test
where ID in (select ID from last_row)
and status='Open'
)
select
*
from test
where ID in (select ID from last_row_open)
0
Check this:
SELECT mt.id main_id, mt.name main_name, st.id sub_id, st.name sub_name
FROM main_table AS mt
LEFT JOIN sub_table st ON mt.id = st.main_table_id
WHERE st.middle_table_id IS NULL
-- AND st.main_table_id IS NOT NULL
UNION ALL
SELECT mt.id, mt.name, st.id, st.name
FROM main_table AS mt
LEFT JOIN middle_table ON mt.id = middle_table.main_table_id
...
0
It is not absolutely clear what you expect as la should yield te result f 1,3,4 see query below
CREATE TABLE table1
(`id` int, `col1` varchar(6), `name` varchar(6))
;
INSERT INTO table1
(`id`, `col1`, `name`)
VALUES
('1', 'a', 'la'),
('2', 'b', 'ria'),
('3', 'w', 'la'),
('4', 'q', 'la'),
('5', 'y', 'ria');
SELECT id
FROM ...
0
Build a SELECT expression that does what you want, and then use INSERT INTO ... SELECT ... to insert it into the table.
To get a SELECT that does what you want, wrap the VALUES expression in parentheses, add an alias with field names for your sub-selects, then add a WHERE for the project and user IDs being NOT NULL. The final query becomes:
insert into ...
0
Another way to look at it too is use SQL predicates to filter down the data from your database that fits the format of your consuming application and includes all the data it would potentially need.
Use MS Access to provide filters on top of those base datasets in ways your end users would potentially want to filter the data within the application to slice ...
1
No it isn't wrong, as both are the same, only the display is different, it simply doesn't matter.
Use the view you can best solve your problems.
Forms are only a gui, for your data. So that user can access , edit or delete data, without knowledge of sql or filters.
AS today a html form and web browser are much more common and many different rdms server are ...
0
Why not split up your separate goals into separate queries to handle them appropriately? It seems a little dangerous to be modifying data within a CTE that is then used to be read from, though not sure if this is common convention in PostgreSQL.
For example:
WITH CTE_UsersToBeAdded AS
(
SELECT 'a' AS username
UNION ALL
SELECT 'b' AS username
...
1
You can use the function the SQL function DATALENGTH to get the longest value and use a CTE to get the biggest one
SELECT TOP 1 *
FROM
(
SELECT options , (options ) AS DLength
FROM ForrasReportsRicsiTeszt
) AS X
ORDER BY DLength DESC
In a similar way but not very optimal you can do this
UPDATE ForrasReportsRicsiTeszt
set options = REPLICATE(options, 4)...
1
Using the MAX function you could write the following to obtain the entry with the longest varchar
SELECT
MAX(LEN(options))
FROM
ForrasReportsRicsiTeszt;
This can then be combined with an UPDATE statement to give
UPDATE ForrasReportsRicsiTeszt
SET
options = REPLICATE(options, 4)
WHERE
LEN(options) = ( SELECT
MAX(LEN(options)...
2
Starting point, mostly unchanged:
SELECT count(*), min(last_updated_date), max(last_updated_date)
FROM schema.table;
Be aware of (not) case sensitive behavior of identifiers in Postgres. If in doubt use legal, unquoted, lower-case names exclusively. See:
Are PostgreSQL column names case-sensitive?
A table can optionally be schema-qualified (schema.table)...
0
By using this inner join and the link is database_id = principal_id
select
dp.[name] as username,
dp.create_date,
dp.modify_date,
dp.[type_desc] as [type],
dp.authentication_type_desc as authentication_type,
dp.principal_id,
m.[name]
from sys.database_principals dp
INNER JOIN master.sys.databases m ON m.database_id = dp.principal_id
AND m.name like '%...
2
The answer is, it wll return all rows.
The condition
SELECt * FROM member WHERe vname <> ANY (SELECT vname FROM member)
check for every row, it the value in not equal to ANY value in the SELECT, so if only one is not equal to the value it compares to, the condition is met, so that it is true for every column as long you SELECT all rows from the table.
2
Try this:
select x.timest,
max(case when x.devloc='outside' then x.value end) as outside,
max(case when x.devloc='hvac_main_return' then x.value end) as hvac_main_return,
max(case when x.devloc='hvac_main_supply' then x.value end) as hvac_main_supply,
((max(case when x.devloc='hvac_main_return' then x.value end)) -
(...
1
By wrapping the current query as a subquery:
SELECT timest,
outside,
hvac_main_return,
hvac_main_supply,
hvac_main_supply - hvac_main_return AS hvac_main_net
FROM
(SELECT x.timest,
max(CASE WHEN x.devloc='outside' THEN x.value END) AS outside,
max(CASE WHEN x.devloc='hvac_main_return' THEN x.value END) AS ...
3
We can actually do this in one scan of the table, by using window functions. Unfortunately, we cannot use COUNT(DISTINCT... as a window aggregate, so we must hack it with DENSE_RANK and MAX
SELECT UserID, [DateTime], IpAddress
FROM (
SELECT UserID, [DateTime], IpAddress,
distinctIps = MAX(rn) OVER (PARTITION BY UserID)
FROM (
SELECT ...
0
With the structure that you have
The C´GROUP CONCAT isd there because there could be more than 1 interval_id, that lies in the minimum date
select house_id,
sum(a.price) as price,
count(a.id) as day_count
,(SELECT GROUP_CONCAT(interval_id ORDER BY interval_id)
FROM availability
WHERE `date` = '2021-02-03'
...
2
You can use the DISTINCT clause inside of a COUNT() aggregate function in your HAVING clause after applying a GROUP BY on the UserId like so:
WITH CTE_Users_MoreThan3IpAddresses AS
(
SELECT UserID
FROM UserLogins
WHERE [DateTime] >= '01/01/2021' AND [DateTime] < '02/01/2021' -- Sets the date range for logins (1 month example)
GROUP BY ...
1
You might like to try this (see the fiddle here):
CREATE TABLE cal (dc) AS
SELECT GENERATE_SERIES
(
(DATE '2020-01-01'),
(DATE '2020-12-31'),
interval '1 MONTH'
)::DATE;
SELECT * FROM cal;
Result:
dc
2020-01-01
2020-02-01
2020-03-01
2020-04-01
2020-05-01
2020-06-01
2020-07-01
2020-08-01
2020-09-01
2020-...
0
Well, I was able to get it working.
If anyone else has some similar scenario, here's what worked:
select casenum,
matcode as casetype,
date_opened,
sp_first_party(casenum) as client,
sp_name(referred_link,1) as referrer,
(select
count(sp_name(referred_link,1))
from cases
where sp_name(referred_link,1) = referrer
and date_opened>='##...
0
You can do it using window functions only as follows (see the fiddle here):
CREATE TABLE test
(
id INTEGER NOT NULL, -- appropriate FK reference here.
m_date DATE NOT NULL,
action VARCHAR (25) NOT NULL, -- appropriate FK reference here.
ticket VARCHAR (25) NOT NULL, -- appropriate FK reference here.
INDEX (m_date),
INDEX (id)
);
...
0
Disclaimer, this answer will not provide a solution, only point out some errors in your query. There are several problems with your query:
SELECT
id,
MAX(t1.date),
LAG(Move, 2, NULL) OVER (PARTITION BY id ORDER BY id ASC) AS Move1,
LAG(Move, 1, NULL) OVER (PARTITION BY id ORDER BY id ASC) AS Move2,
Move AS Move3,
action_ticket,
FROM ...
1
WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY `date` DESC) rn
FROM test )
SELECT id,
MAX(`date`) `date`,
MAX(CASE WHEN rn = 3 THEN move END) move1,
MAX(CASE WHEN rn = 2 THEN move END) move2,
MAX(CASE WHEN rn = 1 THEN move END) move3,
MAX(CASE WHEN rn = 1 THEN action_ticket END) ...
Top 50 recent answers are included
