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

Writing queries to request or change information stored in a database or other information management system. Questions should include table and index definitions, sample data, expected output, and a tag for the specific system (e.g. MySQL, PostgreSQL, Oracle, SQL Server, MongoDB).

-1
votes
1answer
26 views

SQL Server database design for fields of different data types - single table with multiple columns or multiple tables for each data type?

We have a production application which gathers data for certain type of incidents, based on a collection of fields (aprox. 1000) of different data types - string, int, double, date, time, GUID (id of ...
1
vote
2answers
30 views

SQL query, group by and number of occurrences [on hold]

I have the following table (only one column): X ---- 1 1 1 2 2 3 3 3 4 4 5 6 6 7 7 7 I need a SQL query which will output the following: Occurrences | Number 1 1 * there are only 1 entry with one ...
0
votes
1answer
23 views

How to select specific items from table A which appear with all items of some column in table B?

I have 2 tables with the following schemas: t1(id, type) where id and type are the primary key t2(type, rate) where type is the primary key I want to find all id's which appear with all types from ...
1
vote
0answers
35 views

Adding field in SELECT cause Using temporary; Using filesort

I have query with JOIN EXPLAIN SELECT o.id, ostring.services_string FROM offers o LEFT JOIN offers_string ostring ON o.`id` = ostring.`id` WHERE o.`date` >= '2018-09-22 00:00:00' o.`date` <= '...
0
votes
2answers
36 views

SUM value in table with timestamp column where timestamp is between start and stop timestamps in different table

I have a situation where there are network hosts whose monthly bandwidth is summed based on a start date that varies for each host. The number of hosts is quite small (< 5000) but the history ...
1
vote
3answers
48 views

Query To Compare Data

I'm trying to build a query to show rows in a Customer table that match rows exactly in 2 columns (Address 1 and Address 2) but then show only if a 3rd column is not matching. Customer ID Status ...
0
votes
1answer
34 views

Selecting a group from groups based on one row condition

There is a group of rows which have the losscode and service odom. The contractnumber and service odometer are used to group the rows. Please, may you help me to find the way to select only the row ...
0
votes
1answer
32 views

Need to create join on multiple ids on same cell

I have a table uploadedFiles which I have a column assign_users_id which has multiple users in a single cell: +---------+-------------+----------------+ | File_id | file_Name | assign_users_id| +---...
-3
votes
0answers
13 views

Duplicate row many times according to value [duplicate]

What's the easiest way to duplicate rows? id name value y-02 ADAM 3 T-09 IDRIS 2 Result y-02 ADAM y-02 ADAM y-02 ADAM T-09 IDRIS T-09 IDRIS
1
vote
2answers
23 views

How to select records with two columns matching values in one table in PostgreSQL?

I have a table with the below structure and data: create table employee (id int, name varchar, father_name varchar); insert into employee values(1, 'John', 'Alex'),(2, 'Simi', 'Expatri'),(3, 'John'...
0
votes
1answer
18 views

How to join 2 tables when calling a count on the second table

I have 2 tables. 1 is called coaches, the other coach assignments. coach assignments keeps track of what coach is assigned to a customer. When new customers come in I want the coaches with the fewest ...
-3
votes
1answer
20 views

Query multiple JSONB columns for an element [closed]

I have a dynamically generated materialized view in this form: { "f1": { "student_attempt": [ 41, 1 ], "Registration No": "E021-01-059/2014", ...
2
votes
1answer
50 views

Are UDF used by a stored procedure optimised/merged in the SP itself?

The issue has sent me crazy for a few days, I may have found my answer, but I need a confirmation and to check for an eventual easy solution. When a stored procedure uses some user defined functions, ...
1
vote
1answer
26 views

Fetch ids(Primary Key) from table where ids not available in table within range

I have table named Student. It have primary key student_id which is auto increment and not null. Now Student contains around 5000 records. After passed out from university, we need to delete ...
1
vote
1answer
64 views

Query execution time is random, need to identify the root cause

I have a query like below select * from table1 where column2=123 and column3='B' Now the issue faced by us is that this query is executing very slowing sometimes as seen in our performance tracking ...
-2
votes
1answer
41 views

How to group a few row values together to put into a new table? [closed]

I have a table: +-----------+-------+ | risk | count | +-----------+-------+ | high | 5 | | low | 30 | | medium | 81 | | very high | 2 | | very low | 72 | +------...
-3
votes
1answer
47 views

Help to understand query! [closed]

I’m pretty confused with a query I got, I tried with a regular join but I think ot won’t work. I would like to get some clarity to understand how it works! Please see the image attached!
1
vote
0answers
35 views

Efficiently query product variants from inventory database (avoiding EAV)

I'm building an ecommerce website for fun with three main components: frontend in Angular, MySQL database and a PHP backend that will function as a REST API. The inventory database consists of ...
0
votes
1answer
13 views

Getting data from 2 tables by JOIN, when @Foreign Keys exist to the same Table/Object

I have 2 Tables: User id | name | email | staff 1 name1 email1 true 2 name2 email2 no 3 name3 email3 true 4 name4 email4 no Note id | text | sender_id | receiver_id 1 ...
0
votes
1answer
10 views

Split comma seperated values and concatenate with other row values in MYSQL

I have a table like below | ID | Numbers | ----------------------------------------------------------- | 6 | 3245,78765,1234,43566 | | 7 | 8245,88093,8326,92365 | ...
1
vote
2answers
21 views

Mysql getting first and last value from timestamp column

I am not getting the right result from my below query: SELECT t.id as TaskId, cl.name as ClientName, p.name as ProjectName , t.name as TaskName, tp.name as TaskType , t.createdate as ...
0
votes
3answers
39 views

Where to find the OID of a newly created Schema

I have created a new Schema in my Postgres db. I want to replace an OID in the pg_class table with the OID of the new Schema. But I don't know where to find the OID of the newly created Schema. Can ...
0
votes
1answer
31 views

How to SELECT multi-columns from a column?

I have a table with the structure book_id int(11), section_id int(11), note_id int(11), item_number int(11), text varchar(255), UNIQUE(book_id,section_id,note_id,item_number) item_number can be 1 or ...
1
vote
1answer
40 views

How to compute a membership expiration date on SQL

This is a follow up to this previously posted question. I'm using LibreOffice Base to work with some membership data. Further expansion might be possible in the future, but for now, the embedded ...
2
votes
1answer
50 views

Speed up SELECT query with JOIN from table with 2.2M rows in PostgreSQL?

There are two tables in PostgreSQL: //has 2.2 millions rows create table account ( account_id text primary key, email text not null, created_at timestamp not null ); //has 1.8 millions ...
0
votes
2answers
38 views

Select repeating number x times and then increment

I need a select query that can generate z amount of rows starting from 1, repeating x times and then increment to the next number. For example 10 rows repeating 4 times would result in the following: ...
0
votes
2answers
27 views

Update values from one table to another where key values are not unique

I've been trying to figure this out for hours and can't seem to get it. If it can be done, any help would be greatly appreciated! I hope I'm clear with what I'm asking :-) Consider the following: ...
-1
votes
0answers
24 views

See all the DELETE Queries along with user name/login? [duplicate]

I want to find out all the DELETE queries (ran on the server) along with the user name/login in Microsoft SQL Server. This query works fine: SELECT qryStats.last_execution_time AS [Time] ,qryText....
-2
votes
2answers
70 views

Find maximum number of concurrent logged in distinct users

I am looking out for a query providing month and year and show 4 (or 5) weeks of the selected month, and for each week it should show the maximum number of concurrently logged in users during this ...
3
votes
2answers
75 views

Select all overlapping ranges from starting range

My question is similar to this one with (I think) significant enough difference. I have a base range and I want to find all other ranges in a table that conflict with it and each other. or rather the ...
1
vote
1answer
35 views

How to select info about indexes with specified columns from all databases in the instance using TSQL?

This query selects all the indexes - its names, fragmentation, and some other parameters from the current database. select idx.[name] as [index_name], sc.[name] as [schema_name], obj.[...
1
vote
1answer
31 views

MongoDB slow query, $regex + sort, 30 million documents

I have a collection that has 30 million documents, and I am trying to do a live search that selects the documents by a keyword and sorts them in a certin matter (it shows first users that have a ...
-2
votes
0answers
24 views

Previous Value in query column [migrated]

I have a table like below: How can I have a column like below using Transact-SQL (Order By Date)? I'm using SQL Server 2016.
0
votes
0answers
36 views

Pivot and Unpivot with just two colums [duplicate]

I need help with SQL PIVOT or UNPIVOT Example SELECT Manufacturer, CarType FROM Vehicles Current returned data looks like this: I would like to get: I want to pivot or unpivot the table to ...
2
votes
2answers
81 views

Query takes too much time (2 stages. each stage fast, together - nightmare)

MS-SQL 2008 R2 (Dell, 2 CPU) This query takes two minutes to run, which is too slow. SELECT * FROM t_events WITH (NOLOCK) WHERE order_no = 2783190 and event_no <= ( SELECT MIN(event_no) ...
1
vote
2answers
60 views

SUBSTRING and IF statement

I have varchar column, its name is column1 and has records like 111232435-0 and 1123443530. The last digit is not always 0; it can be 0-9. Some of them are digits with (-), other are digits. I want ...
1
vote
1answer
111 views

Split datetime records that fall between 10pm to 7am into new record [closed]

I am very new to SQL Server. I am trying to split time in time out records that fall between night shifts from a employee shifts table but I am having a difficult time doing so. Night Shift runs from ...
1
vote
1answer
19 views

extract different number of rows from multiple tables

I have a quick question. I'm looking for the concept behind this, not necessarily a ready-to-use code, but if it's not too complex, I don't mind a quick solution. I have an app that manages accounts. ...
2
votes
1answer
25 views

Why does this query not return records where both fields using CASE WHEN are zero?

I'm attempting challenge 13 on this SQLzoo page, but my query returns one fewer row than it's supposed to. SELECT mdate, team1, SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) AS score1, team2, ...
1
vote
0answers
16 views

MS Access 2016 M:N relationships return multiple columns instead of multiple rows from a JOIN

Let's assume a data model like this: Books could have multiple authors as well as authors could have written multiple books. If a book and its authors are selected by the following statement (this ...
2
votes
1answer
44 views

How to get last and first occurrence of items into separated columns?

I do have the fallowing table: id name value 1 Daniel 3 2 Daniel 7 3 Daniel 2 4 Carol 9 5 Carol 4 6 Carol 9 7 Ray 5 8 Charles 1 I would like to get the first and the ...
0
votes
1answer
61 views

SQL Server: Find out unused queries

I have a database with hundreds of queries created by many people over the years. Now I can investigate for each query if any SSRS report, application for the database or other queries are using each ...
0
votes
1answer
39 views

How to trace a query on Firebird?

With SQL Server we can trace for a period of time, to get what queries are running. How can I do this with Firebird? I'm reading something on their website but there's only user trace like if the ...
-1
votes
2answers
45 views

TSQL Query to Remove Dates in Multiple Formats from Text Values [closed]

I have a table with list of text values that contain dates in any format types. I need a TSQL query to remove the dates from each row in the table. The date samples are below. What is the best way ...
0
votes
1answer
64 views

JOIN and GROUP BY with additional columns

If I run the following query on my database I get the results pasted below: SELECT t.id as ID, j.text as Text, l.location as Location, FROM [text] t WITH (nolock) INNER JOIN Text_Location tl WITH (...
1
vote
1answer
78 views

Query planning for many-many select and sort query

I have a postgres database with a Genre table (40 records), a Book table (~1.3M records) and a many-many table to relate the two (~2M records). I want to select books by a particular Genre and then ...
2
votes
1answer
27 views

Removing Outliers Grouping by Minute

I have a very simple table storing the following fields per http request: id serial url varchar date varchar (yyyy-mm-dd) time varchar (hh:mm) latency integer And I am trying to calculate average ...
0
votes
2answers
48 views

Parameterized Queries in PostgreSQL using PHP fail to handle null values

I'm using PHP and PostgreSQL on OpenBSD 6.1, with pg_prepare and pg_execute to effect parameterized queries. $rs = pg_prepare($con, base64_encode($query), $query); $rs = pg_execute($con, ...
-2
votes
1answer
69 views

Varchar Overflowed an Int Column [closed]

I am having an error with the following query: SELECT scp.phone, scp.extension, scf.fax, sce.email FROM supplier_contacts AS sc LEFT OUTER JOIN supplier_contact_phone AS scp ON sc....
0
votes
1answer
145 views

Join Tables without losing Data

I am in a situation where I need to query a database for data, but am struggling on how to properly form the query. Essentially I need to get data from the following tables: Table: Contact ...