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
21 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
42 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
28 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
44 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
11 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
35 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
37 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
32 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
35 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
15 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
54 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
22 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
30 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
30 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
39 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
29 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
27 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
133 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
24 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
45 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
24 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
67 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
34 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
31 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
256 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
21 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
80 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
44 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
48 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
35 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
31 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
21 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
22 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
117 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
49 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 | +-...
0
votes
2answers
74 views

Group by range DATIME of 2h up or down - Mysql

Well, I have a table with some columns that some of these are repeated, but the time is not exactly the same, I need to do a group by that has a range of 2 hours. Notice that all the fields marked in ...
-3
votes
2answers
37 views

Showing records with same last name but different first names with one of first names meeting a criteria

My SQL query is more complex than this but this part I am stuck. For example if I had a table: lname fname -------- ----- Mitchell Jason Miles Ryan Miles Mary Mitchell ...
0
votes
2answers
40 views

select single row from table for a particular column combination

I have a table as below Id... ColA... ColB... Isdefault 1... 100... 96... 1 2... 100... 96... 0 3... 101... 98... 0 4... 102... 99... 1 I want to ...
1
vote
1answer
44 views

Oracle Subselect Union Multiple Column

I want to select and count all cats and dogs at one location; location | cats | dogs | new york | 150 | 66 | paris | 34 | 12 | What I've got so far: SELECT cats,dogs FROM (select ...
1
vote
1answer
28 views

Select with inconsistent linkage

I have a table with three columns (ID, ReviewToId, ReviewFromId) I would like to get record with the ReviewToId and ReviewFromId is not Consistent For example, with pic attached: I would like to ...
1
vote
0answers
51 views

MySQL count rows of multiple tables for each user

I basically need this with some adjustments, I just can't seem to figure it out how to do it. I need to sum the rows of two tables, for example: SELECT student.StudentID, student.`Name`, COUNT(...
0
votes
1answer
96 views

MYSQL REPLACE string content with field value

How can I REPLACE a string value with a other field content? SELECT REPLACE(html_input,'!value',field_value) FROM table Value of html_input is <input type='text' value='!value'> Value of ...
0
votes
1answer
46 views

What is the best way to check if a value in a table has changed when writing to a database?

Let me provide you with an example. Let's say I have an html_tag with two columns, tag_type and tag_value For example, let's say I have a record with the value "link" under thetag_type field and "...
2
votes
2answers
42 views

Return values as 1 or 0 together with percent

I need a SELECT that will result in a percentage and columns with 1 and 0 for exist or not exist. The goal is to generate data for a usage meter (Percentage 0.0-1.0) and each measurable value as 1 or ...
1
vote
0answers
175 views

Oracle 11g Select query returns NO ROW SELECTED but the table actually has data

In my production, There is a table named ABC under X schema. I have granted SELECT privilege on ABC table to another user named Z. When I execute the query SELECT COUNT(*) FROM X.ABC; as user Z it ...