Stack Exchange Network

Stack Exchange network consists of 175 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

Questions tagged [select]

A core SQL statement, SELECT retrieves data from one or more tables or other sources of row set data such as views or table-valued functions.

0
votes
1answer
43 views

SQL: Comparing multiple dates

I got a table with multiple columns that contain DateTime namely gendate1 up to gendate8. In my stored procedure, the user will have to input a date (@date_start). Now I have to compare the inputted ...
0
votes
1answer
31 views

Oracle - large table with large IN

Table students has about 10 million records; ID is indexed. Student grades has 20 million records - student_id is indexed. I am querying about 20,000 students by their ids ids with a join between ...
0
votes
0answers
7 views

Select based on list of pairs

I have a table with two main columns, id1 and id2: drop table if exists pairs; create table pairs (id1 int, id2 int); insert into pairs values (1,2), (1,3), (3,1), (2,4), (5,4), (3,6), (7,8); I ...
0
votes
1answer
12 views

Postgres : Select items for an interval from the max date in store

I have a database with items looking like this : id , item_id , date_item , price 0 , 3232 , 2019-04-22, 10 0 , 3232 , 2019-04-23, 4 0 , 3232 , 2019-04-24, 1 0 , 3232 , 2019-04-...
0
votes
1answer
32 views

MySQL: Delete statement from complex select subquery

I would like to delete the output of the below SELECT command but I am not a DBA and my SQL knowledge is rather elementary. I want to delete the users matching the SELECT statement. SELECT count() ...
0
votes
1answer
12 views

SQLite return sum of duplicate rows based on array

If I have a simple table fruits that looks like this: +---+---------+------+ | | name | lbs | +---+---------+------+ | 1 | apple | 0.25 | | 2 | melon | 3.00 | +---+---------+------+ In a ...
0
votes
2answers
37 views

Query not returning results if selecting by indexed columns (Mysql 5.6)

We had a very strange encounter that all of our queries that uses an indexed column in the select wasn't returning any results. On the other hand, if we do select * or on any other non-indexed column ...
-1
votes
1answer
21 views

how do I get date field in certain string format

my table has below columns A: date B: date C: and many more columns, I do not want use to_char(A, date_format), how I can do select * from mytable, and the returned A, B column are in certain ...
0
votes
0answers
10 views

How to always select records in array or empty array

I have a field records of jsonb[] type and I need to insert records from other tables(has many relation) whenever there is an update via trigger. When I apply select SELECT INTO records * FROM "...
0
votes
2answers
28 views

select all tables from dba_table except list

I'm missing something in my query. I want to select all tables from dba_tables except a list of tables. For example, show all except HR.DEPT, SCHEMA.TAB dba_tables: OWNER TABLE_NAME ------ ----...
0
votes
0answers
26 views

How can I optimize this MySql Select query?

