Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

1
vote
0answers
4 views

Relation Algebra: Selection of Cartesian Product

I am learning Relational Algebra basics for sql. I came across an example where the selection of cartesian product of two relations (R1 x R2) has been taken and I realize that it is same as selection(...
0
votes
2answers
56 views

why optimizer in Oracle database does not use index?

when I execute following queries see completely different result where is problem? here I execute a simple select query which takes a few seconds to complete: SELECT * FROM PRODUCTION....
1
vote
1answer
13 views

Finding average of multiple delta times

I'm looking to find the average interval between successive rows across the whole query - delta-previous in below example: SELECT *, "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER ...
2
votes
6answers
71 views

SELECT all rows with sequential id after a specific id

Is it possible to query all rows that their ids are sequential after a specific id? (id is an AUTO_INCREMENT column.) For example, suppose my specific id is 5. Then I want all rows with these ids: ...
0
votes
1answer
12 views

select query on postgis not working for specific fields [closed]

So I have what seems to be a really weird problem. I have imported a table from QGIS into a postgresql + postgis database. I simply want to select a few columns from that, but I can't. whats funny ...
-1
votes
1answer
49 views

Why the error: “The column doesn't exist”? [closed]

I'm using postgresql 9.4 and I'm trying to run this: SELECT a."CI_PROFESOR", a."NOMBRE_PROFESOR", a."OFICINA_PROFESOR", c."CODIGO_RECURSO", c."DESCRIPCION_RECURSO", ...
1
vote
0answers
24 views

InfluxDB - How to get start timestamp of alternating series

I have these data series in an influx database, alternating between 0 and 1. 2018-10-02T07:12:00Z 0 2018-10-02T07:15:00Z 0 2018-10-02T07:18:00Z 0 2018-10-02T07:21:00Z 0 2018-10-02T07:24:00Z 0 2018-10-...
0
votes
0answers
32 views

Find the number of unread messages

Below is the stored procedure I use to build an overview of a given user's conversations. A conversation is made up of a bunch of chat messages that reference the user as either the sender or the ...
0
votes
1answer
41 views

MySQL: Trying to “normalize” data using MySQL to help

I inherited a spreadsheet with 1600+ records, which I simply inserted into a single table (mapping) in MySQL. I'd like to use the power of MySQL to help me do some "normalization", à la the MySQL ...
2
votes
0answers
19 views

How to select all results tied for first position

My current output is a list of caretakers and how many budgies they take care of. I get it via the following SELECT statement: SELECT caretakers.name, COUNT(species.name) FROM caretakers JOIN ...
0
votes
1answer
23 views

What is the best solution to prevent picking the same value (by multiple requests)?

Situation: there is a table "Tours" which contains a lot of generated records with different properties (Color, Priority, PickedUp). The goal is to allow external application (over REST API) to pick ...
0
votes
1answer
18 views

Return proper column based on other column's values

Consider an EAV table with separate columns for each datatype: CREATE TABLE eav_values ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, entity_id INT NOT NULL, name VARCHAR(127) NOT NULL, ...
0
votes
2answers
36 views

How to SELECT multi-columns from a column?

I have a table with the structure book_id int(11), section_id int(11), note_id int(11), item_number int(11), text varchar(255), UNIQUE(book_id,section_id,note_id,item_number) item_number can be 1 or ...
1
vote
1answer
29 views

Query to partially summarize data in a table using SQL

I'm learning how to handle databases, so this is a trivial question, and quite likely has been answered already. I'm just not familiar to the lingo and have no idea how to search for it (although I ...
0
votes
2answers
42 views

MySQL 5.1 - long running select locking the table and not allowing inserts

Platform - MySQL 5.1, MyISAM engine We reached max on int (id, auto increment) in our Prod system. Changed the type to BigInt. Even since this change was made, long running selects are locking the ...
1
vote
0answers
52 views

Why does the Select statement stalls when executed directly in sequence?

When executing the following in workbench or in code, the Drop table/Create Table works ok. The Select statement stalls when executed in one go. If the table is MyIsam, it works ok. If executed [...
0
votes
1answer
22 views

insert with multiple row return through select [closed]

I want to insert multiple rows return from select statement with different uid, lets select statement return (1,2,4,5); INSERT INTO `notifications`( `Text`, `uid`, `read_status`) VALUES ("abc",(...
1
vote
2answers
60 views

Update selected data from same table column as currency

I am trying to update selected data from a table column and format this data as currency. My table column holds strings that have both mixed whole numbers and already formatted data. This was due to ...
0
votes
1answer
67 views

mysql update based on select result, How combine?

Here are two SQL statements. One is an select statement and the other is a update statement. I want to combine them. Select SELECT * FROM test WHERE `id`='1' Update UPDATE test SET `select_count`=...
1
vote
0answers
17 views

MS Access 2016 M:N relationships return multiple columns instead of multiple rows from a JOIN

Let's assume a data model like this: Books could have multiple authors as well as authors could have written multiple books. If a book and its authors are selected by the following statement (this ...
1
vote
3answers
45 views

Show rows with non-unique value in one field

I'm familiar with how to aggregate rows as shown in this answer: Show only duplicate values I'm also familiar with how to filter the aggregated results using a HAVING clause. What I can't seem to ...
0
votes
1answer
38 views

SSRS - select where parameter and fixed value

So there is a lot of stuff about using SSRS parameters to select multiple values but nothing about using both a parameter and a fixed value using the in select statement. So what I'm trying to do is ...
0
votes
1answer
64 views

JOIN and GROUP BY with additional columns

If I run the following query on my database I get the results pasted below: SELECT t.id as ID, j.text as Text, l.location as Location, FROM [text] t WITH (nolock) INNER JOIN Text_Location tl WITH (...
3
votes
1answer
66 views

Select where number range does not overlap

I have two tables that contain records about road construction activites: table_a is the master list. table_b is a legacy list. For each road, in each year, I want to select the records from table_b ...
-1
votes
1answer
225 views

how to select query to inner join same column twice?

Table A: Col1 [TicketSubject] Col2 [Createuserid] Col3 [Modifyuserid] Table B: Col1 [ID] Col2 [Name] How I imagine it would work SELECT a.ticketsubject ,a.createuserid ,a.modifyuserid ...
0
votes
3answers
30 views

Difference of two columns with possible NULL cells

I need to sort a table by the difference of two columns as SELECT (first - second) AS dif FROM table1 ORDER BY dif DESC but the two columns can be NULL. In this case, it returns the error: ...
2
votes
2answers
335 views

Error 1242: Sub query returns more than 1 row and problem with repeating an if statement for all rows

So I've just started learning MySQL with a few exercises and I'm currently stuck at these 2 problems. Since this is technically an exercise I'd really appreciate hints instead of full solutions. Here ...
2
votes
3answers
76 views

Select the greatest value from each column, even if one of the values is null

I have a road_age_test table: create table road_age_test ( surface_year int, base_year int ); insert into road_age_test (surface_year, base_year) values (10,20); insert into road_age_test (...
0
votes
0answers
43 views

sql server - xQuery Select for each element that exists

I have a bit of trouble querying XML columns. This is part of the query I'm trying to use SELECT pof.pofdata.value('InformacionActo[1]/Vehiculos[1]/Vehiculo[1]/@Marca', 'varchar(max)') AS 'marca', ...
2
votes
2answers
57 views

Difference in query execution plan between MySQL 5.5.54 and mariadb 5.5.56

I am executing a SELECT on 2 Hosts, "old db" running mysql 5.5.54 and "new db" running mariadb 5.5.56. Both the runtime and the EXPLAIN show a difference, with the mariadb being significantly slower. ...
1
vote
0answers
36 views

How to apply a condition on “count over partition”?

I'm using Oracle 11g. Assuming multiple consecutive entries (sorted by datetime) with the same place_id represent a single visit. With this table: datetime_id | user_id | place_id and I want to ...
0
votes
1answer
47 views

Is there a way in sqlite3 to set .width of all columns to be equal to the width of the column header?

I am using: SQLite version 3.21.0 I look for away to prettify my SQLite SELECT shell output (to make it more human readable, in essence). This answer helped me a lot. Though, now I am wondering if ...
1
vote
1answer
16 views

Mysql query for get repeated customers list

I have a two tables oc_order and oc_customer. I want to get customers who order more than once. This is sample: oc_order: order_id | cus_id | cus_name ------------------------------- 1 | 1 ...
0
votes
2answers
43 views

What is the F301: the CORRESPONDING clause in query expression?

The SQL 2011 Spec supports a CORRESPONDING clause, Feature F301, “CORRESPONDING in query expressions”: What is this feature? How is it used? And, is it supported by any industry RDBMs?
0
votes
3answers
45 views

How to select two fields from a table using join with another table

I have a books database. CREATE TABLE `books` ( `book_id` int(11) NOT NULL, `book_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `book_subject` int(11) DEFAULT NULL COMMENT 'What genre ...
0
votes
3answers
99 views

SQL Count function returning inaccurate results

I am trying to write a query in oracle to list the number of records contained in a single table filtered by multiple columns in the tablespace. Here is the query syntax I have come up with: SELECT ...
0
votes
1answer
137 views

How to select recursively in a child parent design situation (in MySQL)?

Let's take into consideration the following tables: CREATE TABLE actions ( id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, user_id BIGINT(20) ...
3
votes
2answers
95 views

Is there any risk in SELECT … FOR UPDATE outside transaction scope in MySQL?

In a project where I work the sql for selecting objects does always select for update, whether the context is a transaction or not. My question is if it has any risk, or performance issue, or chance ...
4
votes
5answers
103 views

Select same records that only have different values for a particular column

Below is an example of my customer table. There some records having multiple values in BIRTHDAY DATE (by mistake or so). I only want to select those records that have same values for LASTNAME, ...
0
votes
1answer
63 views

Is it ever useful to SELECT DISTINCT on the primary key column(s)?

From my understanding, primary keys are unique by definition. If that's true, would one ever want to SELECT DISTINCT on the primary key? Is this ever practical/useful?
0
votes
1answer
35 views

Indexes for select performance

I have a table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` enum('new','paid') NOT NULL DEFAULT 'new', `createdAt` datetime NOT NULL, `updatedAt` datetime NOT NULL, `...
0
votes
1answer
28 views

How to left join intermediate result table after filter ta(subquery)?

+---------+----------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table ...
0
votes
0answers
20 views

Finding rows affected by deadlock on page locks [duplicate]

We've got a db that has been pretty much deadlock free for over 6 months, but a day or two ago we had over 60, all on the same two or three pages of the same table (~12,000,000 rows, ~250 bytes per ...
1
vote
2answers
83 views

MYSQL: Values of a column where two other columns have same value

I have a table say table1 with three columns lets name them col1 col2 and col3. I have a suspicion that col2 and col3 have some values that are identical i.e. to say there are some rows where col2 = ...
1
vote
2answers
28 views

Not able to make a query that give me the result that I want in MySQL with 4 tables junction [closed]

I need help to adjust my query to be able to have the result show in bottom. This is my DB illustration: And this is the command CREATE TABLE for each table: CREATE TABLE `produits` ( `sku` ...
0
votes
2answers
48 views

Is it possible to make a SELECT wait until an ongoing UPDATE finishes?

Suppose that I am implementing a bank account logic using PostgreSQL. At some point, I will want to check if the user has sufficient balance to perform an operation. However, the user's balance might ...
0
votes
1answer
21 views

MySQL query with inline selects is very slow

I created a long MySQL query with inline selects. SELECT users.*, (SELECT plan_payments.month FROM user_plans LEFT JOIN plan_payments ON (plan_payments.id = (SELECT id FROM plan_payments WHERE ...
-1
votes
2answers
73 views

How to SELECT value Where value = ''another SELECT" [closed]

First of all I searched for correct answer but I didn't find. I have a function: SELECT user_email FROM `registracija_complete` WHERE user_id = (SELECT `id`  FROM `base`  ...
0
votes
3answers
99 views

Calculate percent of roads (by length) where condition is >=15

I have a road condition table: CONDITION LENGTH 2 5 4 10 6 15 8 20 10 25 12 30 14 ...
0
votes
1answer
36 views

How to select two columns from two different tables using foreign keys from another third table?

I am using MYSQL and here is the schema that I have: Here is my first table user +-----+------------------+ | id | name | +-----+------------------+ | 101 | Daniel Radcliffe | | 102 | ...