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.

learn more… | top users | synonyms

0
votes
0answers
8 views

How do display the vertical format like MYSQL in the console, but for H2 Database?

I am administering a H2 database and I'm coming from the mysql world. I usually use the console to do quick queries in the mysql console. When there is alot of data returned, I use the \G option for ...
-1
votes
0answers
25 views

SQL WHERE statements by Row Number

I have a Data Loader that will only let me upsert 1000 at a time. I have a DBF File that has about 5000+ records. I have a section where I can make WHERE statements for that particular upsert. ...
0
votes
2answers
37 views

Check if there are similar rows in a table

As a Rookie in SQL I have a problem creating a query. Here is the problem : I would like to check on a table if there are lines that share the same DATES. For example I would like to know if in ...
29
votes
3answers
5k views

Why does this query work?

I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c. Why does this query not return an exception? select * from table_a where name in (select name from table_b); From ...
1
vote
0answers
22 views

SELECT UNION from multiple tables with same schema in MySQL

I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use: SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION SELECT ...
3
votes
1answer
107 views

Sorting Order by GETSORTED

In my sybase 12, when I look at the showplan of a select distinct query, I see GETSORTED in the showplan's output. I've searched through the entire internet (YES!) and I can not seem to find a link ...
1
vote
1answer
71 views

How to use a SELECT query inside an ON CLAUSE in a LEFT OUTER JOIN in MySQL

I need to retrieve data from several tables. For now I am getting maximum of them from SELECT disc.id, users.user_login as 'last_active_user', disc.discussion_sub, list....
0
votes
2answers
72 views

Convert Function

I am working on converting a few dates to make them easier to read and when I call upon the convert function to do so, they simply don't convert. When I run the queries they look the same. The code is ...
3
votes
1answer
53 views

Self-JOIN with occurrence count

