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

1
vote
0answers
32 views

Row locks on selects

select t.*, row_number() over ( partition by t.Entity_Identifier order by t.Message_Sequence ) as seqnum from Process_Activity t (ROWLOCK) where t.Process_Id = Is it possible for ...
1
vote
1answer
53 views

MySQL select with join table

I have 3 tables, one of them is join table: user table: CREATE TABLE `user` ( `id`, `name`) media table: CREATE TABLE `media` ( `id`, `value`, `type` enum('M', 'T')) user_media table: CREATE ...
1
vote
1answer
59 views

how to cache a subset for cascading select queries in mysql

Here is another database problem I stumbled upon. I have a date-range partitioned MyIsam look-up table with 200M records and ~150 columns. On this Table I need to perform cascading SELECT-Statements ...
1
vote
1answer
97 views

Reference column alias in same SELECT list

I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this: SELECT samples.id, ...
1
vote
1answer
72 views

How to insert the gathered dates into another table using INSERT SELECT in MySQL?

insert into rfdate (date) select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select ...
1
vote
0answers
46 views

How to use the value from record like as varchar?

I'm trying update a old table with result of selec for another tables but get a error using this sintaxe (e.g.): DO $$ DECLARE _roles refcursor; _onerole varchar; _record RECORD; ...
1
vote
0answers
131 views

two column compare ,get count(*) of two column A value same cloumn B value

I have a Mysql table as --------------------- -- page - user -- --------------------- -- page a - user 1 -- -- page a - user 2 -- -- page a - user 3 -- -- page b - user 1 -- -- page b - user 3 -- ...
1
vote
1answer
139 views

What is wrong with this SELECT query? No errors, no results

Haunted by this MySQL query that refuses to return results: select entity_id from customer_entity where entity_id NOT IN (SELECT sfo.customer_id FROM sales_flat_order sfo); If you divide the two ...
1
vote
1answer
217 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 | ...
1
vote
1answer
61 views

Slow Query with LIMIT

We have this query: SELECT "messages".* FROM "messages" WHERE "messages"."account_id" = 1 AND "messages"."status" = 'unread' AND (created_at >= '2014-04-24 01:00:00.000000') ORDER BY id DESC It ...
1
vote
0answers
77 views

Select all foreign key relationship chains from any set of potentially gapped table names

I am using this query to find all foreign key relationships: SELECT con.relname AS child_table, att2.attname AS child_column, cl.relname AS parent_table, att.attname AS parent_column ...
1
vote
1answer
139 views

use variable to determine the name of a column in the select in postgre

I'm trying create a update for a new structure of tables from a old table, but not using functions. I'm trying create a script to it. The old table is like this: -- old table (OldTable) name| col_a | ...
1
vote
1answer
90 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), ...
1
vote
1answer
90 views

Mysql: Problem using GREATEST with IFNULL and join

I have a query selecting the greatest value of 2 timestamp. To Achieve it I'm calling: SELECT GREATEST( IFNULL( tableb.create_time, 0 ) , IFNULL( tablea.create_time, 0 ) ) I'm using IFNULL because ...
1
vote
0answers
43 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 ...
1
vote
0answers
107 views

SELECT, PROJECT, CARTESIAN PRODUCT and JOIN

I have the following tables; DOG +----------------------------------------------+ | Name Type Age Kennel_name Owner_name | +----------------------------------------------+ ...
1
vote
0answers
167 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 ...
1
vote
1answer
55 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 ...
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
510 views

Merge 2 tables and fetch data one by one

I have two tables: product & offers. products has (prod_id, category_id, prod_name, brand, price, status) offers has (offer_id, prod_id, offer_type, discount_amt, validity) I want to display ...
1
vote
1answer
259 views

MAX (id) function with MySQli

