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

-3
votes
2answers
41 views

Select in select [on hold]

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
29 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
47 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
45 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 | ...
1
vote
1answer
43 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
25 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
13 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
72 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
10 views

Select statement to extract range of hours from Time String [migrated]

I have a PostgreSQL table like this. city_location | isp | date_ | time_ ---------------+------------------------+------------+-------------- Cambridge | Akamai ...
0
votes
0answers
39 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
45 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
110 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
33 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
70 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
53 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
58 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
12 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
39 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
74 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
40 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
268 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
28 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
45 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
43 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
49 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
35 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
33 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 ...
1
vote
2answers
97 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
15 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 ...
1
vote
1answer
33 views

Is it possible to write a select * view that includes new columns added to table?

I would like to write a view that is essentially something simple select a + b, * from table_name and will always truly include all columns in table_name, even when I add or remove them. If I do the ...
0
votes
0answers
50 views

Select query keep showing “loading” message in phpMyAdmin

This query works fine: select * from prospect p JOIN prospect_company c ON p.prospect_id=c.prospect_id JOIN prospect_address a ON a.prospect_id=c.prospect_id; However, the following query keeps on ...
1
vote
1answer
69 views

SELECT query types and optimization

I'm using a database monitoring system that, amongst other data, shows the following values for SELECT queries: number of joins that use ranges on the first table number of joins that do a full scan ...
1
vote
1answer
31 views

Error 1052 Field is Ambiguous using Inner Join Function

Error '1052' Field is ambiguous. I understand the error that is happening, and I've looked at several places for the fix and then applied the inner join function in attempt to fix it. It's still not ...
0
votes
1answer
39 views

Select and update specific mysql data in single query

I am trying to first select data from a row which contains certain characters. I would then like to update and append new data into another row of those items that were selected. This is my current ...
2
votes
2answers
188 views

How do I set the isolation level for SELECT statement?

Using PostgreSQL 9.5 I would like to make sure that when I run a SELECT statement I get consistent results, i.e. if the table is modified while the SELECT is running, I want to get the state that was ...
1
vote
2answers
23 views

How to select distinct record?

I have a table, as follows: ID | Name | MyID 1 | Alan | 2 2 | Alan | 2 3 | John | 3 I want to select the records as follows: If two records have the duplicate ...
3
votes
2answers
78 views

MySQL - Order by closest value on multiple columns

I have a table for a game lobby. In this lobby a user gets put in their connection and their level. In my SQL statement I am getting all the users in the lobby which are between +5/-5 of the user's ...
1
vote
1answer
144 views

Vertica performance on select from view with union

We have a huge number of rows(tens of millions), and for each table we have an associated view that is a select with UNION ALL between that table and several others. Now, we are doing a select from ...
3
votes
3answers
88 views

SELECT + INSERT or INSERT alone

I have an application where several devices send data every second to my server. On the database I have a table with the devices and another table with the data sent every second. The first time a ...
3
votes
2answers
29 views

Howto (async?) preview intermediate resultset

Is it possible to retrieve parts of the resultset from SQL Server queries in advance? For instance if I run a query that will return 20,000 rows, is it possible to throw the results in packages of ...
2
votes
1answer
120 views

How to get number of active users with one SQL-Query?

I am running a site similar to craigslist where users can create classifieds and write/reply messages. In order to get an idea of activity I would like to get the amount of active users per month. ...
1
vote
0answers
40 views

Fetch items where related rows has different values

I have table in MySQL called attributes, and other table, attribute_values, one attribute can have multiple values, values identified by context attribute and has value_c attribute wich contain value. ...
1
vote
1answer
35 views

Cities and States (a few data): single table or splitted?

I am designing a DB schema and I need to store cities and states. I expect only a few rows, let's say, 20 cities. Example: id | city | state 1 | Rio de Janeiro | RJ 2 | Niteroi ...
1
vote
1answer
42 views

How to select data from differents servers with a single query?

I have a challenge! I need select from 4 different tables from 4 different servers. Example: Select * from server1.tableS1.sys.view Select * from server2.tableS2.sys.view Select * from ...
2
votes
2answers
105 views

Select random rows up to a limit

SELECT * FROM my_players WHERE player_type = 'IF' ORDER BY cardweight / rand() DESC limit 3 That is my current query, What I am trying to do is select random rows for which I have the * in my query, ...
1
vote
1answer
27 views

SELECT with equal chance between same IDs - MySQL

How can I SELECT data with equal chances for same IDs in MySQL (for php). For example, assume below data structure: id value 1 alfa 1 beta 1 gamma 2 hello 2 bye 3 sky I want ...
0
votes
1answer
32 views

How can I access certain information from YQL?

I notice the YQL commands such as select * from yahoo.finance.quote where symbol in ("YHOO") return all the data for that certain stock. I was wondering is it possible to access data from YQL ...