I use PostgreSQL 9.4 and I have a table called certificates containing the following columns: CREATE TABLE certificates ( id serial PRIMARY KEY, common_name varchar(255) NOT NULL, state ...
0
votes
1answer
62 views

Select distinct values of two columns as single [duplicate]

For starters: yes, I've seen there are lots of questions on combining two/multiple columns - but so far I neither found the answer to my question nor something to get me started trying further (which ...
0
votes
1answer
50 views

How to SELECT cells from mysql for a HTML table? [duplicate]

I have data for individual cells of a HTML (or excel) table as CREATE TABLE table_cols ( column_id smallint(5) unsigned NOT NULL AUTO_INCREMENT, column_number tinyint(3) unsigned, title varchar(255), ...
1
vote
1answer
60 views

SQL Server query problem when selecting data from child table based on column in parent table

I am using MS SQL Server 2008 using Management Studio. I am running a query(below) to extract dates and value based on the 'ticker' column from the parent table, the attribute column (child) , and ...
0
votes
2answers
49 views

Get row with maximum in one column [closed]

The task: What ingredients has the maximum amount of content in Pancake? The tables: Recipe (Name, group) Ingredient (Iname, price) Composition (Name, Iname, Amount) I tried this query: Select ...
1
vote
1answer
38 views

Increment fixed value in select clause

How might one increment a "fixed" value in the SELECT clause of a query, so that each row has a different value set? For an obviously not working example that illustrates the intention: SELECT ...
0
votes
0answers
50 views

Is there a clean way to extract data from this single-column value?

I'm attempting to extract data from a single MySQL column which contains data that looks like this: a:6:{s:15:"info_buyRequest"; a:3:{s:7:"product"; s:1:"1"; s:3:"qty"; ...
3
votes
2answers
33 views

Select only rows with at least 2 non-missing values

I'm pretty new to MySQL (used to programming in R/Stata), and getting stuck on this - I'm probably going about it the wrong way. Any pointers would be appreciated! I have a large database (a couple ...
1
vote
0answers
30 views

Fetching equal records of all options MySQL [closed]

there are 4 columns which I need in conditions. age, city, accommodation, sex I want to export total number of 1000 records in the following criteria: I choose ages between 31-35 years old with both ...
-1
votes
1answer
31 views

select columns if a column has a value [closed]

I have RegiNum and EmpNum columns in users table. If RegiNum column has a value it's considered as a student. If RegiNum is filled, I want to select Name and ID from that table. I used select if(...
-1
votes
1answer
26 views

Select MySQL with 2 tables conditions, Is this right?

Tables: agencia (nome_agencia, cidade_agencia, fundos) cliente (nome_cliente, rua_cliente, cidade_cliente) conta (nome_agencia, numero_conta, saldo) emprestimo (nome_agencia, numero_emprestimo, ...
-3
votes
2answers
50 views

Select in select [closed]

I am doing some test on the internet as preparation for my test tomorrow. I would like to know what I am doing wrong here. Thanks a lot!
1
vote
1answer
32 views

ORACLE: Changing the grouping of a data to be based on month breakdowns

To keep this short and to the point, I am looking at modifying the grouping for this SQL query to go from NAME to Month/yy Here is a example of the script, and screenshots of the output file This is ...
0
votes
2answers
62 views

INSERT OR REPLACE INTO + SELECT as source in Oracle

I have two tables A and B. I want to insert B's content into table A. The issue is there's a row in B that already exists in A. I've searched and I found some answers talking about MERGE, but no ...
0
votes
3answers
50 views

select two record containing dates in it with two input dates

Simply I have Two Input box Start_date End_date This is my database table | id | category | start_date | end_date | | 1 | gold | 2016-05-24 | 2016-05-31 | | 2 | silver | 2016-05-...
1
vote
1answer
46 views

(MYSQL) Select union of rows depending on row before?

I have a table in MySQL that looks like this: CREATE TABLE IF NOT EXISTS `mytable` ( `id` int(11) NOT NULL, `data` VARCHAR(255), `otherid` int(11), ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT ...
1
vote
1answer
31 views

Get previous and next row from result set help

I have to get "relevant" results from a table by joining it on another table and filtering the results on parent id and current id, this all goes fine until your current id is either the first or the ...
0
votes
0answers
25 views

OFFSET in Transbase

I am working with Transbase DB and stuck on selection of specific range of rows. In MySQL I can do it as folows: SELECT * FROM table LIMIT 2,3; I know about FIRST() in Transbase, but how about ...
0
votes
1answer
97 views

SQL query to count number of payments made in a time period with data from two different table

Could someone please tell me how to write sql query for the following question? I am not able to get how to write the condition where it selects those user_id which is present on the said date and ...
0
votes
0answers
43 views

How to give a prefix to all columns of a JOINed table in MySQL?

I have a data schema like this: car id type -> ENUM('Tesla', 'AstonMartin', 'Bmw') common_attribute car_tesla car_id specific_attribute_a specific_attribute_b ...
2
votes
1answer
49 views

How to JOIN a table with a name that equals to a determined value in MySQL?

The model of the (PHP & MySQL based) Zend Framework 2 application I’m currently working on contains a inheritance similar to the one described here. To map this to the database I’m using the ...
2
votes
3answers
112 views

Poor performance of JOIN, WHERE, ORDER BY combination (MySQL)

Goal: select 10 most recent rows from posts which are related to a specific user. There are two tables: posts (~5,000,000 rows) and relations (~8,000 rows). posts columns: --------------------------...
0
votes
2answers
38 views

Using Select Statement to get column name and table name from the same table

I have a table containing 2 Columns : Column1 (table_name) and column2 (column_name) like this : (Column 1) (Column 2) |Column_Name | Table_Name | -------------|------------| |...
0
votes
0answers
48 views

InnoDB: why “non-locking” SELECT still gets locked?

This issue can be related to my first question: InnoDB: very slow random updates, hanging in “updating” state But i think it needs to be clarified in a separate thread. The Problem: a simple read ...
0
votes
1answer
27 views

Expand a range columns into a single column

I'm thinking this is an easy query but I don't know how to start. I have a database table called Booklet with columns id, controlNoFrom and controlNoTo. Sample data looked like this: id | ...
0
votes
4answers
99 views

How can I use MySQL variables in subqueries?

I'm trying to run the following query, but it keeps getting "reduced" and failing. Cannot get MySQL to provide any information as to why. Here's the query: SELECT * FROM animals WHERE name IN(SELECT ...
1
vote
1answer
61 views

MySQL INSERT … SELECT Performance

I know that this query will lock table2: UPDATE table1... SELECT .. FROM table2 How about this query ? INSERT INTO table1... SELECT .. FROM table2 Does this query also create a lock on table2?
0
votes
1answer
61 views

Subquery returns more than 1 row

I'm executing a query to return the products of a specific sale in Magento database. It's working fine for a sale that contains only one product, however, I'm having Subquery returns more than 1 row ...
0
votes
1answer
14 views

select duplicated rows by modified_date - PGSQL 9.2

Query: SELECT j.id INTO junk.ja_jobs FROM ( SELECT time_job, recurrenceid, clientid, creatortype, deleted, MIN(id) AS id FROM ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND ...
0
votes
2answers
33 views

MySQL - An UPDATE from a SELECT

The more I learn about about the great things you can do with mysql, the more I keep pushing to learn. I have two tables: Tests TestNumber (int primary key) InactiveTestSlotBitwise (int) ...
0
votes
1answer
43 views

INSERT and UPDATE in Trigger

I have created a trigger so I could insert every row in a backup table CREATE OR REPLACE TRIGGER xyz AFTER INSERT ON table_1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO table_2 (...
3
votes
2answers
80 views

Inserting rows with WHERE condition

Structure 1 table named: scan 2 columns named: id, and date Problem I have two working queries, a SELECT and then an INSERT which I would like to merge together creating a INSERT where the ...
1
vote
1answer
48 views

Is there a way to find the partition or subpartition name where my ID belongs

I know if I need to select from any partition I need to provide partition name SELECT * FROM TABLE PARTITION(P1); What if I need to know which partition a particular value belongs using a sql query ...
3
votes
3answers
269 views

Listing rows with a WHERE condition

I am struggling with a SQL query that lists hosts that are offline. Problem The problem occurs when I need to list hosts that don't have any ports open (status = 0), so looking at the table at the ...
0
votes
1answer
30 views

Oracle : Is my DISTINCT function working properly?

I have alphanumeric values as my primary key. I am querying my database in two different ways and finding different results. It is almost certain that I might be missing some key point in my queries. ...
1
vote
2answers
50 views

Counting rows from a subquery

Simple: I would like to count the number of rows from the sub-query. Note that status is whether the host is online or not. Bad code SELECT COUNT(ip_address) FROM `ports` ( SELECT DISTINCT ...
0
votes
2answers
63 views

MySQL SELECT n-th row between dates (time interval)

I have a server which records various sensor data each second and inserts it into a MySQL table. To view the sensor data on a dashboard I have a chart which also gets updated with live data each ...
1
vote
1answer
50 views

SQL query returning cartesian and duplicates and not single records

So I have four tables as follows: CREATE TABLE softdrinks ( drinkID INT NOT NULL AUTO_INCREMENT, drinkName VARCHAR(100), PRIMARY KEY (drinkID) ); CREATE TABLE categories ( categoryID INT ...
1
vote
2answers
38 views

SQLPLUS: SELECT INTO prompting for variable value

Any idea why this is prompting me for the value? I'm calling the script via batch file: DECLARE precount NUMBER; nowcount NUMBER; BEGIN SELECT COUNT(*) INTO precount FROM ...
-1
votes
1answer
36 views

Select and Insert for all data - Need help querying [closed]

At a first glance, this is my query INSERT INTO T2 SELECT T1.*, T3.DATETIME, T3.STATEID FROM T1 WHERE T3.DATETIME BETWEEN '01-NOV-15' AND SYSDATE LEFT OUTER JOIN T3 ON (T1....
1
vote
2answers
99 views

how do I use a value from the select in a joined subquery?

I'm trying to avoid a stored procedure or bringing it into code but they may be my only alternatives. Here's my sql. Does anyone know any SQL voodoo that could make this happen without a stored ...
0
votes
1answer
19 views

Extract date and time separately from a time with time zone column [closed]

I have a table in PostgreSQL 9.3 with the following column ...., created_at time with time zone NOT NULL DEFAULT now(), ..., When I do a select created_at from trails; I get this "02:32:44.62054-...