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

-1
votes
1answer
28 views

The sum of the distances of all employees and select the top 3 [on hold]

I have a table "raporty" in which there are reports of all employees. Each employee has their id. How to do that for each employee separately summed up his trip distance and choose the 3 largest in ...
0
votes
1answer
92 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
2answers
27 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 ...
1
vote
1answer
21 views

Creating a View with results from join of scalar tables

I am really bad at SQL Queries and seem to be stuck at a very odd problem. Maybe there's an alternative of doing this, but I simply can't figure it out. Maybe you guys can help! So, here's my ...
2
votes
2answers
2k views

To select from all tables inside the schema in PostgreSQL

Is it possible to select from all tables inside the schema? I got all the table names from select table_name from information_schema.tables but I am unable to use it to do my query.
0
votes
1answer
107 views

Simple SELECT query doesn't return FALSE if no results [closed]

This is probably a simple fix, but I can't figure it out. This is the MySQL query I have: SELECT (`clockcards`.`date` IS NOT NULL) AS `clockedin` FROM `users` JOIN `clockcards` ON ...
0
votes
1answer
29 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
21 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 ...
0
votes
1answer
108 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 ...
9
votes
2answers
4k views

How to select specific rows if a column exists or all rows if a column doesn't

I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1). Is there a way I can do this ...
2
votes
1answer
42 views

How to speed up slow query using GROUP BY and CASE?

I'm not very experienced with MySQL and and trying to figure out how to speed up a slow query using GROUP BY and CASE statements. CASE 1 -- GROUP BY and CASE SELECT SQL_NO_CACHE m.id, m.sku, ...
1
vote
0answers
22 views

two column compare ,get count(*) of two column A value same cloumn B value

I have a Mysql table as --------------------- -- page - user -- --------------------- -- page a - user 1 -- -- page a - user 2 -- -- page a - user 3 -- -- page b - user 1 -- -- page b - user 3 -- ...
0
votes
1answer
474 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
1answer
37 views

Select into existing table where ID is in another table [closed]

I have one table containing ~35k IDs (TableC), one table that features multiple columns (TableA), amongst others the ID from the before mentioned table and a third empty table, featuring the same ...
0
votes
1answer
18 views

Copy every entry from TableA to TableB where the ID is in a textfile containing a list of IDs

I have a text file (or an xls) with a 35k lines, each containing a single ID. I'm required to copy every entry of TableA to TableB where the ID is in said list. Would it be best if I'd create a new ...
0
votes
1answer
37 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 ...
1
vote
3answers
1k views

`Select` query take more time than it should

I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure Below is some of ...
-2
votes
1answer
9k views

How to get MAX and MIN in GROUP BY query?

In a simple query SELECT col1, col2, col3 FROM table1 GROUP BY col1 each row includes the first value of col2 and col3 for each unique value of col. How to select MAX and MIN for each value of ...
3
votes
3answers
11k views

Getting SELECT to return a constant value even if zero rows match

Consider this select statement: SELECT *, 1 AS query_id FROM players WHERE username='foobar'; It returns the column query_id with value 1 along with a player's other columns. How would ...
0
votes
1answer
38 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
14 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
52 views

Columns count in SELECT query and SQL Server buffer cache

Let's assume that I have a table with 30-50 millions records. It has ~40 columns. Currently when I request data it always requests all columns because of legacy ORM that we use. I know that if we ...
0
votes
1answer
35 views

Move/Copy unique entries only from TableA to TableB, if it's not in the destination table

I'm required to move every unique entry from TableA to TableB, it the unique entry from TableA is not already in TableB. Both tables look the same, they don't have any keys, but the combination of to ...
0
votes
1answer
19 views

Select all results from two independent tables

I have two tables: Comments -------- id comment_content time_stamp Activities ----------- id activity_content time_stamp And I would like to combine the results from both, ordered by their ...
2
votes
2answers
50 views

How can I simplify this UNION?

Here's the case - I have the following query: SELECT DISTINCT post_id FROM ( ( SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date FROM wp_related_people AS rp JOIN ...
0
votes
1answer
144 views

Mysql Select table records which have not been updated since last month

