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.

Filter by
Sorted by
Tagged with
0
votes
1answer
30 views

Select with if statement return 'column does not exist'

Database, application. PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1), Dbeaver 6.1.1. What I need? Return right values. What I do. Here is part of code. select if(available_count < 5, "neni skladem"...
1
vote
3answers
71 views

T-SQLRevoke permissions to all tables in the sql server in one go

How can we revoke a certain permission to all tables? I've tried (T-SQL): REVOKE SELECT on schema::DBO TO [public] AS [dbo] But nothing happens.. All tables still have Select permission. I can ...
0
votes
1answer
28 views

Getting Values of the two rows before the selected row POSTRESQL

I want to update an row according to the values of two rows before the row how can i do that? Let's assume my table looks like this ID amount 1 1 2 3 7 5 And ...
0
votes
1answer
15 views

Options and best practices for Multiple Result Sets in Postgres11

I want to get two (small) result sets and have the benefit of precompilation and 1 round trip to the database. SQL Server allows a stored procedure to contain multiple select statements. SELECT u....
0
votes
2answers
26 views

CHARINDEX equivalent in DB2

I am trying to select the domain from the column where I have email addresses. For example, I have a table email_table and a column email with email addresses as below. johndoe@gmail.com jhefid@...
0
votes
0answers
27 views

How to get custom function result in query in MongoDB

I have experience in MySQL and new to MongoDB. What I need to achieve is below. MongoDb collection is like: What I want is a query similar to MySQL as: Select title,binary,hummingDistance(this....
-2
votes
1answer
49 views

syntax error at or near “AS” [closed]

I have a program that takes in one or more than one table names, from a list stored in a reference table and runs a SELECT query on it. so I retrieve the table names and insert it into the SELECT ...
0
votes
1answer
31 views

Improve time for Select first 10 Rows of a Table with 10 Million Rows Postresql

I am working with a table of 10 million rows and I am selecting the first 10 or the last 10 rows with this query: SELECT * FROM category_member ORDER BY (p::float8/q) DESC LIMIT 10; I am ordering ...
0
votes
2answers
91 views

Is it possible to write a SELECT statement to obtain all dates between 2 date columns?

My question is related to these 2 questions: https://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates/27319120 https://stackoverflow.com/questions/1378593/get-a-list-of-...
9
votes
2answers
445 views

Does SELECT remove dead rows like VACUUM does?

I was fiddling with VACUUM and noticed some unexpected behavior where SELECTing rows from a table seems to reduce the work VACUUM has to do afterwards. Test Data Note: autovacuum is disabled CREATE ...
0
votes
2answers
67 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
41 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
1answer
20 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
15 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-...
-1
votes
1answer
41 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
16 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
44 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
12 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
29 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
53 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
34 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
52 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
42 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
36 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
44 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
22 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
375 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
31 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
64 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
37 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
113 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
43 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
30 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
147 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
64 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
26 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
121 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
37 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
810 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
82 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 ...