New answers tagged

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 ...


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 ...


Top 50 recent answers are included