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
0answers
26 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 ...
2
votes
0answers
188 views

mariadb out of memory crash

I have recently migrated from mysql (5.5.36 MySQL Community Server (GPL)) to MariaDB (10.0.10-MariaDB-1~wheezy), along with switching my cloud hosting provider. Both machines have 2GB RAM and 1GB ...
0
votes
1answer
3k 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
67 views

ORDER BY that doesn't contain repeating values

Relevant SQL Fiddle (postgres 9.2) How do I state a SELECT .. ORDER BY .. that avoids having repeated values for a column? That is, for rows of the column "rank" (1,3,3,1) how do I get a result so ...
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 ...
1
vote
2answers
113 views

How to delete rows from a table when we're joining and return data?

I have two queries: The first one is as below: SELECT * FROM batch bp INNER JOIN sender_log sl ON sl.id=bp.log_id AND bp.protocol='someprotocol' The second query is used to delete rows: ...
2
votes
2answers
141 views

Why is this MySQL SELECT performing so slowly despite indexing?

I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation. So my table A looks like this: CREATE TABLE `A` ( `id_b` int(10) DEFAULT NULL, `id_c` int(12) ...
0
votes
1answer
40 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 ...
4
votes
2answers
117 views

select specified number of unique IDs where second column is unique

Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have ...
1
vote
1answer
153 views

How to improve the performance of this select query?

I have two tables: TABLE1 and TABLE2. 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
58 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 ...
1
vote
0answers
56 views

SELECT, PROJECT, CARTESIAN PRODUCT and JOIN

I have the following tables; DOG +----------------------------------------------+ | Name Type Age Kennel_name Owner_name | +----------------------------------------------+ ...
0
votes
1answer
56 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. ...
2
votes
2answers
794 views

Optimized query to fetch a record from 10 million entries

A table has 2 fields | UniqueKey | TimeStamp | ------------------------- | xfsddddq | 1024125412 | | xfstttdx | 1024125413 |< | xfsdxxau | 1024125415 | Estimated, there are 10 million ...
0
votes
2answers
74 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
3answers
57 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
66 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, ...
1
vote
2answers
156 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
100 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 ...
2
votes
3answers
69 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 ...
-1
votes
2answers
42 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
158 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
179 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
95 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
74 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 ...
0
votes
3answers
119 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 ...
2
votes
1answer
166 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
1answer
151 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
0answers
68 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 ...
2
votes
1answer
125 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 ...
0
votes
1answer
251 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
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
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 ...
1
vote
1answer
52 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
95 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 ...
1
vote
1answer
363 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 ...
7
votes
1answer
4k views

Autoincrement primary key in CREATE TABLE … AS SELECT

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query? For example: create table `user_mv` select ...
1
vote
1answer
32 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
71 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
100 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
1answer
120 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 ...
-2
votes
1answer
72 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 ...
7
votes
2answers
7k views

SELECTing multiple columns through a subquery

I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them. SELECT DISTINCT petid, userid, (SELECT ...
1
vote
1answer
105 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
73 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
80 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
527 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
36 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 ...
2
votes
3answers
399 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
489 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 ...