I have the following query executed every 1 minute on a cron job SELECT COUNT(*) FROM `Orders` AS `o` WHERE (`o`.`OrderStatus` = 0) AND (`o`.`CreatedAt` > DATE_ADD(UTC_TIMESTAMP(), INTERVAL -1....
1
vote
3answers
48 views

Is there a more concise way in Postgres to extract a portion of a string?

I'm using Postgres 9.5. I have a table with a column that records URLs. Sometimes the URL has a query string and sometimes it does not. I would like to extract the URL, minus any query string, so I ...
0
votes
2answers
33 views

For states (col) receiving exactly X number of items (row), sort the items by weight (col)

I have been trying to figure this out for a few hours now but haven't gotten it. Each row in the table is a package. I am able to get the States with the exact number of packages, but don't know how ...
2
votes
3answers
51 views

How to group many information une one select

There are my database articles id | art_code | art_name 1 | 5678 | computer 2 | 0987 | display 3 | 8547 | keyboard action id | code | action | qte 1 | 5678 | IN ...
0
votes
1answer
13 views

Find IDs in table where at least two entries exist with that ID and an additional field is A for one and B for the other entry

I have a game where we store user results in a table, similar to this structure: game_id, user_id, score, .... Now our service team would like to find all game_ids where user A and user B played ...
1
vote
2answers
39 views

Inner join only for not empty values

i can get right result using the where join , with next sql: WITH fktable1 AS ( SELECT 2 AS id ) ,fktable2 AS ( SELECT 3 AS id ) SELECT main.* FROM -- main data ( VALUES (1, NULL, NULL), ...
2
votes
3answers
25 views

Get The Lowest Date if The other Column is Null

I have the following CREATE TABLE employees (employee_id int, from_date date, to_date date); INSERT INTO employees(Employee_ID,From_Date,To_Date) VALUES (1 , '1998-01-02' , NULL), ...
0
votes
1answer
40 views

Left join SQL Select Statement

Good day, Im trying to find a good way on my select statement Table1 AccountID AccountName Rate OldRate 01 Smith X X2 02 Providence X X2 03 ...
1
vote
1answer
34 views

IN subselects vs INNER JOIN (performance)

I have to make a choice: choice 1: using IN subselect choice 2: using INNER JOIN See the examples: IN subselect: SELECT contractServiceCode FROM contractServices WHERE contractID IN ( SELECT ...
0
votes
0answers
40 views

Why two select statements are causing deadlock? S and IX locks

<deadlock> <victim-list> <victimProcess id="process516293848" /> </victim-list> <process-list> <process id="process516293848" taskpriority="0" logused="17436" ...
0
votes
1answer
17 views

Issue with Maximo KPI Returning “0” Instead of a Calculated % [closed]

I am attempting create a KPI in Maximo that will count the work orders completed on time and divide it by the number of work orders due for the current month, then multiply it by 100 to return a ...
0
votes
2answers
199 views

Mysqldump multiple tables with where clauses in single transaction

Suppose, we have 2 tables: t1,t2 in database db: Expected the below code to dump tables based on conditions specified in the where clauses: mysqldump --login-path=login_path \ --single-...
1
vote
1answer
24 views

SQLITE: Select row ID based on only when the column all the values are same

I have a table where more than one rows with same ttid. I would like to get ttid rows based on status column is done. tb tbid ttid status ----------------------------- 216 6 5 done 216 6 ...
0
votes
1answer
44 views

Limit groups of results based on a column value

I'm not sure I formulated the question properly, but anyway. I have a table with level-2 comments, some of which, expectedly, have the same comment_parent column value. How do I make a SELECT query ...
1
vote
1answer
36 views

Recalculate and replace large table without interrupting table reads

There's a large table in the database containing statistical information. This table must be read frequently (by lookups which return a single row). This table must be completely recalculated every ...
0
votes
1answer
53 views

SELECT vs COPY (SELECT) TO STDOUT

I need to read more rows than fit in memory, resonably fast. I see two options: SELECT ... where my bindings use a cursor to stream the rows; COPY (SELECT ...) TO STDOUT (FORMAT binary) where my ...
-1
votes
1answer
40 views

How to do group by based on another column in SELECT? [closed]

I have a table on which I do a SELECT statement. It is joined to some other tables. With the SELECT one column is got but a sub query. like (select top 1 .. from..) for each row. Now I need to do an ...
1
vote
1answer
30 views

What happens if I load last 10 rows then next 10 after other rows are inserted?

What if I had a PHP script that had a pagination that loads last 10 rows then page two loaded next 10 in DESC order, but before loading next 10 on page 2, other 10 rows were added to the table? What ...
0
votes
2answers
29 views

How to SELECT most common (viral) from a table and LIMIT results for pagination?

Sorry if it's a dubplicate but I can't find an answer to the question. Hello there so I have three tables users and articles and likes, a user can write an article, in the table articles there is ...
1
vote
3answers
138 views

Improve Query Performance of Select Statment inside IN Statment

My query is: (3) UPDATE wp_postmeta set meta_value = 'outofstock' where meta_key = '_stock_status' and post_id in ( (2)SELECT post_id FROM ( (1) ...
0
votes
1answer
25 views

Selecting the most specific match from one table

So let's say there is a simple table for dogs: CREATE TABLE IF NOT EXISTS dogs ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, breed char(20) NOT NULL, color char(20) NOT NULL, ...
2
votes
1answer
51 views

Get total number of rows when offset is greater than max

I have a table users that has, say, 42 records. To get the total rows and some other details, I use a window function: select count(*) over() as count, name, id from users And since the consumer app ...
1
vote
1answer
25 views

LEFT JOIN based on content of a field in matched rows

Generically, I'm attempting to take a table and join it with another table but only on rows that have a specific value in one of the fields. Specifically, I have a users table and a contact table. ...
1
vote
1answer
27 views

SQL How to show information from others tables in select requet

There are my database user id | user_code | user_name 1 | 1x2d | Peter shops id | shop_code | shops_name 1 | 1234 | shop1 2 | 3456 | shop2 articles id | art_code | art_name 1 | ...
1
vote
1answer
91 views

Select within or closest to date range MSSQL

Lets say we have a table CREATE TABLE [dbo].[Product]( [ProductId] [int] NOT NULL, [Name] [varchar](50) NOT NULL ) CREATE TABLE [ProductPrice]( [ProductPriceId] [int] NOT NULL, [...
0
votes
2answers
35 views

MYSQL search for each name in 2 columns and add values associated for each player

I have a database "game_results". It contains the following: PlayerID (int not null primary key auto increment) player1 (varchar) p1destroyed (int) player2 (varchar) p2destroyed (int) winner (...
1
vote
1answer
33 views

How to find maximum of average grouped by in sql

I have three tables Faculty {id, name}, Student {id, faculty_id, name}, Course_student {id, student_id, mark} And i would select top student in each faculty I'm tired to perform this in multiple ...
0
votes
1answer
547 views

Why select statement against a table in oracle returns ORA-04021

I have a big partitioned, local indexed table in Oracle database, what I am doing is import a big dump file into it with "impdp" Why when I issue a select statement against that table Oracle returns ...
1
vote
0answers
22 views

PostgreSQL update and select in one query [duplicate]

I have a table (id, serialnumber, status) of numbers and their statuses, need to take one with status 'unused' and change it's state to something like 'in work'. Can do it this way: UPDATE serials ...
2
votes
1answer
81 views

Filter on value in another row

Please see image of the data and my code so far. In this example I only want the first two rows to be retrieved. The reason for this is because where JobID is 1684 the sum of STILL_REQUIRED is > 0 ...
0
votes
2answers
64 views

SQL firebird dont select rows with 0

This works fine, except in the results, I don't want the rows where STILL_REQUIRED is 0. I am using firebird. Select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE, START_OFFSET_MINS, ...
2
votes
1answer
61 views

Select the same field based on different foreign keys

I have two tables: Containing a list of products with ids and names A table linking several products with parent-child relationship based on ids using foreign keys. I want to create a view that will ...
0
votes
1answer
37 views

Why is searching by one varchar column not working correctly on replica?

PostgreSQL: 9.6, master, replica. Async replication, wal archiving. We detected that if we execute a select query on a varchar column against a replica, then the results return no rows, but if we ...
1
vote
2answers
32 views

Showing data that is not already in the data base

I have the following problem. I want to search a query in an existing data base and that the result shows me the entries of the query that are NOT already in the database. Minimal example: | ID | ...
1
vote
1answer
29 views

Selecting only when specific values exist in another select query

A bit of a noob question, but would appreciate the help. I want to use the result of one select statement in a different one in a specific way. The first select statemnent: SELECT DISTINCT id FROM ...
0
votes
1answer
40 views

SQL, creating a new table with auto generated passords from other tables

i have this sql below which i have retrieved from our student database. It retreives all that i need, but i would like to create a new table which includes all the tables plus one column with auto ...
0
votes
1answer
29 views

How to get Row Value & Sum/ Aggregate in Same row? (SQL Server)

Just wondering if I have data like name score A 2 B 5 C 7 I would like to have a 3rd row name score total A 2 14 B 5 14 C 7 ...
3
votes
1answer
197 views

SELECT Parent-Child with SORT

I have a simple, one level parent child relation table, with following columns: ID_Asset| Parent_ID_Asset | ProductTitle I need output grouped by Parent followed by children, and also sorted by ...
0
votes
1answer
91 views

Select rows from table 1, table 2, then selecting based on ID

I am dealing with a large dataset, and have run into some issues because of this. I have a data set like: Table1 State ID Conditional_Value AZ 1 Yes AZ 2 ...
0
votes
1answer
17 views

MySQL 8.0 - SELECT QUERY

Hi I am having trouble with building a select query command with my tables. TABLE CUSTOMER +-----------------+-----------------+--------------+ | customer_number | customer_status | order_status | +-...