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
1
vote
2answers
43 views

Aggregate results from select queries

I have a table with three columns: id, date, state. For each unique value of id, I would like to select date and state, multiply the state array by a scalar variable for each id defined from else, ...
0
votes
1answer
594 views

Mysql query to find last entry for each of the last 7 days

I have a table that contains a timestamp and a number for each row. This data is logged every 15mins on the quarter hour, 24 hours a day, but it does sometimes get missed or is delayed. I'd like to be ...
2
votes
2answers
2k views

Select previous value in row without using a subquery [closed]

Say you have a table in PostgreSQL 11 with sets of values something like this below. 12 18 11 12 5 12 9 18 7 19 14 18 11 16 6 12 4 11 5 9 Is there a way to do a select without using sub-...
0
votes
2answers
40 views

Group results from two different 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
33 views

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

I am following a tutorial for PostGIS. I have uploaded all the example datasets (all shapefiles) into a PostgreSQL database, although I have modified the names so they do not all match the names of ...
1
vote
1answer
41 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=...
5
votes
2answers
3k 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
214 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
511 views

Select youngest record

I have a MariaDB table "mdata" with the following schema: +-----+-----------+---------------------+------+ | id | room | date | temp | +------+-----------+---------------------...
0
votes
2answers
36 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
176 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
134 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
42 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
1k 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 || ', ''' || ...
1
vote
1answer
1k 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
2k 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 ...
1
vote
1answer
264 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 ...
3
votes
1answer
986 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....
2
votes
2answers
5k 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@...
-2
votes
1answer
7k 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
34 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
3answers
12k 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
952 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
2k 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
56 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
45 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 ...
1
vote
1answer
472 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
131 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
55 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
518 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
22 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
2answers
152 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
33 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
1k 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
44 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
66 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
37 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
74 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
35 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
49 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
57 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
1answer
145 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
3k 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
663 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
2k 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 ...
2
votes
2answers
47 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
1k 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
217 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
35 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
67 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
3 4
5
6 7
20