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

2
votes
1answer
107 views

Select last row for a certain group

I have two tables: Table users columns | type | notes --------|--------|------- id | bigint | PK ... | ... | ... Table events columns | type | notes ...
2
votes
1answer
130 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
49 views

SQL query returning cartesian and duplicates and not single records

So I have four tables as follows: CREATE TABLE softdrinks ( drinkID INT NOT NULL AUTO_INCREMENT, drinkName VARCHAR(100), PRIMARY KEY (drinkID) ); CREATE TABLE categories ( categoryID INT ...
1
vote
1answer
32 views

Is it possible to write a select * view that includes new columns added to table?

I would like to write a view that is essentially something simple select a + b, * from table_name and will always truly include all columns in table_name, even when I add or remove them. If I do the ...
1
vote
1answer
33 views

Cities and States (a few data): single table or splitted?

I am designing a DB schema and I need to store cities and states. I expect only a few rows, let's say, 20 cities. Example: id | city | state 1 | Rio de Janeiro | RJ 2 | Niteroi ...
1
vote
1answer
2k views

SELECT from Function that return CURSOR

I would like to select from a function that return a SYS_REFCURSOR Type value. for example : CREATE OR REPLACE FUNCTION my_funtion ( my_param IN VARCHAR2) RETURN SYS_REFCURSOR IS ...
1
vote
1answer
476 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 ...
1
vote
1answer
348 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
83 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 ...
0
votes
1answer
38 views

INSERT and UPDATE in Trigger

I have created a trigger so I could insert every row in a backup table CREATE OR REPLACE TRIGGER xyz AFTER INSERT ON table_1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO table_2 ...
0
votes
1answer
263 views

Select all from table using procedure oracle

I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the ...
0
votes
1answer
39 views

Unexpected SELECT permissions

My user WebApp seems to have SELECT permissions on every table in every schema and I can't figure out why. WebApp is only in the Public role. Public doesn't own any schema. Explicit permissions ...
0
votes
1answer
47 views

Group-by in an Oracle database

on an Oracle database. If I make an: select id,id_operator,denumire,cod_fiscal,loc_den,str from rap where is_punct_lucru=1 and id_operator in ( select id_operator from rap where ...
0
votes
1answer
96 views

on every row on SELECT Query must lookup to other table

I'm working on Login/Logout System and I'm facing this problem regarding on SELECT Query. I have 2 tables namely dtr_tbl and shift_tbl. In dtr_tbl are the logs of the user(login/logout time details) ...
0
votes
1answer
32 views

Padding MySQL queries

I'm trying to retrieve logs of our plant where a certain event has occurred. At the moment, I can query the actual event by using an alarm_code field. However, I'd also like to include about an ...
0
votes
1answer
109 views

Slow query while selecting a higher offset from millions of rows

I have sort of security log files containing millions of rows. On my current testing I imported the data for one month which is the sum of ~14,000,000 records. The table contains several set of fields ...
2
votes
0answers
47 views

How to order by fields of joined table while selecting for update?

For selecting some rows of table Fwt_Chipkarte for update I use the following SQL statement (IBM DB2): SELECT o.Archiviert, o.Id, o.IdComm, o.Kartennummer, o.Konto, o.LetzteAenderung, ...
2
votes
0answers
611 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 ...
2
votes
0answers
215 views

Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id

I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
1
vote
0answers
40 views

Fetch items where related rows has different values

I have table in MySQL called attributes, and other table, attribute_values, one attribute can have multiple values, values identified by context attribute and has value_c attribute wich contain value. ...
1
vote
0answers
45 views

Matching same unknown characters in two columns

So here is the situation. I have a database with values in columns that look something like this field_0_data1, field_0_data2, field_1_data1, field_1_data2 Is it possible to only select the columns ...
1
vote
0answers
43 views

Find the last record of group of overlapped records

I have a little dilemma with filtering out result. The result and what I want to achieve is not very trivial and I didn't find an answer anywhere after extensive search. So here is my problem: I ...
1
vote
0answers
41 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
0answers
56 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
212 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
0answers
136 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
0answers
58 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
186 views

SELECT, PROJECT, CARTESIAN PRODUCT and JOIN

I have the following tables; DOG +----------------------------------------------+ | Name Type Age Kennel_name Owner_name | +----------------------------------------------+ ...
1
vote
0answers
234 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
0answers
508 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, ...
0
votes
0answers
23 views

InnoDB: why “non-locking” SELECT still gets locked?

This issue can be related to my first question: InnoDB: very slow random updates, hanging in “updating” state But i think it needs to be clarified in a separate thread. The Problem: a simple read ...
0
votes
0answers
36 views

Select query keep showing “loading” message in phpMyAdmin

This query works fine: select * from prospect p JOIN prospect_company c ON p.prospect_id=c.prospect_id JOIN prospect_address a ON a.prospect_id=c.prospect_id; However, the following query keeps on ...
0
votes
0answers
35 views

Display Complete Month With or Without TIMESTAMP field

Please edit the title if you have a better, I didn't find other words to describe this. Environment Fields: |id|time|text| Situation I have to make a report with the total of records per day, ...
0
votes
0answers
29 views

Need help with Oracle Query for totaling fields with separate clauses, any feedback highly appreciated!

I am stuck on an issue that has been causing my mind to ramble for the last couple of days. DETAILS ABOUT REPORT This report is to identify the following details based off of the CUSTOMER_ID new ...
0
votes
0answers
26 views

How can I combine join and random choice from one table but 2 conditions?

I want to choose 200 entries randomly from a table However, I have two conditions the first 100 should be white and the other black. How can I join them? One possibility to do is to run following ...
0
votes
0answers
67 views

MySQL Match … Against query very slow

I work on a website running on Propel ORM and I have this query: if(isset($args["QueryText"]) && $args["QueryText"] != "") { $query = $query ->withColumn("(MATCH (Request.Subject, ...
0
votes
0answers
68 views

rows to columns using SQL , merge queries

I would like to merge these two queries: SELECT um.user_id, um.meta_value, u.display_name, u.user_email, u.user_url, t.name FROM wp_usermeta um INNER JOIN wp_users u on u.id = um.user_id INNER JOIN ...
0
votes
0answers
93 views

Fetching from parent/child table pairs

Let there are two tables: "parent" and "child" related by 1:n relation (to every "parent" row there correspond any number of "child" rows). Suppose we need to fetch a subset of parent table with all ...
0
votes
0answers
115 views

Select sub columns from a view

I have a large view in Sql Server named ViewA which looks like Select ColumnA, ColumnB, ... ColumnZ FROM TableA Join TableB ON ... Join TableZ ON This view gives me exactly what I need in ...
0
votes
0answers
367 views

I want to rewrite my MySQL query so that FIND_IN_SET doesn't do a full table scan unless necessary

I want to rewrite my MySQL query so that the last table join doesn't do a full table scan unless necessary. Here's what I mean. Under certain conditions I don't need to select any rows in that join ...
0
votes
0answers
82 views

Use join to aggregate data from various normalized tables

I have a database as follows: tbl_clients (Primary Key: client_id, Foreign Keys:place_id,district_id,zone_id,country_id) tbl_places (Primary Key: place_id) tbl_districts (Primary key: district_id) ...
0
votes
0answers
46 views

SSRS using select from (Select)

I want to report a script which contains a Select from (Select), when I just run the second select I have no problem but when I put the whole script it loads and nothing happens. Here's my code : ...
0
votes
0answers
40 views

mysql:Multi tables SELECT Query

I have 2 tables in my database table_names, with the fields: id, name, join_date, country table_sigs, with the fields: id, pet_title, act_date, member_id I would like to know how many members ...
0
votes
0answers
57 views

SQL JOIN excluding duplicate

I'm using PostgreSQL under ubuntu 14.04. I have two tables as below: create table material_base_name ( id bigserial, base_name varchar(50), constraint pk_materials_base_name primary key (id) ); ...
0
votes
0answers
67 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 ...
0
votes
0answers
39 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 ...
0
votes
0answers
98 views

Name for SELECT equivalent of UPSERT

UPSERT means INSERT and\or UPDATE, is there a standard name for a sproc that will INSERT if necessary and then SELECT? e.g. I want to get the IS of a row that matches fields and give me the ID, if ...
0
votes
0answers
2k 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
0answers
145 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
0
votes
0answers
129 views

Being careful about additional records being added in a duplicate record merge?

We have a multi-user MS-Access 2003 database. Connecting to it using SQuirrel SQL, and the JDBC/ODBC bridge, I've managed to eek transactions of some sort out of JET by turning off autocommit. I've ...