Questions tagged [query]

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

Filter by
Sorted by
Tagged with
2 votes
1 answer
203 views

Why SERIALIZABLE level does not lock the table for other session in SQL Server

I want to Prevent concurrency in my table for Delete, Insert, Update, and Select so I tried to use SERIALIZABLE for the table before transation, but that didn't lock the table for other processes! how ...
user avatar
-4 votes
0 answers
18 views

I am confused on how to get the total of a total column query

SELECT [Parts].[Price]*[transaction].[Quantity]+[Services].[ServiceFee] AS total, Services.ServiceFee, Parts.Price, transaction.Quantity FROM Services INNER JOIN (Parts INNER JOIN transaction ...
user avatar
-3 votes
1 answer
46 views

Retrieving range of values from a lookup table using SQL

I am using SQL Server. I have two tables Result and Grading. Using T-SQL select statement, from the lookup table (Grading),I want to fill Grade column in Result table with the knowledge of range of ...
user avatar
1 vote
2 answers
45 views

Getting the total size of rows with same ids across multiple tables restricted to a particular user id

I'm trying to calculate all the projects size of one user across multiple tables. a unique project is separated into 4 tables with multiple rows assigned to it. for the moment I'm only able to ...
user avatar
  • 133
0 votes
0 answers
22 views

how to print message 'included' along with limit_1 value, when limit_1 is selected with condition

select max(Contract_Num) as contract_num, max(Lic_Plate_Num) as lic_plate_num, max(cast(Revision_Eff_Date as date)) as rev_eff_date, max(contract_name) as cn_name, ...
user avatar
1 vote
0 answers
19 views

Finding sub-groups by relationships within groups

In this example, two tables define the groups and the relationship of persons within each group CREATE TABLE groups ( group_id int(11) unsigned NOT NULL, person_id int(11) unsigned NOT NULL, INDEX(...
user avatar
  • 4,261
0 votes
1 answer
16 views

select row matching exact list of uids

Given table below id | groupid | memberid 1 | g1 | m1 2 | g1 | m2 3 | g2 | m1 4 | g2 | m2 5 | g2 | m3 I'm trying to build a query where I want to get the groupid ...
user avatar
  • 1
1 vote
2 answers
29 views

How do you check if two tags exists in a tag_map GROUP By article_id?

In a fairly simple tag_map, CREATE TABLE tag_map ( article_id int(11) unsigned NOT NULL, tag_id int(11) unsigned NOT NULL, INDEX(tag_id), PRIMARY KEY(article_id,tag_id) ) ENGINE=InnoDB; How can I ...
user avatar
  • 4,261
0 votes
1 answer
42 views

How to display last record per parameter?

I have a query in SQL server I use to get basic stats from two tables of sites and records associated with sites. I'm having trouble figuring out how to ALSO create a column that displays the last ...
user avatar
0 votes
1 answer
26 views

Add new column based on conditions and the value difference of other column

I think I might be overlooking something obvious here. I'm not asking someone to write the whole code for me, I just need a hint or a link to a similar case. Thanks in advance... My query: select ...
user avatar
-1 votes
0 answers
14 views

MySQL query returns string instead of integer

I am attempting to complete a problem on Leetcode that requires the output to be an integer or a string 'null'. My attempt is to use a CASE statement that returns the appropriate response depending on ...
user avatar
  • 1
0 votes
1 answer
24 views

Can't get this GROUP BY or DISTINCT right

On MySQL 5.7.34 I have this simple db structure. Important columns are domain name, domain total links in, but entries are duplicate (once for each found link from various sources), I need to select ...
user avatar
  • 133
0 votes
1 answer
24 views

Find records with join matching all join IDs

Suppose to have the following database setup: Users ( id int primary key, name varchar(255) ) OrgUnits ( id int primary key, name varchar(255) ) Users_OrgUnits ( user_id int, orgunit_id ...
user avatar
0 votes
1 answer
19 views

Better ways of concating the value of columns

I have a table with below structure and sample data: create table CUSTOMER_TEST ( customer_num Number, rel_one Number, rel_two Number, rel_three Number, rel_four Number ) ...
user avatar
  • 1,304
3 votes
2 answers
1k views

MySQL: How to optimize a certain SELECT statement which causes very high load?

Having a table with 25.000.000 entries, I have the following query which is started every ~ every 2 seconds which causes very high loads (up to 40 AAS in Amazon AWS). The execution takes 20 seconds up ...
user avatar
  • 195
0 votes
1 answer
42 views

Syntax error in a simple SQL query [closed]

I am using pgAdmin to write a simple SQL INNER JOIN query. However, when I run the code it only will run the first line and give me a syntax error shown below. But, I can select the whole code and it ...
user avatar
1 vote
0 answers
52 views

Postgres: one-to-one join with duplicates and inequality matching

** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 ** I have two tables taken from two data sources,...
user avatar
0 votes
2 answers
40 views

How to query through foreign keys while only having the value in the the same row as the value used as foreign key in another column

db admins, I require your help! My experience working with databases directly using SQL is rather limited, since I've mostly been using ORMs to cover all my needs as a frontend developer, but this ...
user avatar
3 votes
1 answer
152 views

Postgres order by with multiple columns

So imagine I have a result set that looks something like this: +----+------------+------------+ | Id | Date_One | Date_Two | +----+------------+------------+ | 1 | 2022-05-12 | null | | 2 |...
user avatar
1 vote
0 answers
45 views

MS Access SQL - Left Join (or sub-query?) most recent related record

Hello Database Administrators. Thank you so much for your help with this issue. It blows my mind that something so simple can be so complicated. I have an MS Access database with two tables. The first ...
user avatar
  • 11
0 votes
1 answer
28 views

How to query nested unamed jsonb objects and order?

have an audits column (jsonb) seems like: {{"status": "pending", "updated_at": "2022-06-20T15:58:54-03:00"},{"status": "active", "...
user avatar
1 vote
1 answer
32 views

How can I correct this mysql join query and put 1 to many in the same row?

This query is close to what I need but google_responsive_headlines.responsive_headlines has many rows to google_text_ads. I would like to have those results in the same row but not sure how to ...
user avatar
4 votes
4 answers
159 views

Select only rows where all values in a column match a value for an id

Given the table seq_id seq_status 1 A 1 B 2 A 2 A 3 A 4 B 5 C I want to select the distinct seq_id's where the seq_status is A (2 and 3) and not return if one of the values is different so ...
user avatar
2 votes
1 answer
69 views

Multiple inner JOIN on the same table [closed]

I have this table 'individu' and this table I want to do a select and get in the query, the 'nom' and 'prenom' for iid1 and iid2. For the moment my query is SELECT * FROM lien inner join individu ...
user avatar
1 vote
2 answers
80 views

Convert Long list of IN clause into a temporary table

Instead of using a list of items for IN Clause I'm trying to use a temporary table. The code I used previously. SELECT * FROM table1 AS t1 WHERE itemid in ('3363134,5189076, ...) The code I'...
user avatar
1 vote
0 answers
56 views

Get column name and column type in the same query in the correct order using psycopg2

I have a postgresql database and I am querying it using psycopg2 and I need to get the data in the correct order as in table I have found different ways to get the column names from table and column ...
user avatar
0 votes
0 answers
27 views

Keeping a table up to date that has been created using a DB Query

I have created a new table view for customers, so that when they log in, they only see the information that they need to see... I used the Query: CREATE TABLE IF NOT EXISTS Customer( SELECT ColumnA, ...
user avatar
  • 111
1 vote
2 answers
27 views

Applying conditional statement in Oracle DB

In the Oracle DB there are three tables, see below. Table: 'People' Table: 'Dogs' Table: 'Cats' Here is a Fiddle with data to test : http://sqlfiddle.com/#!4/d7cb4 Depending on the result of a ...
user avatar
  • 177
1 vote
1 answer
74 views

Percentage based scan on a timeseries data

I have 20 GB csv file with 650 million rows. The data looks like this: trade_time,price 2020-01-01 00:00:01.481,7189.42 2020-01-01 00:00:01.708,7189.42 2020-01-01 00:00:06.290,7189.5 2020-01-01 00:00:...
user avatar
  • 13
2 votes
2 answers
288 views

Can Somebody help me understand this query

The below SQL query gets all the ids which have different last names. select * from names a WHERE a.first_name in ( select b.first_name from names b WHERE a.last_name<>b.last_name ) I am ...
user avatar
  • 129
1 vote
0 answers
24 views

Getting totals from different tables to create overall total

I currently have 2 tables TableA and TableB` both within the same MYSQL Database. I am trying to find a way to take totals from both tables and add them together to give a definitive total. The tables ...
user avatar
  • 111
0 votes
1 answer
65 views

How to compute the average score for each user, by time interval?

I have a table User that only has an id and a name. A user has many Games, which is a table that contains an id, a score, inserted_at and a user_id referencing the User table. I would like that my ...
user avatar
0 votes
0 answers
21 views

Inconsistent query result from mysql workbench and command line client

I have noticed I am getting inconsistent results for the same query when sent via MySQL Workbench and the command line client. The query is executed on a remote server, in which one of the columns ...
user avatar
0 votes
0 answers
45 views

MS Access and the Dataverse -- lost primary keys and read-only queries

I'm trying to create a database application with tables stored on a Dataverse/Teams backend and an MS Access front-end. I have a simple one-to-many database design, and a form with a query as its ...
user avatar
  • 3
0 votes
0 answers
15 views

DAX Query ALLSELECTED and play axis context

On my visual, I have filters on the left, at the top a "New parameter Field" to change the legend of my graph dynamically. I display the profitability on the abcissa and the amount before ...
user avatar
3 votes
1 answer
155 views

Inconsistent keyset pagination when using (timestamp, uuid) fields

I am using the keyset pagination method for uuids on my Postgres database described in this post: How to do Pagination with UUID v4 and Created Time on Concurrent Inserted Data? However, I have ...
user avatar
  • 31
0 votes
1 answer
28 views

How to get the highest (max) cost, lowest (min) cost, and the date that each occurred?

I need to find the highest cost & lowest cost for each part in a table (There are many parts) in a given date range. And output the date that each cost was recorded. Example Data: CREATE TABLE ...
user avatar
-3 votes
1 answer
106 views

Import 5gb sql file into MYSQL database [duplicate]

i have exported sql file using mysql dump from a database and i am trying to import this big 5gb file into anohter mysql database. I am using mysql -u username -p -h hostname <file.sql command ...
user avatar
0 votes
1 answer
55 views

Amount of clients that have subscription by years and month

I got 2 tables as follow CREATE TABLE subscriptions( client_id INT, open_date DATE, close_date DATE ); CREATE TABLE calendar(date DATE); subscription table contains data about clients ...
user avatar
1 vote
1 answer
43 views

A query containing multiple joins becomes too slow when adding one more condition after the joins

I have this query that finishes in less than a second on average: SELECT a.trea_code, a.fin_year, a.bill_no AS tran_id, ((('Transfer FROM ' :: text || (a.ddo_code) :: text) || 'ON Bill ...
user avatar
  • 13
0 votes
2 answers
52 views

SQL query to return rows whose column has a common value

How can I query a Microsoft SQL table to return only the InstNum that has all Profile types C or U? Please see my table structure below. InstNum Profile 001 U 001 C 001 U 002 U 002 U 002 U 003 ...
user avatar
  • 3
0 votes
0 answers
21 views

How to make PG account for "empty months"?

I'm trying to return the average amount of money I've invested in Bitcoin in the last six months. I have this: SELECT avg("amount") FROM ( SELECT date_trunc('month', "timestamp"...
user avatar
  • 111
-2 votes
1 answer
227 views

Row_number to not increment if values on three columns are the same from the row before

Suppose I have a dataset that looks like the screenshot below and I need to add a count column for the rows based off the orderid, DocumentNo_, and CurrPackageTrackingNo with the following logic: ...
user avatar
1 vote
1 answer
223 views

Postgres "unnesting" multiple json arrays simultaneouesly with different depths

I am trying to explode/unnest a row of jsonb arrays into individual rows and keep any rows that would result in nulls. Fiddle: https://www.db-fiddle.com/f/pgd6E5yKf8NzPtgboeoC6c/7 Input table: (I am ...
user avatar
  • 113
0 votes
1 answer
28 views

MySQL - Getting the percentage of an occurrence

I'll do my best to explain what I'm trying to accomplish. I have a table called "Actions" and in Actions I have a column called "Resolution". In resolution some the entries, as an ...
user avatar
0 votes
2 answers
57 views

SQL Server - Finding Parent Source from T-SQL Snippet

The environments for which I'm responsible have some pretty substantial plan non-reuse challenges. I've run across the following query (h/t Brent Ozar blog commenter Michael J Swart) that does a fine ...
user avatar
3 votes
2 answers
2k views

How to Join two tables with SUM and GROUP BY in SQL server

I have 2 tables product id designation 1 coca 2 Pepsi 3 Fanta 4 Seven 5 Eight 2)sub_product product_id name quantity 1 sm 10 1 lg 10 1 xl 20 2 1L 10 2 2L 20 2 5L 20 3 Ty 10 3 Sa 20 4 ...
user avatar
0 votes
1 answer
40 views

Microsoft SQL server management studio : insert label in SQL query results

I'm working with SQL server, using Microsoft SQL Management Studio. I'm writing quite some SQL queries, one after the other. This causes a list of results, which are quite difficult to distinguish, as ...
user avatar
  • 463
0 votes
1 answer
70 views

SQL query to group results on matching record ID

I am having trouble designing a query. I have a list of PersonIds for records in a Persons table. I would like to have a single query that takes this list of PersonIds and returns other records in ...
user avatar
0 votes
1 answer
93 views

Forcing a sequential scan over the index scan

I have this query select s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment from part,supplier,partsupp,nation,region where p_partkey=ps_partkey and s_suppkey=...
user avatar
  • 95

1
2 3 4 5
27