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
1answer
7k views

Postgres: How to insert row with autoincrement id

There is a Table "context". There is an autoincrement id "context_id". I am using sequence to retrieve the next value. SELECT nextval('context_context_id_seq') The result is: 1, 2, 3,...20.... But ...
1
vote
1answer
9k views

ora-01410 invalid RowID sporadically in a select statement

I have a select that looks up some data by a join. The system runs Oracle 10.02g and the select looks like this SELECT distinct t1.crit FROM table_name1 t1 INNER JOIN table_name2 t2 ON t1.crit ...
1
vote
4answers
162 views

WHERE NAME LIKE ( A SET OF NAMES )

I would like to drop all the jobs related to a set of specific databases. So I have added all the names into a table variable and I was trying to generate the scripts to drop the jobs. how can I ...
1
vote
2answers
121 views

How to select data in the same table on different rows that are empty?

I use this select on my SQLite database: SELECT attributeX FROM table GROUP BY attributeX HAVING COUNT(attributeY) = 0; My table: attributeX (type integer) | attributeY (type text) ...
1
vote
1answer
1k 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 ...
1
vote
2answers
2k 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 ...
1
vote
1answer
384 views

Simple select statement not returning correct data

I am trying to return data, where I want the item name (items.name), vendor name (vendors.name), url of the price (prices.url), and the price (prices.price) from 3 different tables (items,vendors and ...
1
vote
2answers
1k views

multiple select query for the same tables with union [closed]

Using below query i want to find out the top number of students from each catagory(SC,ST,OBC,etc) on plus2percentage basis. eg i want top 3 students from SC catagory, top 2 from ST and Top3 from OBC. ...
1
vote
2answers
82 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 ...
1
vote
4answers
543 views

cross self join to get more rows - better solution?

I have a table employees: +----------------+------------------+ | Field | Type | +----------------+------------------+ | id | int(10) unsigned | | persons_id | ...
1
vote
1answer
766 views

T-SQL, show a set of rows (specific column) as a field, for a better desc, please see the table relationship

I have been having a problem for quite a while now, that I can show with a sample of 5 tables (I'm actually dealing with more than 5!): -------------------------------- shipments ...
1
vote
2answers
167 views

Select Between Date Range Mysql

I want to select records between 2 dates: select * from orders where orders.created <= '2015-07-31'; select * from orders where orders.created >= '2015-07-01' and orders.created <= ...
1
vote
1answer
62 views

Oracle: guaranteeing unmodified database state with queries?

If the first word of a SQL statement is either WITH or SELECT, is the statement guaranteed not to change the state of the database? i.e. is this loop "safe"? read SQL string if string begins with ...
1
vote
1answer
809 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 ...
1
vote
1answer
4k 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 ...
1
vote
1answer
55 views

Altering strings while selecting them

I'm working in SQL Server 2008, I have a varchar(255) column in my table, I want to select out all of the strings which contain the ':' character, and select the first part of the string before (but ...
1
vote
1answer
8k 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: ...
1
vote
2answers
43 views

Order by on an alphanumerical column

I have a column that has strings of the following type: Miller 10 Allen 20 King 10 .... Jones 100 I try to sort the column based on the numerical part of the data. I tried the following: SELECT ...
1
vote
2answers
4k 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 ...
1
vote
2answers
5k views

Getting repeated value in a group_concat only if ids are different

I have two tables, first the table Product: id|category_id --+----------- 1 | 12345 2 | 12345 3 | 12465 And then a table activity: id|prod_id|activity_type |description ...
1
vote
2answers
55 views

Why this query returns rows that I don't want to be there

I'm trying to running this query: SELECT * FROM (`default_users`) LEFT JOIN `default_profiles` ON `default_profiles`.`user_id` = `default_users`.`id` WHERE `default_users`.`group_id` ...
1
vote
1answer
44 views

Multiple left join where not in another table

I have the following tables (there is more detailed data in each one, I just put the minimum to create a sample): __________ _____________ _____________ _____________ | person | | provider | | ...
1
vote
1answer
48 views

How to select users that doesn't in the role

I have 4 roles which are Admin, Procurement, Accounting, HR roles and 3 tables which are Users, Roles and Userroles. They user can have many roles as well as the roles can have many users. the problem ...
1
vote
2answers
464 views

SELECT and UPDATE in a single query while updating selective fields of duplicate records only

I have a MySQL table named Contacts_table with, say, the following contents: ID NUMBER NAME CITY 001 2222222222 John Doe Los Angeles 002 ...
1
vote
1answer
30 views

select group by with multiple AND conditions on the same columns

I've an sql table like this: SID|SERVICE_SID|ATTRIBUTE_SID|VALUE 1 2 5 "VAL1" 2 2 4 "VAL8" 3 2 3 "VAL4" 4 11 5 ...
1
vote
1answer
45 views

Unexpected empty result [closed]

select a,b from table1 where a not in (select x as a from table2) AND b not in (select y as b from table3) The above query gives an empty result. Why? And how should I construct this combined ...
1
vote
1answer
46 views

joins with “could be blank” conditions

Question is most probably stupid but I am stuck on this and cant think anymore(Pissed Off). I have these tables-> employee(has a column foreign to location),location and location_flag. And I need ...
1
vote
1answer
961 views

Group by age range and gender with aggregation function on some columns

In Mysql, I have a patient table with id, birth_year and gender. Next table is transactions in which I have id, patient_id [FK from patient] and checkup_date. I need to select rows such that I get ...
1
vote
2answers
354 views

Multiple SELECT vs single SELECT over TCIP

My question is a performance one: I need to select roughly 400+ rows, i know their ids. currently i loop in code and fire off multiple queries over tcp/ip - because its easy: ... select [columns] ...
1
vote
3answers
3k 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 ...
1
vote
1answer
201 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
238 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 ) ...
1
vote
1answer
8k 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 ...
1
vote
1answer
1k views

Insert from one row to another using cases

My original table was(being used since 2005): CREATE TABLE `request` ( `msg` VARCHAR(150) NOT NULL, `id` VARCHAR(20) NOT NULL, `ctg` VARCHAR(10) NOT NULL DEFAULT 'misc', `date` ...
1
vote
1answer
243 views

MySQL SELECT query: i want show duplicated entries

Example My query is: SELECT code_CAT FROM categories WHERE id_code IN(14,14,14,14); output is: 53724 Only one row. Instead I want this output: 53724 53724 53724 53724 How can I achieve ...
1
vote
1answer
1k views

How to change the display of an empty string in mysql command line tool?

I'm maintaining a database with InnoDB tables. These tables have some columns of type (from show create table): `val0` varchar(30) default NULL, `val1` varchar(30) default NULL, etc... From the ...
1
vote
1answer
166 views

Complex mysql selection

I have the following table, id | booki_id | bet_id | bet | bettype | line | odds 1 | 123 | 321 | 1 | 3way | 0,0 | 2.3 2 | 123 | 321 | 2 | 3way | ...
1
vote
2answers
113 views

“For each group of results fullfilling condition X, select only result with smallest column value A”

I have a table with data and dates like so: DECLARE @p TABLE (P_key int, P_Data char(1), P_ValidUntil datetime) INSERT @p VALUES (20, 'T', '2003-02-28') INSERT @p VALUES (21, 'U', '2005-05-31') ...
1
vote
2answers
45 views

Select data where column value exists in seperate rows for same reference id

This is on a MySQL database. Locations have services. What I want is to get all results where service_id is 2 AND 3, not OR so using IN is out I believe. Database ---------------------------- | ...
1
vote
4answers
242 views

MSSQL Select all parent rows for which exists a combination of children rows with desired value

I would like to select all parent rows for which exists a combination of children rows with desired value. The easiest approach is to use multiple exists clauses, but this is not optimal - anyone ...
1
vote
1answer
85 views

Can I parse a text value from a specific column in a query (MySQL)?

I was playing around with Sakila and I ran across the actor_info view and one of its columns is film_info which is a detail of all the movies one actor has worked in. For example a query would be: ...
1
vote
1answer
781 views

MySQL Count rows from another table for each record in table

SELECT student.StudentID, student.`Name`, COUNT(attendance.AttendanceID) AS Total FROM student LEFT JOIN attendance ON student.StudentID = attendance.StudentID I am trying to count the last ...
1
vote
1answer
46 views

Creating a View with results from join of scalar tables

I am really bad at SQL Queries and seem to be stuck at a very odd problem. Maybe there's an alternative of doing this, but I simply can't figure it out. Maybe you guys can help! So, here's my ...
1
vote
3answers
899 views

Select only rows with minimum values from union table

SQL Server 2005 Here is a sampling of the data from a union of two tables (named "vProducts_All"): DBID UPC Price1 Price2 1 023614025559 689.0000 649.0000 2 ...
1
vote
1answer
305 views

Select statement for selecting every week's log record

Here is a simplified version of the table in question: +------------------------------------------------------+ | DatabaseName | LogRecordNumber | LogTimestamp | ...
1
vote
1answer
281 views

Determine last time a mysql table was accessed (i.e. SELECT'ed)

In my mysql DB schema there are old tables that are no longer used. I want to come up with a systematic way to show my developer a list of tables and last time they were read (SELECT'ed, not ...
1
vote
1answer
41 views

Should I drop this column or is there a query that can achieve a results table without duplicate columns?

I use a column named active in several of my database tables to adjust whether a particular entry/row/user/etc should be included in future query result tables instead of deleting the information. ...
1
vote
1answer
269 views

Running queries directly on DB2 databases

Just have a general query, if it is really fine or safe to run long select queries on RDBMS production Databases specifically DB2? If NO, what are the major risks and issues in executing select ...
1
vote
1answer
1k 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 ...
1
vote
1answer
378 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, ...