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

SQL insert into … values (…, select(…))

I am trying to insert 25 users, user1...25 into Members with the same GR_ID = 2 with command: insert into MEMBERS (GR_id, username) values (2, (select username from USERS where username like ...
0
votes
1answer
85 views

MySQL select with left join query

I have following code. select class.name as classname, class.hours as hours, teachers.first_name as teacherFirstName, teachers.last_name as teacherLastName from teachers,class left join ...
0
votes
1answer
22 views

Get the most present elements from many fields

Here is my table: id|item0|item1|item2 --+-----+-----+----- 1 | 10 | 12 | 12 2 | 12 | 68 | 13 3 | 15  | 13 | 10 I just want to get the most present number of all itemX columns. The query ...
0
votes
1answer
32 views

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

I wanna ask how to select coloum 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 input ID like when ...
0
votes
1answer
33 views

Selecting 3MB (or more/less) data from database server

I installed PostgreSQL Server on my Windows 7 machine. I created database and a simple table, and populated the table. Now I want to connect through the network with my Postgres server and select a ...
0
votes
1answer
41 views

MySQL locking tables / rows on select statement

I have a web app that does an addition to a field based on a user input. So say for example User-A inputs 7, my web app would first query the contents of the field for the current number in there, ...
0
votes
1answer
61 views

Low-level relational operators from SQL

I have been asked to outline the sequence of commands using low-level operators (join/project/select) that are used in the following SQL statement; SELECT d.name, k.city FROM DOG d, KENNEL k WHERE ...
0
votes
1answer
70 views

how to correctly return values from a function

I would like to create a function that queries a user table by passing a username and a password, the function is supposed to return 'Y' if the username / password are correct and 'N' if they're not. ...
0
votes
1answer
43 views

Design for hierarchical embedded database

The goal is to construct a hierarchical database of Objects having the following format: TypeA -> ObjectA [values, contains ObjectB*, ObjectC*, etc.] TypeB -> ObjectB [values, contains ...
0
votes
2answers
156 views

Insert into … (…) (Select…) on dupliacate key update

Can you help me to solve a problem. The task is to insert a record in MySQL database. terms: If the record exists - to update the record. If the record does not exist - to create a new one. The Key ...
0
votes
1answer
70 views

Is there a table size above which SELECT performance degrades significantly?

In MySQL, does SELECT performance degrade significantly after a certain table size? I have a table with about 107 million rows and some queries seem stuck in "Sending data" for a long time. Of course, ...
0
votes
2answers
107 views

Select * on inner join with common columns

I have 3 tables (mysql db) on which I want to do an inner join. The problem is that these tables have common columns. I know I could use aliases to differentiate them (select A.x as Ax, B.x as Bx, C.c ...
0
votes
1answer
367 views

How can I search a column in MySQL containing unix timestamps with a query referencing a different format?

I have a mysql table that contains something like: +-------------+-----------------+ | FOO1 | FOOTIME | +-------------+-----------------+ | blah | 1234567890000 | ...
0
votes
1answer
42 views

Order result by number of occurence of a word in a text

I am working with sqlite3/python . I would like to order results by the number of occurence of %@% in text. For exemple Jimmy 10 // Jimmy wrote 10 strings like %@% James 2 Sophia 0 ..etc The ...
0
votes
1answer
134 views

How to UPDATE MAX and MIN of a GROUP BY?

I have a SELECT query of a table with GROUP BY id col1 category 1 some 1 2 x 1 3 another 1 4 x 2 // should be removed 5 some 2 6 x ...
0
votes
1answer
98 views

Update , Select and Join using 3 tables

There are 3 tables: Label (Label_ID,Label_Name), Label_ID is Primary Key. Tracks (Prefix, Label_ID), Prefix is Primary Key,Label_ID is empty column Top100 (Prefix,Label) No keys in this table. My ...
0
votes
3answers
822 views

how to grant select access my sql server

when I exec the syntax to gain the access I get an error that I cant grant permissions to my self. any Ideas please!
0
votes
1answer
271 views

How to avoid duplicate entries with SELECT statement?

+----+----------------------+---------+---------+---------------------+-------+ | id | translation | id_word | id_user | added | tuser | ...
0
votes
1answer
44 views

Why this query returns rows that I don't want to be there

I'm trying to running this query: SELECT * FROM (`default_users`) LEFT JOIN `default_profiles` ON `default_profiles`.`user_id` = `default_users`.`id` WHERE `default_users`.`group_id` ...
0
votes
1answer
2k views

How can I select the latest record having one state where no later records exist with any other state?

I want to select the last record (by datetime) having a given state where no later records exist with any other state for each given "widget". widget state timestamp ------ ----- ...
0
votes
1answer
33 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
35 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
0answers
29 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), ...
0
votes
1answer
61 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
25 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
43 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
30 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
21 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
34 views

Simple SELECT query doesn't return FALSE if no results

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
77 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
47 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
19 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
1answer
45 views

Gets records by max date and hour

I have this query: "SELECT * FROM `" . $table . "` WHERE STR_TO_DATE(data, '%d/%m/%Y')=(SELECT max(STR_TO_DATE(data, '%d/%m/%Y')) FROM " . $table . ") AND hour=(SELECT MAX(hour)-".$delta_hour." ...
0
votes
1answer
106 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 ...
0
votes
0answers
27 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
38 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
84 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
0answers
23 views

MySQL most specific to least specific

I have records in the next table: id_a | id_b | id_c | data each of the parameters can be 0 or arbitrary integer number. I want to get the most specific record for provided values of a, b and c. ...
0
votes
1answer
85 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
138 views

MySQL select closest time

I have a table that contains the start times for shift patterns, for example. I have early starters with a shift start defined at 07:00:00 and normal starters at 08:00:00 When someone punches in at ...
0
votes
0answers
643 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
77 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
325 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
231 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
1answer
176 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
0answers
44 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
99 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
1answer
44 views

Optimize Mysql SELECT from 60 milion rows

Task and Problem There are 100 realms and 10k items. An item has many histories. There are 600k histories in each realm. And I want to find all of an item's histories of the last day by using: ...
0
votes
2answers
37 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 ...