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
2answers
15 views

DB2 - REPLACE INTO SELECT from table

Is there a way in db2 where I can replace the entire table with just selected rows from the same table ? Something like REPLACE into tableName select * from tableName where col1='a'; (I can export ...
0
votes
2answers
40 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
1answer
6k views

Select Multiple Values from Same Column; one sql statment

I am looking to return a list of users from a database. There are hundreds of users in the db. Each user has it's own unique user_id. All of the values that I am trying to return reside in the same ...
0
votes
1answer
21 views

Postgres: Disable “similarity” column output on “SELECT SIMILARITY”?

I've tried disabling similarity column output on SELECT SIMILARITY but still cannot. Below query will generate a column similarity: SELECT SIMILARITY(title, 'Nation'), title FROM gallery WHERE title ...
0
votes
1answer
32 views

Postgres: Subquery in select similarity [on hold]

I need to find similar entry of title, just like in "Similar post" of many blog. By the way, I'm not sure if this is the right way I need to find list of rows similar to title where the uri is ...
0
votes
0answers
22 views

Fetching data from View with more number of columns [on hold]

I have created a view with 400+ columns & fetching data from 4different table (max 15,000 rows) in it. I have specified name of all columns individually. Ex: CREATE OR REPLACE FORCE VIEW ...
1
vote
1answer
37 views

MySQL select with join table

I have 3 tables, one of them is join table: user table: CREATE TABLE `user` ( `id`, `name`) media table: CREATE TABLE `media` ( `id`, `value`, `type` enum('M', 'T')) user_media table: CREATE ...
0
votes
1answer
32 views

Select range of non-identical date from different table

On MySQL5 with innoDB, I have sensor data in multiple tables, all with an ID and a DATETIME field, like so (two tables shown, but there are more). The IDs and DATETIMEs are not identical between ...
0
votes
0answers
15 views

Searching a keyword from multiple tables using my querry , [migrated]

I want to search "law" from three tables tbl_books, tbl_books_author and tbl_books_subject. I am running following query. SELECT * FROM tbl_books p, tbl_books_author d, tbl_books_subject m WHERE ...
8
votes
4answers
11k views

What does the term “projection” refer to in an SQL query (or view definition)

In Oracle's document The Query Optimizer, under View Merging, I found the following information The view merging optimization applies to views that contain only selections, projections, and ...
1
vote
1answer
21 views

Can I parse a text value from a specific column in a query (MySQL)?

I was playing around with Sakila and I ran across the actor_info view and one of its columns is film_info which is a detail of all the movies one actor has worked in. For example a query would be: ...
0
votes
1answer
116 views

Run and Save multiple SELECT statements

Within a Postgres function I would like to run a number of SELECT commands. For each SELECT command I would like to output written to separate text files. What is the best way to implement this? ...
0
votes
1answer
150 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
725 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
2answers
35 views

SQL Server user cannot select from a table it just created?

I'm having trouble understanding permissions in SQL Server (in this case SQL Server 2012, but not sure how version-dependent this issue is). I can create a database, create a login/user on master, ...
1
vote
2answers
104 views

SELECT and UPDATE in a single query while updating selective fields of duplicate records only

I have a MySQL table named Contacts_table with, say, the following contents: ID NUMBER NAME CITY 001 2222222222 John Doe Los Angeles 002 ...
0
votes
1answer
168 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 ...
1
vote
1answer
53 views

Insert into table select * from table vs bulk insert

I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ? If no, Is there any way exclude index while inserting records. Process is inserting ...
0
votes
1answer
43 views

How do I perform a query on a dynamic list of tables?

I have a database named as ( conntrack ) with unknown amount of tables.I have a static table "tabidx" which has one column "date". The following table is static and its name is tabidx which provide ...
0
votes
3answers
130 views

DISTINCT Role in SELECT

EDIT: @Moderators: Please delete this question. I still feel that it is unclear what I am asking. Maybe I could have used some Venn diagrams in order to describe the problem. The COUNTs do not seem to ...
0
votes
1answer
53 views

How do I select the value with SERIAL data type in postgres [closed]

I want to know how to select values from a column that has SERIAL data type. I already try to do that but the output is like this 'Resource id #3', and not just '3' I use SERIAL so I dont have to ...
1
vote
1answer
68 views

Selecting most recent date when dates are stored in another table

Working in a MySQL database, I have tables set up like this: Table song_instances has song_id (int), meeting_instance_id (int) The meeting_instances table has columns meeting_instance_id (int), ...
2
votes
2answers
29 views

MySQL - SELECT on MyISAM Table on index - Does table size (rowcount) matter?

I was arguing with a coworker if SELECT * WHERE t BETWEEN ? AND ? on a MyIsam table where t is an index is significantly slower for huge tables. The hypothetically returned dataset is always the ...
1
vote
1answer
21 views

select group by with multiple AND conditions on the same columns

I've an sql table like this: SID|SERVICE_SID|ATTRIBUTE_SID|VALUE 1 2 5 "VAL1" 2 2 4 "VAL8" 3 2 3 "VAL4" 4 11 5 ...
0
votes
1answer
12 views

Numeric cell with 0 not null

I have the following query (in a Oracle Database): SELECT SUM (CANTITATE) FROM ANEXA2A_TABEL2_ROWS WHERE TIP_ACTIUNE = 'COLECTATE' AND TIP_AMB = ...
1
vote
1answer
33 views

how to cache a subset for cascading select queries in mysql

Here is another database problem I stumbled upon. I have a date-range partitioned MyIsam look-up table with 200M records and ~150 columns. On this Table I need to perform cascading SELECT-Statements ...
0
votes
1answer
30 views

Grant Select on some columns for specific user

I have a windows group with 20 people and all 20 people in the group were revoked access for some columns in the table. Now I need to add the select permissions for 2 of the users in this windows ...
0
votes
1answer
177 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
2answers
32 views

Why does “DELETE WHERE (VARCHAR)=0” execute successfully?

DELETE FROM stats WHERE name=0 "name" is type VARCHAR(64). The above resulted in all records in the table being deleted, instead of just the 10~ that should have been affected. Only that many were ...
0
votes
1answer
77 views

The cost of left out join versus a select [closed]

I'm currently working with Ebean for database abstraction. My test database is Postgresql 9.3 I turned on logging for the SQL queries to test some code behavior. So, assume 2 tables, OneToMany ...
0
votes
1answer
128 views

Select * from (every table in an subselect)

I was trying to get an select * from (for column table result) every table_name with the result bellow, how can i achieve this? (SELECT a.table_name FROM all_cons_columns a JOIN all_constraints c ...
0
votes
1answer
154 views

Processlist show most of the queries are sending data for select query

I have faced some problem like sending data state in processlist. In my application I used parallel processing which send 50 count at a time, and that will do some MySQL database operation (select and ...
3
votes
1answer
6k views

how to fetch different data on compare rows of two tables

I have two tables in my database, one is user_app table in which user info are stored, table are as: +--------------+--------------+------+-----+-------------------+-----------------------------+ | ...
1
vote
1answer
61 views

Oracle: guaranteeing unmodified database state with queries?

If the first word of a SQL statement is either WITH or SELECT, is the statement guaranteed not to change the state of the database? i.e. is this loop "safe"? read SQL string if string begins with ...
-1
votes
1answer
30 views

SELECT rows with duplicate values

I have a table of names in mysql InnoDB as Firstname Lastname Steve Ross John Doe J. Doe Mike Ross Michael Ross Roger ...
1
vote
2answers
73 views

How to select data in the same table on different rows that are empty?

I use this select on my SQLite database: SELECT attributeX FROM table GROUP BY attributeX HAVING COUNT(attributeY) = 0; My table: attributeX (type integer) | attributeY (type text) ...
1
vote
1answer
150 views

MySQL Count rows from another table for each record in table

SELECT student.StudentID, student.`Name`, COUNT(attendance.AttendanceID) AS Total FROM student LEFT JOIN attendance ON student.StudentID = attendance.StudentID I am trying to count the last ...
0
votes
1answer
75 views

MySQL - How to replace a given value from multiple columns when using SELECT?

I need to SELECT a set of rows of a given table whose structure looks like - 1º line is the name of columns A01 A02 A03 A04 A05 ... A200 99999 99999 10 753 99999 ...
2
votes
2answers
268 views

MySQL - How to check whether all values of a given SELECTed row are equals? [closed]

Using MySQL, how can i SELECT all rows whose values are equals ? For instance, suppose a SELECT of a given table as follows COLUMN_A COLUMN_B COLUMN_C ... COLUMN_N 1 2 4 ...
2
votes
1answer
2k views

Mysql search matching multiple values in multiple columns

I need to speed up a search. Currently we let our users input a name: John Doe Then in Java we split that string to create multiple values (or "terms") ["John", "Doe"] and loop through the terms ...
1
vote
1answer
44 views

Reference column alias in same SELECT list

I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this: SELECT samples.id, ...
0
votes
0answers
62 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
43 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) ...
-2
votes
2answers
32 views

SQL SUM of different product groups in one query?

I have a table with the following columns: PRODUCT_ID | PRODUCT_NAME | GROUP_ID | GROUP_NAME | PRICE GROUP_ID and GROUP_NAME are not UNIQUE. Can I make a selection which just returns each GROUP_ID ...
0
votes
1answer
53 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: ...
3
votes
3answers
67 views

How does like query work with indexed table

I am working in Spring-java and mysql. I have to query the table of size 100k records. Table has say 10 columns. And In my sql select query I have to make like queries with %text% search on say 4 ...
0
votes
1answer
35 views

Replace nested “select with count” with outer join? is this possible?

SELECT c.classid , c.classname , (select count(s.id) from students s where s.classid = c.classid and status = 'failed') as noOfFailed, (select count(s.id) from students s where ...
2
votes
1answer
68 views

Better way to select from nested tables in PostgreSQL

I have the following schema with millions of rows in the match table and each match have two sides (away and home). I want to create a view which shows the most significant data regarding the matches ...
0
votes
1answer
24 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
1answer
67 views

Alternatives to Linked Servers (Allow Inline SQL with Parameters)

I am trying to find a solution to execute inline SQL with parameters against some liked server databases. From what I have found online the only proper way to query a linked server is to use ...