Posts by bruno • 7,237 points
193 posts
-
0
votes1
answer67
viewsA: Sort by two date fields
Your solution only needs a small adjustment to generate the expected result. The solution is to change the IF. Solution 1: ORDER BY IF(PROMESSA IS NULL, 1, 0), PROMESSA ASC, VENCIMENTO ASC If…
-
1
votes1
answer54
viewsA: How to update a column in the auto increment database and taking into account a sort
Since you cannot use the ORDER BY clause in an update, you can for example perform the update indirectly via a CTE. ;WITH CTE As ( SELECT Codigo, ROW_NUMBER() OVER (ORDER BY Descricao) AS RN FROM…
-
2
votes1
answer293
viewsA: Use select top to declare a variable
Your syntax is incorrect. You can assign the value of a SELECT to a variable as follows: Using SELECT: SELECT TOP 1 @total = Alu_COD FROM Alunos ORDER BY Alu_COD DESC SELECT @total Set SET @total=…
-
1
votes1
answer203
viewsA: Find shift based on a given date time in ORACLE/sql
In Oracle this can be done by creating a virtual column. When the table is accessed the virtual columns appear to be the same as the normal columns, however their value is derived/calculated instead…
-
0
votes1
answer91
viewsA: Count how many children the father has (Self Related)
The important thing is to be able to define correctly the set of Ids (Children). Because there may be Paiid that are not in a relation Paiid - Paiid. For this you can start by making a union of the…
sql-serveranswered bruno 7,237 -
1
votes2
answers304
viewsA: Triggers INSTEAD OF Update, How to filter by date
The problem with your Rigger is how you compare the dates of the scales and it may result from the way the information is stored in the table. Also note a difference between triggers INSTEAD OF…
-
1
votes2
answers313
viewsA: Calculate the average of a field in a specific year within a period
Surely you have noticed that all your queries follow the same pattern: SELECT "ANO", AVG(salary) as media FROM salaries WHERE "ANO" BETWEEN YEAR(from_date) AND YEAR(to_date) Creating a generic form…
-
1
votes3
answers1736
viewsA: Find word in python txt file
A solution, as indicated in your question, is to use regular expressions. Here is an example, changing only its function checkWord: def checkWord(wrd, wordLine): """ Checks whether the word exists…
-
1
votes1
answer226
viewsA: SQL query where WHERE has 2 options
I don’t see a way to do it without using Join or subquery. Here is a solution using the EXISTS clause. SELECT * FROM products WHERE name = "%explosion%" -- Devolve os produtos com nome =…
-
1
votes2
answers372
viewsA: Qt - Check if table is empty in the database
After carrying out the consultation with QSqlQuery.exec(const QString &query) it is necessary to place the result in a valid record so that the values can be read. You can do this, for example…
-
1
votes1
answer5443
viewsA: Convert str(Object) to int (python)
To change the data type you need to assign the function result astype(int) back to the spine Ranking. Using the data from your example import Pandas as pd df = pd.DataFrame({'Marca' : ['A', 'B',…
-
2
votes2
answers199
viewsA: How to reduce formula . replace in Python
Editing: I noticed only after putting the answer, that I had already solved your problem. In any case, I leave here the answer. Maybe I can help someone in the future. Your problem results from the…
-
4
votes1
answer1933
viewsA: Swap String part in all dataframe columns
You can use the predefined function replace, as follows: df.replace({'A': ''}, regex=True, inplace=True) Given your dataframe, the result will be: A B C 0 1 2 3 1 4 5 6 2 7 8 9…
-
1
votes1
answer251
viewsA: How to import a . sql extension file into SQL Server 2014?
Files . sql normally contain SQL statements. These files, unlike . Bak are not imported, but "run" for example using SQL Server Management Studio (SSMS). Heed that the file may contain…
-
1
votes1
answer69
viewsA: Treat a column of the same table twice and present as a record in the same column
Here is a possible solution (there may be some syntax error, because I wrote without access to SSMS). DECLARE @DT_ATUAL VARCHAR = CONVERT(VARCHAR, GETDATE(), 104) ;WITH PESSOAS AS ( SELECT 'Aaron…
-
2
votes1
answer106
viewsA: How to join these two querys in one?
Assuming a user has only one vote per question, you can do it as follows, for example: SELECT A.reply_id, SUM(case when A.rating = '1' then 1 else 0 end) as likes, SUM(case when A.rating = '0' then…
-
0
votes1
answer220
viewsA: How to split a string with two conditions
If you want to define your own version, here is a recursive alternative using the function span sliceIt :: String -> [String] sliceIt [] = [] sliceIt (' ':xs) = sliceIt xs sliceIt (x:xs) = let…
-
2
votes2
answers308
viewsA: Sort by larger ID and group the similar
Here are two alternatives: The first consists of using a junction (with the table itself). This is a portable solution, since it only uses standard SQL language elements or functionality, and which…
-
1
votes3
answers91
views -
6
votes1
answer587
viewsA: How to simulate LEAD and LAG functions? [Mysql]
There are several simple alternatives to simulate the LEAD() and LAG() functions in Mysql. Using your Fiddle as a base CREATE TABLE Teste (`Time_Stamp` varchar(20) ,`valor` int(2)) ; INSERT INTO…
-
3
votes1
answer80
viewsA: Mysql query - How to select products that were returned before they were purchased using sql only
A solution involves connecting the table to itself, through the product code, and using data_transaction to find the error instances, i.e., situations that should not occur in reality. As is the…
-
0
votes1
answer68
viewsA: How can I change the code I made to a recursion
Recursion, as you may already know, is a way of defining functions, in which the function is invoked within its own definition. This is a widely used concept, for example, when mathematical…
-
-1
votes3
answers628
viewsA: Create a custom SQL order
Assuming that you cannot change the table by updating the value of display_order, here are some alternatives to sort a table with the following content:…
-
3
votes1
answer1119
viewsA: Concatenation of lists in Haskell eliminating repetitions
Let us begin by trying to understand why its function uniaoS returns [3, 4] and not [1, 2, 3, 4] Using your example, the input data is the lists [1,2,3] and [1,2,4] and the definition of the…
-
1
votes2
answers50
viewsA: Select only 1 record per month with an aggravating factor that prevents the use of Group By
Here is a possible solution using ROW_NUMBER() or DENSE_RANK(): ;WITH CTE AS ( SELECT loja, nome, tipo, data, ROW_NUMBER() OVER (PARTITION BY nome, loja ORDER BY data ASC, tipo) RN FROM vendas )…
-
5
votes1
answer270
viewsA: Result when converting minutes to hours
This is not only an error in the code, but also a slight confusion with the result of the function SEC_TO_TIME used to obtain the result. This confusion is partly caused by the fact that, as 1650…
-
1
votes1
answer484
viewsA: Variable without pl/sql value
The problem in this case is not related to the fact that v.ra be or not NULL, is only a syntax error. Most likely the error message you received is as follows: ORA-06550: line 3, column 1:…
-
4
votes2
answers112
viewsA: How to make a NOT IN mysql with 2 tables
Doing by parts. First we identify pairs student-notes that do not exist in the Note table. It has several forms, here is a possible alternative: SELECT PE.id, PE.nome, TM.materia FROM pessoa PE…
-
4
votes5
answers35616
viewsA: SELECT condition WHERE with multiple values in the same column
This is a (des)known problem called "Relational Division" (I’m trying to find sources in Portuguese but it’s complicated) in which the goal is to find records that meet a set of criteria. There are…
-
5
votes1
answer660
viewsA: How to sort a date that is in varchar format?
Direct conversion using CONVERT(DATETIME, _a_sua_data_, 103) is not working because of the '-' that separates date and time. I think this is not a standard SQL Server format. One possible solution…
-
2
votes2
answers458
viewsA: Sum in a Pivot Table
One solution is to calculate the total number of items that each seller sold before executing the PIVOT operation. One possible way is through the COUNT function (used as a window function). Here is…
-
1
votes1
answer375
viewsA: How to return the total amount when using OFFSET and FETCH NEXT?
Here are two alternatives. The first one using the COUNT function, as a window function. SELECT COUNT(1) OVER() AS total, T1.* FROM Usuario T1 ORDER BY Id OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY ; The…
-
0
votes1
answer20
viewsA: Output redirect
Depending on the output of the executed command you can use grep (or one of its variants, egrep or fgrep). For example: comando | grep batata | tee -a saida.txt In the above example, only lines…
-
1
votes1
answer130
viewsA: Percentage in SQL
Try, SELECT AC.DT_AGENDA AS DATA, ME.DS_MULTI_EMPRESA AS EMPRESA, AC.CD_UNIDADE_ATENDIMENTO AS CD_UNID_ATENDIMENTO, UA.DS_UNIDADE_ATENDIMENTO AS UNIDADE_ATENDIMENTO, COUNT(IAC.NM_PACIENTE) AS…
-
0
votes1
answer107
viewsA: Doubt, mistake Haskell
The type of function map is map :: (a -> b) -> [a] -> [b] i and.., map is a function receives as parameters a function (from a for b) and a type list a and returns a type list b. Knowing…
-
2
votes1
answer308
viewsA: SQL - Transform different values of a Column into multiple columns with Count in each of them
Here’s a possible solution: SELECT COUNT(CASE WHEN OS.TIPO_OS = '1009' THEN OS.TIPO_OS END) AS '1009', COUNT(CASE WHEN OS.TIPO_OS = '1008' THEN OS.TIPO_OS END) AS '1008', COUNT(CASE WHEN OS.TIPO_OS…
-
1
votes2
answers1084
viewsA: How to make a dynamic update to all database tables for a specific column
A possible solution, as stated in the question, is to use the catalog to get the names of the tables you want to update and generate a set of update instructions that will finally be executed using…
-
3
votes2
answers609
viewsA: For/Foreach Sql Server For Each Separate Column Data
I propose a solution that does not use ties (at least it does not explicitly). We start with setting an example table and loading some records. CREATE TABLE #Vendas ( VendaID INT IDENTITY(1, 1),…
-
1
votes1
answer189
viewsA: Example Query Count
You can try the following: CREATE TABLE temp ( estadoId char(02), valor int ) INSERT INTO temp VALUES ('SP', 1) INSERT INTO temp VALUES ('SP', 2) INSERT INTO temp VALUES ('SP', 3) INSERT INTO temp…
-
1
votes1
answer55
viewsA: I need to add values from a table
Since you want the total sum of the 10 products with the highest selling value, I would suggest a sub-query. Example: SELECT COALESCE(descricao, 'Somatório total de vendas'), SUM(total_venda)…
-
0
votes1
answer123
viewsA: Sql does not return column name
First, I suggested using explicit junctions rather than implicit junctions (ANSI-92). Although there are no performance differences, the "new" syntax is much more readable when there are many tables…
-
1
votes2
answers97
viewsA: How to use pseudo names with CONCAT and Mysql?
According to the standard it is not possible to use aliases in the same SELECT statement in which they are created. A solution is to use a sub-query. The standard states that the sequence of…
-
3
votes2
answers342
viewsA: Mask SQL Select return
According to the question, only a few Ids should be masked. If this is the goal, you can use the expression CASE. For example, in this way: SELECT CASE WHEN ID = 1 THEN 'Objecto' WHEN ID = 2 THEN…
-
6
votes3
answers3104
viewsA: Why does GROUP BY not work with Mysql in this case?
As the error indicates, the instruction SELECT contains columns not part of the clause GROUP BY and at the same time are not aggregated (through an aggregation function). The GROUP BY aggregation…
-
0
votes1
answer104
viewsA: Leaf Trees - Height
Using recursiveness, usually the solution goes by considering: Knotless tree (trivial case) Node tree (recursive case) The size of a tree without nodes, ie, just the leaf, is 0. How to calculate the…
-
0
votes1
answer89
viewsA: Problem numbers after the comma
You can specify the number of decimal places in the Tostring() method as follows: txtresultado.Text = Math.Round(txtsalmin.Text * 0.2, 2).ToString.("N2") txtresultado.Text =…
-
1
votes1
answer155
viewsA: Haskell - Higher Order Functions
Here are alternatives for an implementation of the two functions. myAny is a function that takes as arguments a predicate and a generic list and returns True/True if any of the elements respects the…
-
2
votes1
answer142
viewsA: Ratio 1:N check date, difference 6 month
What is missing from your implementation is filtering after getting the maximum date for each process. I could do this in two parts: Identify the maximum date for each process Apply the filter to…
-
0
votes1
answer126
viewsA: Recursive definition of the pre-closed Enumfromthento function
The function enumFromThenTo returns a sequence starting at the first parameter of the function (in most cases), followed by its successors according to a given distance (difference between the first…
-
4
votes4
answers2013
viewsA: Find the best-selling item along with a particular item
Let’s do it in pieces. Find all sales containing the salty code of chicken SELECT v.idvenda, v.dtvenda FROM venda v INNER JOIN vdaitem vi ON vi.idenda = v.idvenda INNER JOIN produto p ON p.codbarra…