0
votes
1answer
18 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 ...
15
votes
2answers
3k views

Why would SELECT * be magnitudes faster than SELECT foo?

Consider a table of values and hashes, like so: +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
50 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
36 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 ...
1
vote
0answers
27 views

Multiple selects ordering desc from single table

I'm trying to do multiple selects to give multiple ordered columns. I'm struggling to get the below mysql query to achieve the output I want. SQLfiddle: http://www.sqlfiddle.com/#!2/a1002/3 ...
0
votes
2answers
43 views

SELECT query with referenced subqueries

How does one reference the currently-grouped by field in a subquery? Consider a table with the following data: Client Status Size 1 A 500 1 B 1500 2 ...
0
votes
1answer
32 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
27 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
65 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
2answers
387 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
2answers
129 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
3answers
65 views

How to create an Access query to find # symbol into a field?

It seems easy but I'm missing something. I have a MySQL database that is connected from access through ODBC connector. I'm looking for the fields where there are # symbol but I'm getting zero ...
2
votes
2answers
616 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
60 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
53 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
62 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
129 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
2answers
81 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
0answers
59 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
115 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
181 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
2k 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
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
41 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
48 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 ...
1
vote
1answer
30 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
67 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
80 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
106 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
69 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
98 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
71 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
1answer
35 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
73 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
2k 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 ...
0
votes
1answer
87 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
231 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
75 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
1k 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
1k 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
2answers
2k 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
203 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
1k 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
211 views

How to avoid duplicate entries with SELECT statement?

+----+----------------------+---------+---------+---------------------+-------+ | id | translation | id_word | id_user | added | tuser | ...
0
votes
2answers
382 views

How to use OR in query with priority?

In a simple query like SELECT * FROM table WHERE id='x' OR id='y' How to give a priority to get only one of the WHERE clauses? I mean getting rows with id='x'. Only if there is no row, get row ...
0
votes
1answer
153 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 ...
-3
votes
1answer
5k 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 ...
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 ...
0
votes
1answer
137 views

Conditionally limit MySQL SELECT query

My goal is to select rows from a mysql table, but according to certain column. Normal SELECT would be: SELECT ti.id, ti.userid, ti.date, ti.duedate, ti.datepaid, ti.subtotal, ...
1
vote
4answers
3k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...