Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

1
vote
0answers
36 views

Database administration in “start-up” companies [on hold]

I am an Information Systems Engineer. I'v been working with data for over 4 years, mainly in Business Intelligence and Data Science. I'v been using Tableau, R and all of Microsoft's tools (sql ...
3
votes
0answers
18 views

Managed backup large database to Azure

Got a bit of an issue here that I need some help to solve. Hosting a database, that is about 400Gigs. This is giving us a little headache getting it to work with Managed Backup to Azure Storage ...
0
votes
1answer
22 views

MySQL trigger/check on INSERT based on multiple factors (location, date and time)

I have a shift schedule planner for various warehouses. I'm trying to add multiple checks at DB level. If I'm using a composite key (by removing shift_id) to match staff, location,date time it's works ...
-5
votes
0answers
28 views

Query result needed

I need to create e view that returns me top 2 PartnerIddressId desc by Data_Tran where top 1 SyntheticName <> second SyntheticName. PLS, help!
0
votes
0answers
21 views

Find Popular/Common States

State Table CREATE TABLE state ( id serial primary key, name varchar(255) not null ); id | name -------------------- 1 | NC 2 | OH 3 | MA 4 | NY 5 | Connecticut 6 | New York ...
1
vote
0answers
14 views

What are the pros and cons of using ORMs like sqlalchemy versus writing in SQL directly?

sqlalchemy is an ORM in python. https://www.sqlalchemy.org/ What are the pros and cons of using ORMs versus writing queries directly in SQL? Suppose I have a project which involves only one kind ...
0
votes
0answers
3 views

Need help with counting the number of games which has quantity greater than 10 in php from the database and printing them

here is the code i have everything is good except i need to print the quantity of games i have which is greater > 10 in stock and printing it out just the like this picture i have here how can i add ...
1
vote
0answers
17 views

Double Entry Accounting Schema

Setting up a double entry accounting system for personal use and to help manage a really small business. Trying to put a few features that seem relevant now. I'm just starting to learn about DB/...
1
vote
1answer
12 views

Azure server names vs Azure database names

Let's say I have one Azure subscription and 2 Azure instances: Dev-instance.database.windows.net for developer testing, and Prod-instance.windows.net for production work. I want to create a new ...
-2
votes
1answer
20 views

Trying to find the max occurrences in dataset based on a group by

I have a table like this: ID group_name ============= 9 Yellow 9 Yellow 3 Yellow 1 Red 2 Red 5 Red 5 Red 99 Green 99 Green I would like the output to be the most frequent ID ...
-2
votes
1answer
44 views

How do a have a column filled by a default value? [on hold]

I would like to fill a column with a default value as soon as i finish edit other columns using SQL code in PostgreSQL 9.5/ PostGIS. The value of this column is "01LU" the column name is "...
0
votes
2answers
38 views

Insert into a table selecting data from a sub query [on hold]

