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
3answers
110 views

New field based on sum field

I'm trying to create a new field based in another (sum field) SELECT sum(price) AS old_price, old_price+100 AS total_price FROM sales This returns Unknown column 'old_price' in 'field list' sum ...
0
votes
1answer
92 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, ...
3
votes
2answers
1k views

MySQL: Is it possible to order a query by a specific letter using ORDER BY?

What I'd like to do is order my query so that the results show every row found, but are ordered by showing results that begin with the letter 'D' first. Is this something that is possible to do with ...
0
votes
1answer
103 views

performance difference between < and <=

I remember that there is a performance issue with SQLs that use "<" against "<=" because of statistics and/or histograms and/or the data itself, but I dont remember exactly the issue. I know ...
-1
votes
2answers
128 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 ...
-2
votes
1answer
408 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
2answers
387 views

Why does MySQL fail to use an index effectively when LIMIT offset is large?

I do not understand when querying 5 rows from a table of 500,000 rows, the following query takes 0.00sec: SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id LIMIT 20,5; But querying ...
0
votes
2answers
434 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 ...
1
vote
2answers
80 views

Not able to produce table based on conditional select

I would like to achieve something that probably ends up in some kind of conditional select (MS SQL) but I do not know how to. I have tried to simplified my problem to the description below: I have 3 ...
2
votes
1answer
397 views

How can I use running total aggregates in a query to output financial accumulations?

I am attempting to write a query that will be consumed by a SSRS 2012 report. Given a paid invoice, with a certain set of account allocations towards which the money paid towards it must be saved, I ...
1
vote
0answers
141 views

How to join the tables in mysql to get wall messages and comments with name of person ? Only user and his friends messages

I have four tables in my mysql database as shown in the image url provided. I want to select data from the tables without duplication. I want to show messages and comments on wall according to the ...
0
votes
3answers
128 views

How to query for 'First showed up as'

Given the following table: Date Trd (INT(10)) Code (VARCHAR(10)) T-Date 1-Jan-14 1 B 1-Nov-13 1-Jan-14 2 B 3-Oct-13 ...
0
votes
1answer
949 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
8k views

MySQL: split value in column to get multiple rows

I have some data in a table like so: product_id categories 10 9,12 11 8 12 11,18,5 I want a select statement that would produce this output: product_id ...
0
votes
1answer
47 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 ...
1
vote
1answer
115 views

How to calculate average of list of qualifications of each pupil MYSQL

I have this database and query: http://sqlfiddle.com/#!2/b4cf72/116 And I would like to add a column with the average of the exam qualifications. However, I try doing this but it tells me there are ...
3
votes
1answer
157 views

postgres filtering select based on combination of joins

