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
1 answer
18 views

Select function-based index data as rows in a query?

I'm trying to learn about indexes in Oracle 18c. As a novice, it's occurred to me that it might be interesting to view the actual data that's in a function-based index: create index ...
user avatar
  • 1,375
0 votes
1 answer
32 views

Display count of rows from one table in columns in another without knowing how many columns there would be

Let's say I have a Library database and the following tables in it: Books(ID, Title, Author, PublihserID, LanguageCode, Genre, DatePublished, ISBN) - contains examples of books as abstract items, ...
user avatar
1 vote
0 answers
28 views

Very slow MariaDB query when adding ORDER BY/LIMIT but there are no rows

I am facing an issue with MariaDB 10.1 on a log table with the following (simplified) structure: CREATE TABLE logs ( log_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, log_date TIMESTAMP DEFAULT ...
user avatar
0 votes
0 answers
19 views

CSV Resultset for IN clause

I am working on writing a SQL query to get data from 2 tables with inner join and need some help. Here is the scenario. I have 2 tables say A and B. table A Name Value 1 Red 2 Green 3 Blue 4 ...
user avatar
  • 1
0 votes
1 answer
29 views

Using SELECT by key value, the smaller the result count, the slower the query performance

In my MariaDB database I store combined metadata from multiple other sources (face databases). There is a 'main' table containing great majority of data, and each row is identified by both it's own ID,...
user avatar
  • 1
0 votes
1 answer
32 views

Why my simple searching query is too slow?

I don't know why searching in my "cities" table is so slow. My query looking for a table "cities" located about 25km from the city. I use this simple query and the database takes ...
user avatar
2 votes
1 answer
48 views

Select rows where array of letters is contained in given array

I want to search for the letters in the "spelling" (text[]) column: a n n o y t I need it to find the words: any, annoy, no, an, toy But not find: derivatives of annoy (annoying, annoyance), ...
user avatar
0 votes
0 answers
30 views

replace duplicate column values only on one column, from second occurrence onwards

I have a simple query in Oracle 11g select ID,Section,Capacity,Output from yeilds order by section; that returns data in this form ID Section Capacity Output 2 SectionA 45 30 3 SectionA 45 25 4 ...
user avatar
  • 465
2 votes
1 answer
56 views

How to calculate % change for nearest date

I have a Postgres 14 database with a table recording water levels in a number of locations. Readings are only taken on business days: CREATE TABLE water_level ( reading_id BIGINT GENERATED ALWAYS ...
user avatar
  • 125
0 votes
1 answer
32 views

Optimize select query with OR and ILIKE

I have the following query for my PostgreSQL 13 database: SELECT * FROM "user" INNER JOIN "comment" on "comment"."user_id" = "user"."id" ...
user avatar
1 vote
1 answer
45 views

Query hierarchical file permissions

I have a table representing some files with their paths and another table with file sharings by user and files. Below is Table files, simplified. Example: I have a directory A (path /A) containing ...
user avatar
  • 13
-2 votes
1 answer
34 views

Is it possible to group a table based on the condition in MYSQL?

I have table 'request' and I need to somehow group the table where the flag 'is_applicant' = 1 if exiting rows with the same 'id_request' and 'the same id_user' you can see it better by example... I ...
user avatar
  • 1
2 votes
1 answer
56 views

Do UPDATEs block SELECTs?

I need to query a frequently updated record, does it wait until all updates finish? /updateUser/1 Many requests are sent UPDATE users SET count = count + 1 WHERE id = 1 LIMIT 1 /getUser/1 While the ...
user avatar
3 votes
1 answer
60 views

Add a new row for each jsonb value pairs

I have this table from were I'm running a query: CREATE TABLE IF NOT EXISTS public.preprocess_things ( preprocess_id integer NOT NULL DEFAULT nextval('preprocess_things_preprocess_id_seq'::...
user avatar
  • 49
1 vote
2 answers
22 views

How to select grouped column in one table and other column in different table with third table with foreign keys?

I am using my PostgreSQL and here is explanation what is wrong: I have first table river_changes and it has structure: id_hydro size date 1 50 2002 1 55 1992 2 5 2005 2 20 2022 2 10 2016 3 80 ...
user avatar
  • 13
1 vote
0 answers
26 views

Select value and display in a combobox if the datetimepicker value lies between two extreme dates in a table in database

I am creating an attendance management system in vb.net and using MS access as my database. I created a leave form in which the student on leave has a roll number and his details along with the from ...
user avatar
  • 11
-1 votes
2 answers
37 views

I want to select many columns from many tables without foreign key

I have three tables, and I want to fetch the three table data without a foreign key. Say I have a table product_1 which contains (id, name) and table product_2 contain (id, name, price). I want to ...
user avatar
0 votes
1 answer
45 views

Mysql grant restricted to specific hours of a day

I'd like to know if it is possible in mysql to grant select permission to user but only within specific hours of a day. I have created several views and new user and want to let them browse those ...
user avatar
0 votes
1 answer
46 views

Sum of Total per Client

I want to know the total of each product that each customer has bought and I don't know how to add the total per customer. The tables are as follows: CREATE TABLE CLIENTES -- Customers ( CODIGO ...
user avatar
0 votes
1 answer
53 views

How can I improve the run time of a query that groups / display same date data in one row?

I currently have a view that contains about 1k rows of user information. currently i have a query that groups all the same user information by date into one row. Currently this query takes between 20 ...
user avatar
  • 15
1 vote
2 answers
64 views

Lock specific select query until all insert transactions are commited

I have an insert-only table in my database that contains time-series data. When my system starts, I need to populate an in-memory state with the last-month data (each row contains data for one minute) ...
user avatar
  • 125
1 vote
2 answers
46 views

Only SELECT rows from table when two rows from the same table fulfill the condition as a whole under the same ID

Title is a bit weird, but I am not quite sure how to formulate this problem quite right. Maybe this is why I cannot find the solution...? I am currently crawling through a SQLite database which has ...
user avatar
  • 13
1 vote
1 answer
23 views

MySQL - Select query to return all cols but only 1 of each combination of 2 identifying columns

I need to write a mysql query that returns all columns, but only 1 of each set of identifying columns. i.e. I have a table with a primary key on 3 fields (firstName, lastName, effDate). I can have x ...
user avatar
  • 11
2 votes
1 answer
120 views

Compare queries

On what basis should I compare performance of queries? Query 1: select * from dbo.Table1 where [T1_col1] like '%string%' Execution Plan Query 2: select Table1.* from Table1 left join db2.dbo.Temp on ...
user avatar
1 vote
1 answer
42 views

Filter out rows which the text on that current row is contained in the text of the following row

I wonder if it's possible, in a SELECT, to filter out rows which the text on that current row is contained in the text of the following row. For example, image we log "searches" performed by ...
user avatar
  • 599
5 votes
1 answer
633 views

How can I improve the performance of where clause with LIKE %abc?

I had below select statement that turned out very slow (41s) SELECT DISTINCT a.Doc_Resource_ID ,a.Parent_ID ,a.Logical_Path ,a.lvl ,CAST(SUBSTRING(a.Security_Level, 21, ...
user avatar
0 votes
2 answers
85 views

How to get the second highest value per group?

Getting the second highest value from a table has been solved many times, but I'm looking for the second highest value in each group. Given this table: +----+-----+ | A | 10 | | A | 20 | | A | ...
user avatar
  • 455
0 votes
2 answers
159 views

Do select statements start a transaction in auto commit mode?

I knew that update, insert, delete start a transaction in auto commit mode, I would like to ask if select statements make their own transaction also in auto commit mode or there is no need for that ? ...
user avatar
0 votes
0 answers
30 views

Concatenate external variable into Select Statement with no. of TABS based on length of external variable

Could someone please help with this requirement. WHENEVER OSERROR EXIT FAILURE WHENEVER SQLERROR EXIT SQL.SQLCODE FAILURE SET newpage none SET trimspool ON SET feedback OFF verify OFF tab OFF echo OFF ...
user avatar
1 vote
1 answer
129 views

Select/Insert concurrently in Postgresql

I have a table like this `articles` id: int data: timestamp body: varchar for some reason, I want to split this table into 2 separate tables: `articles` id: int data: timestamp `article_body` ...
user avatar
  • 111
0 votes
1 answer
634 views

Strange Error ... Conversion failed when converting the varchar value 'www.com' to data type int

The issue that I am running into is happening on Microsoft SQL Server. I have a view that is unioning together 2 tables. It is returning a little over 7 million records. When I attempt to JOIN ...
user avatar
  • 33
1 vote
1 answer
57 views

Speeding up SELECT FROM a large table

I have a large table (500M rows with ibd size of 1.5TB) CREATE TABLE t1 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, ChildID int(11) unsigned NOT NULL, Number smallint(5) unsigned, Title varchar(255)...
user avatar
  • 4,179
0 votes
0 answers
20 views

MySQL LEFT JOIN, returning data for 0/1 values vs. NULL values [duplicate]

Have this MySQL SELECT query: SELECT product.id, product.name, shop.currency, price.amount, shop.name FROM product LEFT JOIN price ON product.id = price.product_id LEFT JOIN shop ON price.shop_id = ...
user avatar
  • 13
1 vote
2 answers
70 views

How to select the latest continuous set of rows of a certain type?

(Using PostgreSQL) I'm looking to select the latest set of continuous (by date) rows that have a code 'A'. The latest row is always missing the "To Date" as it is assumed to be current. And ...
user avatar
0 votes
3 answers
45 views

Statement of total precipitation for each hour, day or month

I store the data from the rain gauge in mySQL and needed help with listing the data. I save the data every 5 minutes and use the save date in the form of a timestamp. At the end of each hour, ...
user avatar
0 votes
1 answer
43 views

SQL Developer: View only permissions

I am using SQL Developer, generally to assist in writing reports against various databases but there is one application that is run on an annual basis and at the end of that processing (about a 3 hour ...
user avatar
  • 11
0 votes
1 answer
210 views

Automatically materialize CTE? (instead of temporary table CREATE and INSERT)

I’m a novice trying to learn about query optimization and temporary tables in Oracle. The examples I’ve seen for Oracle temporary tables involve CREATE TABLE and INSERT INTO statements. CREATE ...
user avatar
  • 1,375
-1 votes
1 answer
53 views

how to select date time step by 60 second from first value to last value in table

I want to add 60 seconds to the date value from the first date and return the data if it exists in that interval like this datetime value 2021-05-02 12:20:1 5 2021-05-02 12:20:15 8 2021-05-02 12:...
user avatar
1 vote
1 answer
259 views

SELECT inside SELECT in Redshift

select t.col1, t.col2, a.col1, b.somecolumn VQ.a, VQ.b, VQ.e, VQ.d, VQ.f, (select t.status as a, p.id as b, p.permit as c, p.des as d, p.error_code ...
user avatar
1 vote
1 answer
37 views

Selecting related data without selecting foreign key from subquery [closed]

cities id name county_id 1 City1 1 2 City2 1 3 City3 2 4 City4 2 counties id name 1 County1 2 County2 Hello, I would like to select all city names from related county by enetring a city name....
user avatar
  • 13
0 votes
1 answer
278 views

Executing a query with a variable in a loop

I have two select, how can I do the same using a loop and a variable in PL SQL Developer. I have: select 1 as nm from dual; select 2 as nm from dual; I need something similar: DECLARE x NUMBER; BEGIN ...
user avatar
0 votes
1 answer
113 views

How to reuse list of columns in multiple statements

I have a list of columns for each entity I want to get from a SELECT/UPDATE/INSERT statement. I want to reuse the same list instead of having a copy in each query. Example: SELECT col1, col2 FROM ...
user avatar
  • 126
0 votes
1 answer
30 views

Can analytic functions add or remove rows from a resultset?

I'm a novice who is starting to learn about Oracle analytic functions (window functions). I know from experience that it's important to fully understand how SQL mechanisms work when using them in ...
user avatar
  • 1,375
0 votes
2 answers
54 views

Limit and offset (pagination) for rows with related data (with joins)

I have tables Alpha and Beta. Beta belongs to Alpha. create table Alpha ( id int auto_increment primary key ); create table Beta ( id int auto_increment primary key, alphaId ...
user avatar
0 votes
0 answers
26 views

Join 2 tables and show row data in individual columns

I got a peculiar requirement to create a SQL query joining 2 tables as below customer is the first table. This table holds the primary (p) and other (o) customers. The field cust_id creates the link ...
user avatar
0 votes
0 answers
65 views

Oracle 19c Select Query with Database Link

How can I use the following query with database link: set pages 999 col "Size MB" format 999,999,999 col "Objects" format 999,999,999 col OWNER for a25 select obj.owner "Owner&...
user avatar
1 vote
1 answer
27 views

Can I order/group on a column and show a limited number of the results at the top and then show the remainder in a second sort order?

I have a simple table for product data with a column called special which can be 1 or 0, indicating if the product is special and should be highlighted in listings of products that I need to generate. ...
user avatar
  • 113
0 votes
1 answer
61 views

Select Result Different On Float Data Type MariaDB 10.4.8 [duplicate]

On MariaDB I have simple table with float data type CREATE TABLE `testatt` ( `id` int(11) NOT NULL AUTO_INCREMENT, `nilai` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=...
user avatar
  • 47
0 votes
0 answers
77 views

Creating a report with multiple sub-reports, grouped on one field - Microsoft Access 365

I am trying to create an MS Access Report (Microsoft Office 365) with more than 1 subreport, to be grouped under a field called "City". The report is based on a linked table and the sub-...
user avatar
  • 1
1 vote
1 answer
98 views

To trim particular value in a column in Oracle sql

I have a regular expression select statement like below : SELECT REGEXP_REPLACE(nvl(l.text_1, l.text),'^'||21810||'=|\|' ||21810||'=', '|'||21810||'='||'B1') ...
user avatar

1
2 3 4 5
21