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
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
31 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
59 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
74 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
51 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
40 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
38 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
102 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
20 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-...
1
vote
1answer
34 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
96 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
72 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
40 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
50 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
197 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
25 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
96 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
226 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
100 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
30 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
212 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
37 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 server3....
2
votes
2answers
108 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
29 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
47 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 ...
3
votes
2answers
44 views

Get rows with minimum value

I have tables company: company_id | - - - - - - - - - - - - - - 1 | 2 | employee: employee_id | - - - - - - - - - - - - 1 ...
2
votes
1answer
43 views

Join two SELECT results with the same post_id value

I need to do a query to the MySQL database of my site built in WordPress / WooCommerce. The 'wp_posts' table keeps products, articles and more. Every row in 'wp_posts' has a 'post_id' to connect '...
4
votes
2answers
74 views

Indexed but still slow during query

I have a table that stores REST requests and responses: CREATE TABLE ws_call_logs ( id BIGINT NOT NULL, ws_method_id BIGINT NOT NULL, device_id ...
4
votes
2answers
220 views

Same record shows up multiple times in ordered paginated query

Some records show up on multiple result "pages" with the following query: SELECT `description`, `wallpaper` FROM `bbr_bar` WHERE `country_id` = '2510769' ORDER BY `online` DESC ...
3
votes
2answers
132 views

SQLite Like clause match whole word only

I have a SQLite database with a table named: minecraft +----+----------------------+ | id | name | +----+----------------------+ | 1 | Pocket Mine MP | | 2 | Open Computers ...
0
votes
0answers
37 views

Display Complete Month With or Without TIMESTAMP field

Please edit the title if you have a better, I didn't find other words to describe this. Environment Fields: |id|time|text| Situation I have to make a report with the total of records per day, ...
0
votes
1answer
61 views

MySQL: Select two values of two columns and compared their values exchanging them each other

I need to find a way to select one random row where the subscriber is not also leader (in the same or any other row). Something like this: (just to have an idea) SELECT `leader`,`subscriber` FROM `...
2
votes
2answers
92 views

CTE To Use Alternate Select When Select Returns No Rows

I have created a involved CTE, no recursion, with multiple selects being done as it processes the business logic of the requirement. But I have been advised that the logic needed for the scenario ...
2
votes
2answers
123 views

Search multiple values in multiple columns

I have a table results with this fields: id results_id number_one (int) number_two (int) number_three (int) number_four (int) number_five (int) Each number_* column can contain a number from 01 to ...
1
vote
1answer
65 views

Unknown column 'actions_in_group' in 'field list'

I keep getting: Unknown column 'actions_in_group' in 'field list' Here's the SQLFiddle: http://sqlfiddle.com/#!9/4ddf0/19 SELECT *, SUM(actions_in_group) AS total_rows_in_group, GROUP_CONCAT(...
0
votes
2answers
38 views

Combining two similar queries into one (PostgreSQL)

I have 2 queries like this: SELECT c.id FROM component c, base_vee bv WHERE c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199) AND c.id = bv....
0
votes
1answer
297 views

Simple select query taking too long

I have been having a hard time with this query, I have a webpage which is supposed to run this query a dozen of times, and with an execution time up to 40 - 50 seconds per query, you can see how this ...
0
votes
1answer
311 views

Efficient way to get last 10 rows of each group in SQLite, can CTE help?

Consider there is a table of job runs history with the following schema: job_runs ( run_id integer not null, -- identifier of the run job_id integer not null, -- identifier of the job ...
0
votes
0answers
12 views

Selecting entries which have all relations [duplicate]

I have 3 tables: A, B, C. Tables A and C have fields: ID, Name and they are related many-to-many through B table with fields A_ID, C_ID. How can I select the entries from A which have relations to ...
0
votes
0answers
29 views

Need help with Oracle Query for totaling fields with separate clauses, any feedback highly appreciated!

I am stuck on an issue that has been causing my mind to ramble for the last couple of days. DETAILS ABOUT REPORT This report is to identify the following details based off of the CUSTOMER_ID new ...
0
votes
0answers
28 views

How can I combine join and random choice from one table but 2 conditions?

I want to choose 200 entries randomly from a table However, I have two conditions the first 100 should be white and the other black. How can I join them? One possibility to do is to run following ...
-1
votes
1answer
25 views

Setting An Order For Specific Fields Using SELECT and ORDER BY

So I'm writing a query to show the hire_date, job_id and last name where the last name= 'Matos' and 'Taylor and the hire_date is in ascending order. When executing this code: SELECT last_name, ...
0
votes
2answers
87 views

Which row is selected by DISTINCT?

I have a table with products with names in different languages: product-id | lang-no | name Now I want to generate product lists with names in each language. Unfortunately I don't have all names in ...
-4
votes
1answer
59 views

SELECT TOP (or MAX) for each Specific value [closed]

Lets say i have this table ____________________________ | EmployeeID | Sales | |------------|-------------| | 1 | 300 | | 1 | 350 | | 2 | 400 ...
0
votes
0answers
79 views

MySQL Match … Against query very slow

I work on a website running on Propel ORM and I have this query: if(isset($args["QueryText"]) && $args["QueryText"] != "") { $query = $query ->withColumn("(MATCH (Request.Subject, ...
-1
votes
3answers
64 views

Using the name of an “AS” column in the WHERE clause

I have the following query in mysql: SELECT voter_id, first, middle, last, title, party, phone, ward, households.hh_id, households.hh_long, voters.1311 + voters.1111 + voters.0911 + voters.0909 + ...
0
votes
1answer
38 views

Update rows in a table with an index column starting from 0 with consecutive numbers

I have a few tables in a Oracle db with some "rule": The INDEX_COLUMN must start from 0 and must be consecutive numbers and the column NRCRT must start from 1 and also consecutive numbers. I've made a ...
1
vote
0answers
52 views

Matching same unknown characters in two columns

So here is the situation. I have a database with values in columns that look something like this field_0_data1, field_0_data2, field_1_data1, field_1_data2 Is it possible to only select the columns ...