I am having trouble filtering a request that depends on a combination of child elements (one-to-many relationship) Please consider the following example. CREATE TABLE sample_container ( id serial ...
2
votes
1answer
274 views

Search a Redis Database

Imagine having a tun of hashes in a Redis Database, each representing a user object. How would you get the user with for example the lowest registration date? The user where ts_registered is the ...
1
vote
1answer
820 views

How do I query for leaf nodes in a hierarchy using a closure table?

I have some hierarchical data (representing musical instruments and playing techniques), where each leaf in the hierarchy is an audio file. The nesting depth of leaf nodes will vary because some ...
1
vote
1answer
54 views

SELECT showing two COUNTs

I had this table: tblA id | name | value 1 nameA 1 2 nameA 0 3 nameB 0 4 nameC 0 5 nameC 1 6 nameC 1 As you see, Value can be 1 or 0 I need to perform a query ...
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
1answer
180 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 ...
1
vote
3answers
4k views

Select query to retrieve values even if are null

I need some help in a query. Need to retrieve data from 1 table, even if one of the field has null value. Here's an example: Select name, SUM(credit) as credit From expenses Where name like ...
-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 ...
1
vote
1answer
331 views

Slow running query with inner SELECT in where clause

I have a query that looks like the following: USE DATABASE3; GO SELECT t1.COL1 AS COLUMN1, t2.COL2 AS COLUMN2, t1.COL3, t1.COL4 AS COLUMN4, t1.COL5 AS COLUMN5, ...
1
vote
1answer
186 views

Need to optimize simple SELECT query for InnoDB table with 2million rows

I'd like to make this query the fastest possible, since it's called really often. SELECT login_events.id FROM login_events WHERE ( DATE(created_at) >= DATE(CURRENT_DATE) AND person_id = 1 ) ...
0
votes
1answer
89 views

Get all items from list table which don't have a successful log entry for 'sent' in log table

How might one retrieve items that when joined either don't have a specific field (sent), or if they do have that field (sent) then they have an additional field (failure_reason) as well for every sent ...
0
votes
3answers
88 views

prepared statements are not inserted

i am new to JDBC and trying following code. Could not find the error in it try{ Connection con = ConnectionProvider.getCon(); stmt=con.prepareStatement("insert into ...
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
1answer
42 views

Should the value of a select max be indexed when the where clause is already indexed

I have a customer_orders table in a schema designed to be shared between multiple companies. Each customer_order has an invoice number that should always start at 1 and increment based on company ...
0
votes
1answer
112 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
vote
1answer
6k views

Select records in one table but not in another, when the second table is not matching on primary key

I have two tables from which I need only the results that appear in one table (list) which do not appear in the second table (cardinal). The list table's primary key is sku, and the table has a ...
2
votes
3answers
846 views

How to query efficiently from Postgres to select special words?

Let's say that I have a table called words with very many records. Columns are id and name. In the words table I have for example: 'systematic', 'سلام','gear','synthesis','mysterious', etc. NB: ...
1
vote
1answer
2k views

Select next and previous rows

I have the following table: CREATE TABLE post ( id bigint primary key, thread_id bigint, is_notice boolean, title text, content text ) I display the list ...
2
votes
3answers
76 views

Platform independent select ordered by primary key

Is there a reasonably standard way to perform a select on a single table and have the result set ordered by primary key without having to specify the primary key condition explicitly? Ideally I wish ...
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
1
vote
1answer
271 views

INSERT INTO selected columns creates new column from JOIN [closed]

I have an INSERT INTO ... SELECT.. query that uses a JOIN on two tables. The results of the query should take a subset of the information and insert it into an existing table. That part works, but the ...
0
votes
1answer
201 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
1answer
688 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 ...
1
vote
1answer
78 views

Mysql with index [closed]

I have this following sentence: SELECT Day(C.DataCadastro) AS Dia, Month(C.DataCadastro) AS Mes, Year(C.DataCadastro) AS Ano, COUNT(I.Plano) AS Total FROM clientes AS C ...
1
vote
1answer
5k views

Selecting multiple columns from multiple rows in one column (subquery?)

I'm working with MySQL 5.1.68. I have a situation where I'd like to select rows from another table into a column. I'd like to output this: id # name # citizen_name (multiple citizen names) 1 # ...
2
votes
1answer
4k views

MySQL query to evaluate ranges in date and time elements of a datetime column separately

TABLE EventID, EventTime (DATETIME) 1 2013-09-29 23:55:00.0 2 2013-10-01 00:05:00.0 3 2013-09-29 23:55:00.0 4 2013-09-29 23:45:00.0 5 2013-10-02 23:05:00.0 6 ...
0
votes
3answers
1k 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!
2
votes
2answers
98 views

Get count of processed queries

I have been searching a while to find a way to retrieve the amount of queries my postgres database is processing and responding to. I found various statistics about disk I/O, insert/update statistics ...
0
votes
2answers
3k views

How to select only first column dynamically on mysql

I have to select only first column. Because of i dont want to select all columns like SELECT * FROM table. But my first columns may vary like following: id article_id user_id product_id page_id etc.. ...
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, ...
-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 ...
0
votes
1answer
479 views

How to avoid duplicate entries with SELECT statement?

+----+----------------------+---------+---------+---------------------+-------+ | id | translation | id_word | id_user | added | tuser | ...
-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 ...
0
votes
1answer
559 views

SQL output formatting

I want this: "ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID" "abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos" But I get ...