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
votes
2answers
30 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 | ...
0
votes
2answers
75 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 ? ...
-3
votes
0answers
42 views

group by and output based on count

Based on the following business determination that ONE 'exit' comprises (MIN time of entry and MAX time of exit), (meaning the cardholder has exited the facility). In reality there can be many '...
-2
votes
0answers
34 views

How to write in a column what columns have changed from query result

Thanks to another answer I was able to group all distinct values for the same users in a table. sample data below: CREATE TABLE tableName ( NAME varchar(300), HOME ...
0
votes
0answers
25 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 ...
1
vote
1answer
51 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` ...
0
votes
1answer
80 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 ...
1
vote
1answer
51 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)...
0
votes
0answers
17 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 = ...
1
vote
2answers
50 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 ...
0
votes
3answers
40 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, ...
0
votes
1answer
14 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 ...
0
votes
1answer
69 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 ...
-1
votes
1answer
47 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:...
1
vote
1answer
84 views

Select inside select Postgres

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 ...
0
votes
1answer
30 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....
0
votes
1answer
100 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 ...
0
votes
1answer
37 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 ...
0
votes
1answer
27 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 ...
0
votes
2answers
47 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 ...
0
votes
0answers
24 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 ...
0
votes
0answers
44 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&...
1
vote
1answer
23 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. ...
0
votes
1answer
27 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=...
0
votes
0answers
23 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-...
1
vote
1answer
39 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') ...
0
votes
1answer
13 views

MariaDB procedure, from a previous select create another select and return both

I've this tables in my database, and I'm trying to select all the career info, including the specializations it has. CREATE OR REPLACE TABLE School_Catalog ( school_id INT UNSIGNED PRIMARY KEY NOT ...
1
vote
2answers
42 views

Track SELECT statements on specific tables

I'm searching for a way to keep track of any SELECT statements on a specific table in a database where logs are not enabled. I can't find any solutions to this except to force people to use a stored ...
7
votes
3answers
976 views

Recommended way: "SELECT *" vs "SELECT COLUMN_LIST" from same table, multiple times

This question is very much near to what I wanted to ask, but both question and answers are focusing more on selecting random rows. Since there is general rule that, "SELECT COLUMN_LIST" is ...
0
votes
1answer
36 views

Add a list of names to a generated series

I generated a date series this way: SELECT generate_series(date '2020-01-01', date '2021-01-21', '1 day'); Additionally I have a list with name (Max Meyer, Anna Smith, Peter Gardner). How could I add ...
0
votes
0answers
25 views

How to find the three closest distinct units for each of my sites using SQL

I am trying to locate the three closest distinct units for each of my sites. I have my measured distances as total_mile, the starting locations are from_site_, and the end locations are to_unitcod. ...
0
votes
1answer
28 views

How to use multiple SELECTs within a single INSERT? [closed]

I'm currently working with PostgresSQL 13.3 and I thought I could have a file with something like this: INSERT INTO points (zone_id, element_id, position_id) VALUES (SELECT zones._id, elements._id,...
0
votes
0answers
13 views

Average amount of orders per hour in the last X days per merchant

I have the following table named Orders: MerchantID (INT) OrderID (VARCHAR2) OrderAmount (INT) CreatedDate (DATE/TIME) I need to create a query that returns the average amount of orders per hour in ...
0
votes
3answers
31 views

How to return a number surrounded with parenthesis as a negative number

I have a table CREATE TABLE tab1 (col1 integer, col2 character varying(20)); Col2 contains numeric value but the negative values corresponds with parenthesis not and minus (-). This is the sole ...
0
votes
1answer
118 views

Selecting all columns from row with specific max value [duplicate]

Imagine that I have the following table: ItemCode WhsCode MinStock MaxStock AvgPrice 123 01 1 4 600 123 02 0 1 200 512 01 0 2 100 489 01 0 6 50 489 02 2 7 72 489 03 5 10 80 489 04 1 4 65 258 ...
0
votes
1answer
56 views

MySQL: Reason for Degraded performance of a single inner join

We have two tables in our MYSQL 5.7 Aurora database: CUSTOMER_ORDER and BATCH. Customer order can have only one batch associated and it is not mandatory to have one. Create table statement of ...
0
votes
0answers
36 views

Privileges to select any table in Oracle

I'm using a Oracle database client application that connects to 1521 port to do "select" commands over every table in a Oracle db 12.1. I told dba to create this role with these commands: ...
1
vote
1answer
99 views

Select with fallback and default?

Can the following code be converted to database queries? In our application we have a dictionary, where each value is given by in a table like: CREATE TABLE dictionary ( id serial NOT NULL, ...
0
votes
1answer
33 views

Selecting attributes from multiple tables via foreign key

Apologies if this question has already been answered. Couldn't find the answer myself. I'm trying to work out the SQL to extract data from one table which is being referenced from another table. My ...
0
votes
1answer
46 views

need the rows with a unique value on certain columns but date column doesn't help?

I have a table with multiple records by each employee. I need to find all the records by employee where any of the columns values changes. For example, I have the following data for George: CREATE ...
0
votes
2answers
247 views

PostgreSQL: Can i use tuple in where clause?

Actually might be a simple question: Given 2 tables: table1: A,B,C (A1,B1,28471), (A1,B2,01244), (A2,B1,1283a), (A2,B2,82r7e); table2: A,B,D,E,F,G (A1,B1,18,1,6,8), (A2,B2,18,2,3,0), (A3,B1,18,7,1,4),...
0
votes
1answer
30 views

SELECT only the rows with a unique value on certain columns [closed]

I have a table with multiple records by each employee, i need to find all the records by employee where any of the columns values changes. for example i have the following data for George: HOME ...
0
votes
1answer
32 views

how can improve this query in big mysql database

my query is simple but 10 seconds long to respond. please check and help me to improve that SELECT COUNT(test.code) AS sumid FROM test WHERE test.state = '03' AND test.type = '0' ...
0
votes
0answers
61 views

extract pattern from text in SQlite

I have the column that contains text and number how I can select/extract/return only the pattern nnnn.nn.nnnnn or null if there is no match from the column?
0
votes
0answers
22 views

Apply same SQL SELECT query to different columns

If this question turns out to be very basic, I will delete it. I have an SQLite table with the following schema: sqlite> .schema complexity CREATE TABLE complexity ( plan_id TEXT NOT NULL, arc ...
0
votes
1answer
198 views

Can I GRANT SELECT on all schemas in a database?

I need to create role users of which can only read (select) data. I can do it for a particular schema. But I need it to work with all existing and future schemas (existing at least). How can I do it? ...
1
vote
0answers
47 views

Many to many relationship, select rows based on values in list

I want to select group_runs based on a list of group_runners.player and I'm having issue getting rid of results that don't have all the values on that list. In this case group_runs with id = 2 shouldn'...
0
votes
1answer
20 views

Checking if entities match an id in on relation where it could show up in either of two columns

The problem I'm trying to make a page for a website (with PostgreSQL database) that allows the owner of a group to add new members from their friends list. Given the owner_id of a Group its group_id I ...
1
vote
1answer
102 views

Selecting data from a mapping table using a foreign key

I'm trying to make a simple Login sys where each user should be shown data from a list depending on their user ID Using MS SQL. After searching for how to do it best i came across Mapping (pretty much ...
0
votes
2answers
46 views

Add a column with name as a result from a SELECT

I'm using PostgreSQL on my project and I have a Spring Boot Application that uses Flyway to create everything on my Database. I need to create a column that its type is based in a row on other table. ...

1
2 3 4 5
20