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
2answers
19 views

Group results from two differenct tables on same hours

I have two simple tables: indoor id | timestamp | temp | humi outdoor id | timestamp | temp and two selects which give me time and average temperature grouped by same hour for the last 24 hours: ...
0
votes
1answer
27 views

How to access Multiple DB2 Relational Databases In A Single Query

I need a solution for below: There are Two DB2 instances with two authorization ID on the same server and can be access using same user User1. Example: To access Inst1DB, I use the following ...
0
votes
1answer
20 views

Postgres/PostGIS is returning data from a table that doesn't exist (I'm a newbie — show me why I am wrong)

Ok, so I am following a tutorial (here) for PostGIS. I have uploaded all the example datasets (all shapefiles) into a postgres db, although I have modified the names so they do not all match the names ...
1
vote
1answer
31 views

Get normalized representation of SET datatype

In MySQL you can SELECT SET values from a table using this syntax: CREATE TABLE `test_set` ( `int` int(11) DEFAULT NULL, `set_2` set('a','b','c') DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=...
4
votes
2answers
87 views

Unable to output data from tables in pgAdmin4 due to missing oid operator

I am currently running Postgresql 10.6 locally which I interface with using PgAdmin 4.12, up until today everything was running fine. However today I ran the following query in the pgAdmin query ...
0
votes
1answer
34 views

Postgresql, case, select in when

System PostgreSQL 11.5 (Ubuntu 11.5-0ubuntu0.19.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 8.3.0-6ubuntu1) 8.3.0, 64-bit Case Does select work in case condition? I need return 1 state ...
0
votes
3answers
37 views

Select youngest record

I have a MariaDB table "mdata" with the following schema: +-----+-----------+---------------------+------+ | id | room | date | temp | +------+-----------+---------------------...
0
votes
2answers
22 views

Count two different columns from two different tables and throw into a single variable

I have two tables in my project: "cats" and "items". Every single item in "items" table has the value "item_cat" (column), which represents the ID of actual category, where that item is assigned to. ...
0
votes
1answer
14 views

How to get the customer payment count for each row in MYSQL

I have a payment system, and want to display the counter of each customer payment (first, second, third ...) and if it's the fist payment, display (New). How can i do this in one query ? SELECT c....
3
votes
1answer
88 views

Selecting rows conflicting values in WHERE clause

I am having trouble selecting rows in MySQL. I do not want some rows to be selected, but they are being selected. My query: SELECT name,url FROM additions WHERE enabled = 1 AND ...
0
votes
0answers
31 views

How to retrieve all children of a parent record?

I have one MySQL table with this columns: id father son street city I want to obtain all descendants of a father recorded, how I can do this? I tried this: WITH RECURSIVE filhos AS ( select login, ...
6
votes
2answers
446 views

NULL value causes blank row in SELECT results for text concatenation

I have a query for producing some text for creating some VALUES text to put in a .sql file for inserting some rows. I get a blank line in the results: postgres=# SELECT ' (' || obj_id || ', ''' || ...
0
votes
1answer
32 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
92 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
32 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
24 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
91 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
28 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
252 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
33 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
143 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
484 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
71 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
44 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
23 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
24 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
43 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
20 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
45 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
38 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
27 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
57 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
36 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
55 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
15 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
43 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
53 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
26 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
520 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
51 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
93 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
38 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
158 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 ...