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
114 views

I want to rewrite my MySQL query so that FIND_IN_SET doesn't do a full table scan unless necessary

I want to rewrite my MySQL query so that the last table join doesn't do a full table scan unless necessary. Here's what I mean. Under certain conditions I don't need to select any rows in that join ...
0
votes
1answer
46 views

on every row on SELECT Query must lookup to other table

I'm working on Login/Logout System and I'm facing this problem regarding on SELECT Query. I have 2 tables namely dtr_tbl and shift_tbl. In dtr_tbl are the logs of the user(login/logout time details) ...
0
votes
1answer
69 views

CONCURRENT SELECT's SLOW … LOCK IN SHARE MODE

My Table has 25 Million rows total and 6 million rows satisfying below where clause. Select 1: select max(stamp) from Table where type = "string" LOCK IN SHARE MODE ; 6000+ seconds Select 2: ...
0
votes
1answer
28 views

Padding MySQL queries

I'm trying to retrieve logs of our plant where a certain event has occurred. At the moment, I can query the actual event by using an alarm_code field. However, I'd also like to include about an ...
0
votes
2answers
52 views

MySQL: What exact commands does each privilege level allow?

I am configuring some new logins for users who have already been using the relevant MySQL servers under other logins. I would like to limit their privileges to the minimum each user needs, based on ...
0
votes
0answers
43 views

What is the difference between normal Select query and a Select query inside Common Table Expression(CTE)? [duplicate]

I'm having a table 'Products' and if Execute this query SELECT TOP 1000 [ProductID] ,[ProductDesc] ,[ManufacturingDate] ,[ExpiryDate] ,[Price] FROM ...
0
votes
1answer
56 views

Slow query while selecting a higher offset from millions of rows

I have sort of security log files containing millions of rows. On my current testing I imported the data for one month which is the sum of ~14,000,000 records. The table contains several set of fields ...
0
votes
0answers
34 views

How to fetch data on a single column from a table having composite key [duplicate]

I am working on a messaging application, where the users can have a group conversation with multiple recipients. So in my database, I have a table called user_message where user_id and message_id is ...
0
votes
0answers
84 views

SELECT from Function that return CURSOR

I would like to select from a function that return a SYS_REFCURSOR Type value. for example : CREATE OR REPLACE FUNCTION my_funtion ( my_param IN VARCHAR2) RETURN SYS_REFCURSOR IS ...
0
votes
1answer
159 views

How to get result count from mysql inner join

