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
762 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
21 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
1answer
347 views

Select into existing table where ID is in another table [closed]

I have one table containing ~35k IDs (TableC), one table that features multiple columns (TableA), amongst others the ID from the before mentioned table and a third empty table, featuring the same ...
0
votes
3answers
248 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
3k 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
765 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
214 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
1answer
30 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
130 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
34 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
44 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
61 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
88 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
2answers
1k 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
132 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
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 ...
0
votes
3answers
130 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
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
1answer
572 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
3k 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
1answer
83 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
39 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 ...
0
votes
1answer
62 views

Filtering the results of a SELECT query to avoid certain words in the value of a column?

I have a table called videos with a column named title and there is a set of words (around 50 words as of now but the number of words may increase) which is the knowledge base for filtering the ...
0
votes
1answer
6k 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
118 views

Run and Save multiple SELECT statements

Within a Postgres function I would like to run a number of SELECT commands. For each SELECT command I would like to output written to separate text files. What is the best way to implement this? ...
0
votes
1answer
46 views

Is there any performance difference between a group by statement and nested select statements in sql?

I am curious to know about the differences between using group by statement and nested selects to achieve the same result. for example consider I have two tables such as the following: tblPurchase ...
0
votes
1answer
40 views

How to sum the buy order and sell order in one row per each ticker

I have a table containing some trading information of several tickers and I would like to divide the data into sum(Amount) for buy and sell separately, grouping by each ticker. I have one table ...
0
votes
1answer
53 views

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

I want to know how to select values from a column 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 ...
0
votes
2answers
87 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
465 views

How to return a result set from oracle procedure ?

How can i return a result set from a stored procedure in oracle ? suppose i want to return a table from a simple select statement such as this : Select * from tblTest I tried this but this seems ...
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
90 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
90 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
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
231 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, ...
0
votes
1answer
6k views

Delete from table where multiple fields match select subquery from other table

I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table. This is what I have so far, though it doesn't work: ...
0
votes
1answer
227 views

Order of where clause evaluation

If I have a MySQL table which has content from the last 3 months I actually do the following: SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt FROM `mDelivered` where _campaign = 2 and `session` = ...
0
votes
2answers
7k views

MySQL: Join one Row in a table with two Rows in other table

In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations): `users` `user_login` +--------------+ ...
0
votes
2answers
2k views

Need a way to query a table, and JOIN it with the TOP 1 related record from an other table

As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question. As stated, this query does return me 95% of the data I need -- SELECT ...
0
votes
2answers
1k views

MySQL Reusing calculation in SELECT clause in a VIEW

I have an SQL query that has a simple date difference between NOW() and the creation date (is in the table). I need to reuse this again within the SELECT clause in a VIEW. Is this possible at all? ...
0
votes
2answers
106 views

SQL and Multiple Indexes

Consider the following table: i | a | b | c -------------- 0 | 1 | 2 | 3 1 | 4 | 5 | 6 2 | 7 | 8 | 9 3 | x | y | z The table, in the end, will have several thousand rows, and even more columns then ...
0
votes
1answer
3k 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
3answers
198 views

How can I select data and the total to cursors?

I have a table like this: ID | Name ------------ 1 | AA 2 | AA 3 | AA 4 | BB I want to select all rows where Name like 'AA' in a cursor and get the count from this result in another ...
0
votes
2answers
37 views

SQL Server user cannot select from a table it just created?

I'm having trouble understanding permissions in SQL Server (in this case SQL Server 2012, but not sure how version-dependent this issue is). I can create a database, create a login/user on master, ...
0
votes
1answer
79 views

The cost of left out join versus a select [closed]

I'm currently working with Ebean for database abstraction. My test database is Postgresql 9.3 I turned on logging for the SQL queries to test some code behavior. So, assume 2 tables, OneToMany ...
0
votes
1answer
32 views

Select range of non-identical date from different table

On MySQL5 with innoDB, I have sensor data in multiple tables, all with an ID and a DATETIME field, like so (two tables shown, but there are more). The IDs and DATETIMEs are not identical between ...
0
votes
1answer
36 views

Replace nested “select with count” with outer join? is this possible?

SELECT c.classid , c.classname , (select count(s.id) from students s where s.classid = c.classid and status = 'failed') as noOfFailed, (select count(s.id) from students s where ...
0
votes
1answer
67 views

Alternatives to Linked Servers (Allow Inline SQL with Parameters)

I am trying to find a solution to execute inline SQL with parameters against some liked server databases. From what I have found online the only proper way to query a linked server is to use ...