I would like to insert a few data into a table selecting data from a subquery having multiple joins. I tried the following way: insert into mytable (columns) select * from (select columns from ...
0
votes
1answer
56 views

LEFT JOIN performance for WHERE on secondary table

I've been developing for years and through out the carrier I've been using only LEFT JOIN as it provides a more logical sense of mapping my one to many data. However recently I ran across a ...
-1
votes
0answers
19 views

Pull the 2 min value - mysql

I need a calculated min column using datediff & min functions but not exactly the min value but the 2nd min value or date, below is the query for your reference: Select r.rfq_Id, datediff(min(q....
0
votes
0answers
15 views

Redshift fill sparse data at a group level

I have a table in Redshift (which I don't control) containing the history of issues within an issue tracking system. For any given issue, there are a number of changes to elements like the issue's ...
0
votes
0answers
31 views

Calculate daily avg/total user duration that spans over 2 days?

I have a table (lapsed_table) where it stores all a user's login and logout dates and the number of hours a user has spent on the platform. With that, I want to calculate: The daily average ...
0
votes
1answer
36 views

Optimizing SQL query with two table joins and over 40 million rows in partitioned table

I have three tables, lets say their names are: table1 (has total over 40 million rows, but is partitioned with Declarative partition https://www.postgresql.org/docs/10/static/ddl-partitioning.html ...
0
votes
1answer
42 views

Can I join multiple column in a table?

I am working school homework question. I have an inpatient table, I have to determine the top 10 ICD code that have the highest frequency. Please output each code with its frequency (i.e. ...
1
vote
1answer
51 views

Last character in column shift to next column

I have a unique situation I have to fix. Our vendor gave us a data map and things are not mapping out as they said it should. My situation is, starting with a particular column, I need to drop the ...
3
votes
3answers
83 views

“SELECT *” why is it an antipattern

On multiple questions here and on stack overflow i saw people saying in the comments and answers that select * from table is almost always an antipattern without any explaination why. Althrough I can ...
0
votes
0answers
21 views

Display values of joined tables WHERE table 2 col equals something [closed]

I have 2 tables that handle asset management and I'm trying to create a report that lists each asset under its location (which is determined by the desk_id, referencing the location in table 2) Table ...
0
votes
2answers
65 views

SQL Work Hours Only

I am working on a report, one of the requirements is that I find the turnaround time between two dates (columns) - I have done that using a DATEPART function, however, the hours are exaggerated, I ...
0
votes
0answers
22 views

Many to many join taking only one value from table 2

DB-Fiddle The resulting table should have the same number of rows as the first table while adding the column State from Table 2. This State value should be the highest SRC_DT value that is lower than ...
0
votes
1answer
22 views

FileStream files won't deleted after records deleteion

I am working in sql server 2016 with Filestream attribute column. I have a column with filestream attribute activated on it. when I insert a reacord in TSQL , the associated file is created in ...
0
votes
1answer
36 views

Finding the odd row between two tables in Oracle

I have two tables with the following schema id, fruit In TableA the values are like this: A01, Apple A02, Orange A03, Pineapple A03, Pineapple A05, Banana In TableB the values are like this: A01, ...
1
vote
2answers
30 views

SQL Server 2008 R2 - How does the SQL Trigger actually work in this case?

I have a table named _Test which has the following values stored in it. Primary Key : Id I also have designed a trigger on that table ALTER Trigger [dbo].[_Test_Data_Save] On [dbo].[_Test] For ...
-2
votes
0answers
22 views

SQL Server: Select Records From One Table Based on Fields in Another Table [closed]

I'd really appreciate some help with an SQL query across tables. its need to know all the orders of the clients before the orders of table_1 Table_1 Companny | Warehouse | Order | Client | Date ...
1
vote
0answers
63 views

SELECT all the children of a row and concat

I have this requirement that I need to attach a new column in select statement. It holds a string series of the disp_order of its children. I currently have the SQL statement only for the immediate ...
0
votes
0answers
47 views

How to track revenue for a set of users [closed]

I'm trying to track the revenue generated by a set of users, across time. I have users who signed for a paid subscription and never had a paid subscription before that time of x at the same time they ...
0
votes
0answers
10 views

How works the flash recovery area

Im working with QGIS and Im trying to save a Mapinfo map to Oracle Spatial. The map file is around 1GB and has 700k road segment. The issue is when copy the segements from the source map to the db ...
0
votes
0answers
16 views

Reusing the same view or query for different underlying tables

Is there a way in Postgres to make some kind of "parametrized view" e.g. the view that uses the same query but on different tables. I would like to simplify the maintenance of the query below. It is ...
1
vote
2answers
46 views

Difference between Hints and Indexes

Oracle Hints Hints can extend the capability of cost-based optimization. This technique is especially helpful if your distributed query contains an aggregate, subquery, or complex SQL. Indexes ...
-1
votes
2answers
38 views

MySQL Query can we count rows using php to reduce time of count(*) in a query or there is no difference?

Having the following queries, where the first query is returning a field of number of rows found, and the second returns the actual rows: First Query: SELECT count(*) as total FROM individual i ...
-1
votes
0answers
19 views

What is the best query to fetch multiple table, join table is changed from attribute value

I'm writing query. I'm worrying about performance. Because table will have huge data. [Schema] CREATE TABLE `contract` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `section_id` int(10) ...
-2
votes
1answer
25 views

The result from Print (@SQLquery) differs from stored procedure exact result set

This is my query ALTER Procedure [dbo].[OpenExcel] @FileName Varchar(2000), @ExcelName Varchar(2000), @SheetName Varchar(100) , @Header Varchar(50) , @...
0
votes
1answer
18 views

How do I generate a date series in PostgreSQL without hard coding the dates? [duplicate]

I want to generate a series of months. The start should be the first month of the current year, the stop is the current date with an interval of 1 month. I used the function generate_series (see ...
1
vote
1answer
13 views

Finding average of multiple delta times

I'm looking to find the average interval between successive rows across the whole query - delta-previous in below example: SELECT *, "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER ...
-1
votes
2answers
35 views

Time Difference Between Two Consecutive Rows

i need simple SQL to calculate the time diff between Two Consecutive Rows anyone can help
1
vote
0answers
23 views

Getting text surround search queries?

I'm looking for a way to retrieve search results from an sqlite3 db, including some text around the query, to show context in the search results. I'm not finding any examples of this. Is this ...
2
votes
0answers
27 views

Modeling Database to Support Sub-Users for Users [closed]

I'm having a hard time wrapping my head around how to best model my relational database. I want to be able to structure it in such a way that a User on my site can create Sub-Users that are able to ...
0
votes
1answer
19 views

Update postgres array jsonb

I'm using postgres 9.6, and my question is about updating a jsonb array column. My table looks more or less like that: ID Name MyArray 5 John [{"key":1,"key2":a}] 6 Mike [{"key":4,"key2":b}] ID ...
2
votes
2answers
93 views

How can I insert records in a table that are dependent on other tables?

I have 3 tables: table1: Employee_class(PK), Executive_lounge, Executive_washroom, Supply_closet, Employee_lounge, Server_room, Server_closet table2: Emp_id(PK), LName, Years_of_service, ...
-1
votes
1answer
49 views

Why the error: “The column doesn't exist”? [closed]

I'm using postgresql 9.4 and I'm trying to run this: SELECT a."CI_PROFESOR", a."NOMBRE_PROFESOR", a."OFICINA_PROFESOR", c."CODIGO_RECURSO", c."DESCRIPCION_RECURSO", ...
1
vote
1answer
22 views

Using “Where clause” In OPENROWSET as variable

I need to have a where clause in OPENROWSET but it is not always fix. It might be null sometimes. I have a procedure as you can see here : ALTER PROCEDURE [dbo].[OpenExcel] @TableName VARCHAR(2000) ...
-3
votes
2answers
23 views

Insert the columns from one table to the other with the data [closed]

I have report with stored procedure , where i can see two output resilts because of two select statements I have output like below, but i do need it to be one like in yellow color first output on ...
0
votes
1answer
39 views

Execute UNION and after LEFT JOIN in MySQL

I have 3 tables cdc_vw_Marbetes_Catalogo_Exis tblMarbetesAdicionales tblMarbetesCongeladas First I make a UNION of the tables cdc_vw_Marbetes_Catalogo_Exis with tblMarbetesAdicionales. (SELECT ...
0
votes
0answers
14 views

Skip row if Postgres COPY command can not validate syntax of an import file column

I know that it is possible to use null parameter to convert empty strings when getting errors like: invalid input syntax for integer: "0A" by using COPY my_table FROM '/my/file.csv' (FORMAT csv, ...
-2
votes
1answer
20 views

Using Postgres \COPY function with TSV format

I am getting ERROR: COPY format "tsv" not recognized when trying to copy file contents with psql \copy function. For some reason Postgres does not know TSV format? Command that I am running is: psql -...
11
votes
5answers
1k views

How do I limit a SQL stored procedure to be run by one person at a time?

I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time. While this ...
0
votes
1answer
28 views

Changes between Mysql version 5.1 TO 5.7

I have recently migrated Mysql 5.1 to Mysql 5.7. DB Migration was successful but when the application started firing few selected queries, Mysql started taking hours to execute them for which ...