I have a mysql query something like this. SELECT SQL_CALC_FOUND_ROWS wp_posts . * , wp_geodir_gd_place_detail . * FROM wp_posts INNER JOIN wp_geodir_gd_place_detail ON ( ...
0
votes
0answers
37 views

Use join to aggregate data from various normalized tables

I have a database as follows: tbl_clients (Primary Key: client_id, Foreign Keys:place_id,district_id,zone_id,country_id) tbl_places (Primary Key: place_id) tbl_districts (Primary key: district_id) ...
0
votes
0answers
23 views

Need help on transferring data from a PostgreSQL database to a future (to be decided) database!

(English isn't my native language) I'm doing an internship for school and my task is to theoretically describe the best way to transfer the data from their current database (PostgreSQL) that they have ...
0
votes
1answer
179 views

Update does not working while select returns right results

I am trying to execute the query below: UPDATE( SELECT Ord.Completed FROM Product P INNER JOIN Order1 Ord ON Ord.OrderID = P.OrderID WHERE EXISTS( SELECT * FROM Delivery ...
0
votes
1answer
22 views

How to modify the below script to take tables and its sizes for a particular databse to send via mail to a recipient?

SELECT top 10 t.NAME AS TableName, (SUM(a.total_pages) * 8)/1024.0/1024.0 AS TotalSpaceGB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN ...
0
votes
2answers
177 views

How to find all schemas where a table has records?

In my Oracle database, I have a number of schemas (users/owners) with similar table structures (about 100 in total). However, some tables are empty in a lot of schemas. Is there a way to find out for ...
0
votes
1answer
45 views

select statement for subtype

Okay, so all I'm trying to do here is list the managers who supervised the employee that charged a customer $100 for a rental on November 1st, 1998. My issue is that I don't know how to select the ...
0
votes
2answers
35 views

Get all the users that are managed by a targeted manager

I have the following model: Table user: (id, name, direct_manager_id) Table customer: (id, name, managed_by_id) i.e. users can have another user as their direct manager, Customers can be managed ...
0
votes
1answer
57 views

Need help in incrementing the date in oracle

Error thrown as "Invalid number" while trying to increment the date. Given below is the query executed through sqlplus. Please help me on this. select ''''||to_date(:To_date, 'dd-mon-yyyy')+1||'''' ...
0
votes
1answer
90 views

using update query with subquery in Oracle sql

I have an update query where I am trying to update a field but for more than one record, I am trying a join but it doesn't work. Below I get multiple values that come back from the select statement ...
0
votes
1answer
46 views

How not to display column name during select query

I have a sql file which prints the count(*) of some tables and result of it is something like this. COUNT(*) ---------- 10443 COUNT(*) ---------- 497 COUNT(*) ---------- ...
0
votes
0answers
34 views

SSRS using select from (Select)

I want to report a script which contains a Select from (Select), when I just run the second select I have no problem but when I put the whole script it loads and nothing happens. Here's my code : ...
0
votes
1answer
208 views

Copy Data To Different Server using SELECT * INTO?

I'm not a DBA by any means. I'm just knowledgeable enough with the MySQL command line to be capable half the time and dangerous the rest. Today I was working on exporting data from a one DB into ...
0
votes
0answers
39 views

mysql:Multi tables SELECT Query

I have 2 tables in my database table_names, with the fields: id, name, join_date, country table_sigs, with the fields: id, pet_title, act_date, member_id I would like to know how many members ...
0
votes
1answer
115 views

What is wrong with this SELECT query? No errors, no results

Haunted by this MySQL query that refuses to return results: select entity_id from customer_entity where entity_id NOT IN (SELECT sfo.customer_id FROM sales_flat_order sfo); If you divide the two ...
0
votes
1answer
65 views

Query execution involving two servers in SQL server , is really slow

I am running the following query , and it takes over 1 hour to finish execution. As there are so many joins and then one union is involved, is it normal for this query to be executed in 1 hour, or is ...
0
votes
1answer
178 views

Speeding up a simple select query on Mariadb

I have a 100k rows table with the following structure: +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
53 views

Query to select distinct name based on several factors

I have a table with the following data (simplified here): ID | PolicyName | Setting | State | Value 1 Default DiskUsage Enabled 1 2 Default Memory Enabled 1 3 Default CPU Enabled 0 4 Personal ...
0
votes
0answers
39 views

SQL JOIN excluding duplicate

I'm using PostgreSQL under ubuntu 14.04. I have two tables as below: create table material_base_name ( id bigserial, base_name varchar(50), constraint pk_materials_base_name primary key (id) ); ...
0
votes
1answer
116 views

SQL Server sometimes uses a lot more cpu for query

I have a stored procedure with a quite heavy SELECT statement that includes five joins and uses FieldName LIKE '%searchterm%' on multiple columns. This query usually finishes in about 2-5 seconds. But ...
0
votes
1answer
145 views

How to union tables into a new table in mysql? [closed]

I have a table as following: id col1 col2 col3 type 1 12 12 23 1 2 23 33 1 0 3 333 2 21 1 ..... I try to select 1% sample of where type=1 and all the data where type=0, I ...
0
votes
1answer
184 views

How can I optimize this Cross Table COUNT() mysql query?

I am trying to cut down the retrieval speed of this MySQL query. All instances of COUNT() point to the PRIMARY key of each field. Currently this query makes my MySQL hang. It seems ridiculous as I'm ...
0
votes
1answer
37 views

how to optimize grouped select from an already grouped select query

I already had a specific query to get a number of shops, but I couldn't sum the cost field, I kept on getting an error on the sum function, but it worked when I did this; select tab.shop_id, ...
0
votes
0answers
66 views

Text comparison select query needed that finds most whole word matches within larger string

I have a database of products, many with similar names. For example... iPhone 5s (GSM model) A1533 iPhone 5s (GSM model China) A1518 iPhone 5s (GSM model China) A1530 iPhone 5c (GSM model) ...
0
votes
1answer
218 views

SQL SELECT - adding field to SELECT with JOIN is changing the results

I have a SQL sentence with JOIN, running on mysql 5.5.16: SELECT DISTINCT (ot.orders_id), ot.value FROM orders_status_history osh INNER JOIN orders_total ot ON ( osh.orders_id = ot.orders_id ) WHERE ...
0
votes
0answers
61 views

Select and sum from related columns

I have table with the following columns: date (month), hours/days (31) and daily tasks (31) Columns: date day1 day2 day3....task1 task2 task3.... Records: june 4 6 2 .... run walk ...
0
votes
0answers
31 views

SQL Get result as a column

Is it possible to get a result as a column? The SELECT query will only need to return one record, however, at the time of querying the database it will not know what attributes are in the table, and ...
0
votes
0answers
9 views

Improving the performance of this select query [duplicate]

i have two tables TABLE 1 and TABLE 2 And i am running this select query to fetch the data , it takes long time to run SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4 FROM TABLE1, TABLE2 WHERE ...
0
votes
1answer
41 views

How to get desired type of records for desired number of times in oracle

I want to get get 6 "double_scoring" records and 4 "singe_scoring" records from my Profiles table based on SCORING_RULE column values. Can you please let me know how can I do this with SELECT query ...
0
votes
0answers
90 views

Name for SELECT equivalent of UPSERT

UPSERT means INSERT and\or UPDATE, is there a standard name for a sproc that will INSERT if necessary and then SELECT? e.g. I want to get the IS of a row that matches fields and give me the ID, if ...
0
votes
1answer
270 views

How to remove duplicates in col1 and get group MAX in col2? / mysql

Given the following table: col1 Col2 A 1 B 1 C 1 B 2 B 3 A 0 C 5 how do I write a SELECT query (I just assumed has to be SELECT) that returns the unique items of col1 and the ...
0
votes
0answers
1k views

SQLite UPDATE two tables from VIEW or TEMPORARY TABLE

Here is the query that doesn't compile just to show how it should look like. But since I can't do Multiple table UPDATE's. This fake query should be able to run the inner UPDATE's for both tables if ...
0
votes
0answers
111 views

How to grant privileges for a user to create views on specific tables on Oracle SQL?

I just added: GRANT CREATE VIEW TO user1; But it gives user1 access to ALL the tables, I would like them to only have access to the tables where the field X is user1
0
votes
2answers
795 views

Getting the top count of records from a table for each month and user

I have a table where translations are stored along with the user_id and when the translation was added. I want to create a query that shows the 'Translator of the month' (the person who did the most ...
0
votes
0answers
354 views

How to increase MySQL performance with joins

I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below: SELECT t1.`ARTREF1` FROM `lijnen` AS t3 INNER JOIN (`kortingen` AS t2, ...
0
votes
0answers
47 views

mysql select query optimization [duplicate]

Possible Duplicate: mysql select query optimization I have two tables, testa and testb. CREATE TABLE `testa` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT NULL, ...
0
votes
0answers
106 views

Being careful about additional records being added in a duplicate record merge?

We have a multi-user MS-Access 2003 database. Connecting to it using SQuirrel SQL, and the JDBC/ODBC bridge, I've managed to eek transactions of some sort out of JET by turning off autocommit. I've ...
0
votes
2answers
60 views

How to split a row in a result into mutliple rows

I have the following query: SELECT vs.MonthName AS Month, vs.Region, vs.Category, CAST (MIN (a.TotalAmount) AS money) AS ActualAmount, MIN (a.TotalCases) AS ...
0
votes
2answers
82 views

Selecting rows with condition on two columns together

i have table with columns Number and VAT How can i select all rows without row which is Number=123 and VAT=15 ? I tried something like this SELECT * FROM tablex WHERE <.. zilions of ...
0
votes
1answer
124 views

Select historical prices for an exact datetime - MySQL

I have a price_list table where I store historical prices for each item. columns are - id, name, price, start_date, item_id When a price is changed, a one new row is inserted with a current ...
-1
votes
3answers
115 views

Add Contents in a Column and make them 0 [closed]

http://sqlfiddle.com/#!3/96f11/3 In the above fiddle, I need the required output in the Fiddle. ie., The UserID column in Filtered Table and Main table are equal. I need to get the Amt column data ...