I have a table names 'scoredata' having fields id, lessonid, levelid, studentid, addedate in which score for each day will be entered by the admin. I want to select only those studentid whose score ...
1
vote
3answers
121 views

Select only rows with minimum values from union table

SQL Server 2005 Here is a sampling of the data from a union of two tables (named "vProducts_All"): DBID UPC Price1 Price2 1 023614025559 689.0000 649.0000 2 ...
1
vote
1answer
74 views

Determine last time a mysql table was accessed (i.e. SELECT'ed)

In my mysql DB schema there are old tables that are no longer used. I want to come up with a systematic way to show my developer a list of tables and last time they were read (SELECT'ed, not ...
0
votes
1answer
49 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 ...
1
vote
1answer
43 views

MAX (id) function with MySQli

How can I use the following statement to return only the record with the maximum PersonID from the table? /* Create the prepared statement */ if ($stmt = $mysqli->prepare("SELECT Max(PersonID) AS ...
1
vote
1answer
43 views

Select statement for selecting every week's log record

Here is a simplified version of the table in question: +------------------------------------------------------+ | DatabaseName | LogRecordNumber | LogTimestamp | ...
0
votes
0answers
26 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
45 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
28 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 ...
-1
votes
1answer
200 views

Restrict access to rows by usename

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so ...
0
votes
1answer
50 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
43 views

Work with table named Group [closed]

I have a table named Group in my database , when I try to work on it it gave error : SELECT GroupID,GroupName FROM Group it said : Msg 102, Level 15, State 1, Line 1 Incorrect syntax ...
1
vote
1answer
46 views

Slow Query with LIMIT

We have this query: SELECT "messages".* FROM "messages" WHERE "messages"."account_id" = 1 AND "messages"."status" = 'unread' AND (created_at >= '2014-04-24 01:00:00.000000') ORDER BY id DESC It ...
0
votes
1answer
36 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
1answer
29 views

Filtering the results of a SELECT query to avoid certain words in the value of a column?

I have a table called videos with a column named title and there is a set of words (around 50 words as of now but the number of words may increase) which is the knowledge base for filtering the ...
1
vote
1answer
33 views

Unexpected empty result [closed]

select a,b from table1 where a not in (select x as a from table2) AND b not in (select y as b from table3) The above query gives an empty result. Why? And how should I construct this combined ...
0
votes
2answers
32 views

How to use relationships to extract data via select

Disclaimer: Novice in SQL I have 3 tables. There is one "major" table (labeled used_for_lookup below) used to do look-ups on and the other two (labeled fid_table and did_table below) are foreign keys ...
-1
votes
2answers
2k views

How to get missing values in row order?

In a table, many rows have been deleted, how to get the id of missing rows for next INSERT? For example id col1 1 1 3 3 4 4 5 5 8 8 9 9 how can I get the ...
0
votes
1answer
37 views

Using count() and sum() in a calculation in a select

I am trying to do the following in a single select. We have a SAAS that has Company records and User records. A User is a member of a Company (Company.CompanyId = User.CompanyId). In the User record ...
0
votes
0answers
29 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) ); ...
1
vote
1answer
42 views

joins with “could be blank” conditions

Question is most probably stupid but I am stuck on this and cant think anymore(Pissed Off). I have these tables-> employee(has a column foreign to location),location and location_flag. And I need ...
0
votes
1answer
24 views

How can I get all rows of data not included in a TVP

I am trying to do the following: declare @domains DomainNames; declare @CompanyId int declare @CompanyName varchar(255); declare @deletedList DomainNames; set @CompanyId = 239; set @CompanyName = ...
2
votes
0answers
238 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
0answers
29 views

Select all foreign key relationship chains from any set of potentially gapped table names

I am using this query to find all foreign key relationships: SELECT con.relname AS child_table, att2.attname AS child_column, cl.relname AS parent_table, att.attname AS parent_column ...
1
vote
1answer
37 views

use variable to determine the name of a column in the select in postgre

I'm trying create a update for a new structure of tables from a old table, but not using functions. I'm trying create a script to it. The old table is like this: -- old table (OldTable) name| col_a | ...