New answers tagged query
4
You can completely avoid dynamic SQL (you also should never name PL/SQL variables the same as table columns):
create or replace procedure test_bind_variable(res out sys_refcursor,
p_item_number varchar2,
p_item_name varchar2,
...
2
You can't dynamically send bind variables to the dynamic query. Instead, you'll have to code in the bind variables in the same order no matter what, you can use a trick so that they're ignored though.
create or replace procedure test_bind_variable(res out sys_refcursor,
item_number varchar2,
...
-1
You shouldn't be able to call that procedure without supplying all the parameters as you have no defaults.
1
If I understand your requirement correctly, this is the kind of problem where window aggregation shines. Use the window version of the COUNT(*) function on the filtered dataset to obtain the counts alongside the other columns. Then filter on the count results to get only the rows you want. Your output can include any or all of the columns your table has:
...
1
The first approach is to create an index by sessionId. But its datatype is MEDIUMTEXT...
So the approach is: normalize your data, move sessionId values into separate table and refer to it by foreign key. The reference column will be compact (4 or 8 bytes) and indexed, so its usage in the query as GROUP BY expression will improve.
The columns clientID and ...
2
A possibility from Akinas cte is
CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
INSERT INTO t1 (ID,ParentID) VALUES (1,NULL),(2,1),(3,2),(4,3),(5,NULL),(6,5),(7,6);
SELECT * FROM t1;
✓
✓
ID | ParentID
-: | -------:
1 | null
2 | 1
3 | ...
3
WITH RECURSIVE
cte as ( SELECT id, id nextid, parentid
FROM t1
UNION ALL
SELECT cte.id, t1.id, t1.parentid
FROM t1
JOIN cte ON cte.parentid = t1.id )
SELECT Id, nextid RootId
FROM cte
WHERE parentid IS NULL
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=209df940143d3e984d418e49929bd847
0
On DUPLICATE KEY UPDATE you can use the VALUES(columnname) syntax to access the value of the column to be inserted:
INSERT INTO t2 (ID, P1, P2, Items)
SELECT ID, P1, P2, SUM(Items)
FROM t1
GROUP BY ID,P1,P2
ON DUPLICATE KEY UPDATE t2.Items = VALUES(Items) ;
0
Like all functions you need to make a further select with the data
And please next time provide a minimal reproducible example, so that the answer can be doe quicker and with much less effort
CREATE TABLe t2 (ID INT primary key,P1 INT,P2 INT ,Items INT )
INSERT INTO t2 VALUES (1,1,1,1)
CREATE TABLe t1 (ID INT primary key,P1 INT,P2 INT ,Items INT )
...
1
As others have mentioned, your goal is to denormalize the data and is not a usual use case for a relational database system, but it is certainly achievable. So if you really want to accomplish that, what you'd want to Google is something along the lines of "PostgreSQL concatenate multiple rows". The goal usually is to create a comma separated ...
1
I want all 200 rows between 400 and 600 regardless of if the value in the second table column exists
So given two joined tables, "left" and "right", you want to include every row from the "left" table and matching rows in the "right" table if they happen to be there?
That's called a Left [Outer] Join.
You'll have to ...
1
...well I'm dumb. I didnt realize I had the year wrong.
I was looking for data from 2021
2
Are there any transactions on that particular day?
Check the data "by eye" and see if it's the query that's "missing" them or if they really aren't there at all!
Your query looks OK to me.
Your table I'm less convinced about.
Having a table called "table" is a Bad Idea.
Using any reserved word as an identifier (table, column, ...
0
To answer your initial question:
You should first start with the object you want to get data about, in this case Employees:
SELECT LastName
FROM Employees -- Gets the LastName of every employee
Now we need a way to pull in Orders information, since you want "the employee with the largest order quantity". The way we associate an Order record with ...
4
The word you're looking for is "SARGable". From Wikipedia:
In relational databases, a condition (or predicate) in a query is said
to be sargable if the DBMS engine can take advantage of an index to
speed up the execution of the query. The term is derived from a
contraction of Search ARGument ABLE.
1
As I'm not sure whether you're looking for the person who sold the most of a thing on an individual order, or the person who sold the most of a thing over a period of time, here is a rough query that will allow you to see both:
SELECT e.LastName, tmp.MaxQuantity, tmp.SumQuantity
FROM Employees e INNER JOIN (SELECT o.EmployeeID, MAX(od.Quantity) as ...
2
Metrics
It is possible to find the number of rows looked at in the tables for a single query. These count rows of index or data, not counted separately, for your connection. If no other queries are running, total counts are possible.
SHOW SESSION STATUS LIKE 'Handler%';
Here is a discussion of using that to help with optimization: http://mysql.rjweb.org/...
3
SELECT t2.*
FROM servers t1
LEFT JOIN servers t2 ON t1.id IN (t2.id, t2.parent_id)
WHERE t1.name = 'kvm01';
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4f41816149af8751cf109a4e1c0a249e
3
You can either adjust your join condition
SELECT child.id
FROM servers AS parent
LEFT JOIN servers AS child
ON (child.parent_id = parent.id
OR child.id = parent.id)
WHERE parent.name = 'kvm01';
or use UNION / UNION ALL to retrieve the parent record separately:
SELECT child.id
FROM servers AS parent
LEFT JOIN servers AS child
ON ...
1
If temptable has one record, then you probably dont need subqueries at all. It it has more the one, then you need aggregates. Probably the best is not to provide more info, but study some examples like here: https://www.w3resource.com/PostgreSQL/postgresql-xml-functions.php and build your xml from inside moving outward step by step like
1:
SELECT xmlforest('...
1
Mike D. has provided an answer on how SQL Server sorts data that might help you understand what is it about the deterministic result you see on some comments.
The Arguments of the ORDER BY doc says:
ASC | DESC Specifies that the values in the specified column should be
sorted in ascending or descending order. ASC sorts from the lowest
value to highest value....
1
There is something you need to keep in mind for windowing functions: they are performed on every row. Thus windowing functions like lag and lead apply to the previous and next rows. By adding an order by, you are basically saying “in this grouping, up to and including the current row, what is the max value for column item_name”.
This doesn’t make sense ...
1
But the data in your order by clause doesn't provide any ordering
verification_code is null
and all values for
created_date are the same
you have to give the engine a little bit to work with
3
The windowing clause you use (in this case the default of "range between unbounded preceding and current row") operates on what you order by.
There is some logic to that: to be able to know what is "preceding" or "following" you have to talk about ordered data.
In query-1 you order by item_index, which is also what you partition ...
3
max(t.Item_name)over(partition by t.item_index order by item_index) new_column
Let's take a group where t.item_index = 0. It is
Item_index
Item_name
0
A
0
C
0
E
When order by item_index is applied then all rows have the same value, hence all of them are included into the frame, and all rows values are used for MAX() selection. So the value 'E' is ...
5
The explanation for the different results is given in SQL Server's documentation about window functions, the ORDER BY section:
ORDER BY
Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.
If it is specified, and a ROWS/RANGE is not ...
2
if only date:
select convert(date, substring('202104171830',1,8))
1
First use CAST() to convert it to a string representing that same value
Then construct a new string my using SUBSTRING() to pull out the parts and add punctuation as needed, i.e. SUBSTRING(@TimestampIntAsString, 1, 4) to pull out the year, so you end up with '2021-04-17 18:30'
Then the CAST() function will recognise that as an ISO8601-ish datetime that it ...
8
select id from table where field in (gigantic list of ordered longs)
Queries with long IN lists are slow, as they are parsed and compiled each call.
Either pass the list as a JSON array:
select id from table where field in (select value from openjson(@values))
Or use a Table-Valued Parameter or bulk load a temp table with the values. Ensure there is an ...
answered Apr 16 at 19:33
David Browne - Microsoft
32.8k3 gold badges33 silver badges73 bronze badges
0
The way a query like that is effectively evaluated is,
WHERE field = x1 or field = x2 or field = x3.....
which as your seeing can be very intensive. (Sometimes, the optimizer can use an index if available and will rewrite the query.)
What you can do to rewrite the query is to dump the fields you want into a temp table and then join between them. If you have ...
1
A "query" one or more "plans", linked by sys.query_store_plan. Each "plan" has multiple rows in sys.query_store_runtime_stats. If you turn on Profiler while running the built-in Query Store reports you can see lots of examples. EG
exec sp_executesql N'SELECT TOP (@results_row_count)
p.query_id query_id,
q.object_id ...
answered Apr 16 at 16:07
David Browne - Microsoft
32.8k3 gold badges33 silver badges73 bronze badges
Top 50 recent answers are included
