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
1answer
133 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
50 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
36 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
105 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
110 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
161 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
34 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
54 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
169 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
60 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
20 views

How can I check the size of my SELECT query? PostgreSQL

I know how can I check the size of my database, called test: SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database where datname='test'; Now, I ...
0
votes
0answers
29 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
88 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
211 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
100 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
1answer
685 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
329 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
102 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
54 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
65 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
111 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 ...
-1
votes
2answers
3k 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 ...
-1
votes
1answer
2k views

'ambiguous column name' error refers to database, table, and column [closed]

sqlite3 gives an 'ambiguous column name' error with the query below. The error itself references what seems to be about as an unambiguous column name as one can imagine, identifying the database as ...
-1
votes
2answers
4k views

How to do division from select statements in sql server?

I am trying to create a select statement that can return a table of 1 column. Basically, I am looping through all the records of NewHire, and then using its id, to select stuff from another table. I ...
-1
votes
2answers
2k views

Select query having count and variable

I have a table like the one below. I need to form a select query. I'm going to pass a RefID like SELECT SNo FROM Register WHERE RefID = 1 And beside the SNo in the Statement, I need to have ...
-1
votes
2answers
127 views

Fetching Recently added rows from table [closed]

I have the following table structure , name || description || timestamp varchar varchar date time stamp column consists of time the row is added Now i need to fetch the ...
-1
votes
1answer
27 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
votes
1answer
100 views

Select latest record for each ID

I have three tables that contains data as below: Users Id Name Other_Columns --------------------------- 1 John Blah 2 Ricky Blah 3 Stella Blah 4 Bob Blah Saldo Id ...
-1
votes
1answer
238 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 ...
-1
votes
1answer
92 views

Optimization of a select statement

I'm using MySQL and have a table user_data like this: user_id int(10) unsigned reg_date int(10) unsigned carrier char(1) The reg_data is the unix timestamp of the ...
-1
votes
2answers
1k views

SELECT only values THAT are different

I have two instances user1 and user2. They have colors u_id | color<br> 1 | red<br> 1 | green<br> 2 | red<br> 2 | blue<br> I need a mysql request, that ...
-2
votes
1answer
13k 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 ...
-2
votes
1answer
107 views

how to count result set for select statment [closed]

I have to make query from DB. I found some solution but it isn't so precise as it need to be. Here result of my query: country weight Germany 1687.5 Gt.Britain 1687.5 Japan ...
-2
votes
1answer
119 views

How big the database should be in order to take 30 minutes to query

We have few tables each containing 2,5 million rows in average. Then there is a select query with joins. It takes more than 30 minutes to execute. The db server runs on a machine with 9GBs of RAM and ...
-2
votes
1answer
1k views

Using the “Select Top (100) percent vs. Select * , or selecting any column” [closed]

I wrote the first query below that I was hopeing to give me the exact same results that the 2nd query does, but it seems there is one recrod different: SELECT DocumentSourceName FROM ...
-2
votes
1answer
407 views

How to prevent duplicated records when applying ORDER BY NEWID() to fetch them randomly? [closed]

I tried using the solution provided in http://stackoverflow.com/questions/1117761/sql-query-to-return-rows-in-random-order to fetch random records in my query. But I have to add NEWID() to the list of ...
-2
votes
1answer
92 views

Select data one by one from table and data need to repeat custom number of times [closed]

I have table data like data1 data2 data3 data4 and I need to select these rows like this: data1 data1 data1 data1 data2 data2 data2 data3 data3 and how to perform this in SQL Server 2005?
-2
votes
2answers
28 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 ...
-3
votes
1answer
939 views

Best practices for finding and updating a handful of records?

Seems like a simple question, but wondering what is the "best" way to do this if you were "dotting your i's and crossing your t's".
-3
votes
1answer
117 views

Which is faster Select Column names or Select *? [closed]

I am writing a Stored Procedure which is joining 3 tables, each table have two Columns. Query 1 : SELECT * FROM [dbo].[PersonInfo] P join [dbo].[Receipt] R ON P.Id = ...
-4
votes
2answers
5k views

Get Hierarchial Data in Self-Referencing Table (Parents , Childs) [closed]

In the following Fiddle, sqlfiddle I will pass a sno and I need all the parents of that sno in a table. And all the children of that sno in another table Please refer to this question. Since ...