How can I use the following statement to return only the record with the maximum PersonID from the table? /* Create the prepared statement */ if ($stmt = $mysqli->prepare("SELECT Max(PersonID) AS ...
0
votes
3answers
538 views

Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?

I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
0
votes
1answer
44 views

Work with table named Group [closed]

I have a table named Group in my database , when I try to work on it it gave error : SELECT GroupID,GroupName FROM Group it said : Msg 102, Level 15, State 1, Line 1 Incorrect syntax ...
0
votes
2answers
810 views

How to mix pairs of rows without JOIN (in a temporary table)?

I have a TEMPORARY table containing pairs of key/value as id col1 col2 type pair 1 key1 key2 key 1 2 value1 value2 value 1 3 key3 ...
0
votes
1answer
33 views

Postgres: Disable “similarity” column output on “SELECT SIMILARITY”?

I've tried disabling similarity column output on SELECT SIMILARITY but still cannot. Below query will generate a column similarity: SELECT SIMILARITY(title, 'Nation'), title FROM gallery WHERE title ...
0
votes
3answers
349 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 ...
0
votes
1answer
4k views

How to create an Sql table from SELECT with a multiple row DISTINCT constraint?

I want to create a table from a SELECT statement based on a multiple row DISTINCT, but PostgreSQL does not allow me to do it. I need to create a table where the elements were previously selected from ...
0
votes
1answer
836 views

Retrieving a fixed range of rows from Postgresql

I have a query: select qid,ansid,ans from table1 where askerid='something' This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the ...
0
votes
2answers
218 views

MySQL: SELECT the n-th item from an ordered result

SELECTing the top n elements from a table is easy: SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id DESC LIMIT 1000; Finding the n-th element with a subquery is quite ...
0
votes
1answer
81 views

Can you search using an index while indexes are being updated in oracle11g?

I am using oracle 11g. I noticed some performance problems that causes hanging on some search operations. Can I still search based on an index while that index is being updated in the database?
0
votes
2answers
27 views

MYSQL Select, where rows exists

I have a Mysql Database and tables with the structure: Table 'photos' - ID, Name Table 'photos_tags' - Key, Photo_ID, Tag My goal is to select all photos, which has one tag (it's not a problem) ...
0
votes
1answer
69 views

Grouping based on the status, then count its product and the total based on its category ID

I have a table with product ID, category ID and status of the product. +---------+-------------+-------------+ | id | cat_id | status | +---------+-------------+-------------+ | 1 ...
0
votes
1answer
32 views

Grant Select on some columns for specific user

I have a windows group with 20 people and all 20 people in the group were revoked access for some columns in the table. Now I need to add the select permissions for 2 of the users in this windows ...
0
votes
3answers
138 views

DISTINCT Role in SELECT

EDIT: @Moderators: Please delete this question. I still feel that it is unclear what I am asking. Maybe I could have used some Venn diagrams in order to describe the problem. The COUNTs do not seem to ...
0
votes
2answers
37 views

How to use relationships to extract data via select

Disclaimer: Novice in SQL I have 3 tables. There is one "major" table (labeled used_for_lookup below) used to do look-ups on and the other two (labeled fid_table and did_table below) are foreign keys ...
0
votes
1answer
45 views

Using count() and sum() in a calculation in a select

I am trying to do the following in a single select. We have a SAAS that has Company records and User records. A User is a member of a Company (Company.CompanyId = User.CompanyId). In the User record ...
0
votes
1answer
90 views

PostgreSQL SQL syntax - JOIN and substrings

I have a single table of hostnames and NFS exports, some but not all of which are related. Example entries: HOST VOLUME ----- ---------- host1 vol1 host1 vol2 host1 ...
0
votes
1answer
91 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
3answers
187 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
104 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 ...
0
votes
3answers
133 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
2answers
4k 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
1answer
597 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 ...
0
votes
2answers
72 views

Difference between the given queries

I am required to retrieve the list of pno ( product_id ) for the products which were purchased by two customers. For this, I have been given two relations which are ( along with the attributes ) as ...
0
votes
1answer
4k views

Select fields from multiple tables

I want to make a query a little complicated, and I'm not able to do it alone. I have the following tables: Table color: id_uo P id_cor PF id_area F text_cor Table area: id_area P ...
0
votes
3answers
57 views

If in mysql I have string `xxx yyy` - how can I SELECT this if user input `xxxyyy`?

My online-shop in PHP (OpenCart). On site we have product-search. For example product have name iSport 220, and if user search input string is iSport220 we must show this product. And vice versa. ...
0
votes
2answers
31 views

PostgreSQL, merge join results

I have 2 tables with common columns and would like to know if it's possible to request this 2 tables by merging the results. Table "job" : parent_id, target_id, start_date, end_date, jobposition_id ...
0
votes
2answers
55 views

MYSQL Select Distinct with SUM

I am developing a Stocks management application, the Portfolio register table has 4 columns, other than date and registry_key, and it looks like this: ---------------------------------------- | ...
0
votes
1answer
198 views

MySQL - How to replace a given value from multiple columns when using SELECT?

I need to SELECT a set of rows of a given table whose structure looks like - 1º line is the name of columns A01 A02 A03 A04 A05 ... A200 99999 99999 10 753 99999 ...
0
votes
1answer
41 views

Move/Copy unique entries only from TableA to TableB, if it's not in the destination table

I'm required to move every unique entry from TableA to TableB, it the unique entry from TableA is not already in TableB. Both tables look the same, they don't have any keys, but the combination of to ...