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
412 views

PostgreSQL WHERE clause with two OR'ed conditions, priority to first

I'm storing user data (username, email, password hash) in PostgreSQL, and I can only enforce uniqueness on username. I'd like to let users provide either their username or their email when they login. ...
2
votes
1answer
251 views

Select query - group by and sub group by

CREATE TABLE [dbo].[temptable] ( [Id] INT NOT NULL PRIMARY KEY IDENTITY, [uid] INT NOT NULL, [pid] INT NOT NULL, [event] INT NOT NULL, [timeStamp] DateTime NOT NULL ) I want ...
2
votes
2answers
772 views

Why does MySQL fail to use an index effectively when LIMIT offset is large?

I do not understand when querying 5 rows from a table of 500,000 rows, the following query takes 0.00sec: SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id LIMIT 20,5; But querying ...
2
votes
1answer
830 views

How can I use running total aggregates in a query to output financial accumulations?

I am attempting to write a query that will be consumed by a SSRS 2012 report. Given a paid invoice, with a certain set of account allocations towards which the money paid towards it must be saved, I ...
2
votes
1answer
3k views

Postgres nested hstore select query

I have a carts table with items hstore column. An example entry in this column is: carts.items row#1 = { "1614" => {:quantity=>"100", :price_cents=>1655}, ...
2
votes
2answers
5k views

To select from all tables inside the schema in PostgreSQL

Is it possible to select from all tables inside the schema? I got all the table names from select table_name from information_schema.tables but I am unable to use it to do my query.
2
votes
1answer
515 views

select count(*) in mysql 5.5 innodb— rewrite advice?

I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow... select count(*) from mails3 join questions using (question_id) where ...
2
votes
1answer
3k views

MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?

I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The ...
2
votes
2answers
631 views

How can we automatically replace a table with a view for a specific user?

We currently have some reports that query a specific table, but one of our users wants the ordering to be different. I don't want to go through and fix each of the reports, because that's rather a ...
2
votes
1answer
160 views

How to get number of active users with one SQL-Query?

I am running a site similar to craigslist where users can create classifieds and write/reply messages. In order to get an idea of activity I would like to get the amount of active users per month. ...
2
votes
2answers
66 views

Cannot build correct SELECT in MySql

Please help me to create a correct MySql query, which I failed to do. I have 2 tables in our DB: CREATE TABLE IF NOT EXISTS `user_audit` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id`...
2
votes
1answer
74 views

Insert into a DB that was selected via other DB?

I have a table that stores what database should I use for a given device( specified by its IMEI), like this: imeiDB( IMEI VARCHAR(15), db VARCHAR(100)); Let's say I have the database device1(IMEI:...
2
votes
2answers
81 views

MySQL - SELECT on MyISAM Table on index - Does table size (rowcount) matter?

I was arguing with a coworker if SELECT * WHERE t BETWEEN ? AND ? on a MyIsam table where t is an index is significantly slower for huge tables. The hypothetically returned dataset is always the same,...
2
votes
1answer
2k views

How to speed up slow query using GROUP BY and CASE?

I'm not very experienced with MySQL and and trying to figure out how to speed up a slow query using GROUP BY and CASE statements. CASE 1 -- GROUP BY and CASE SELECT SQL_NO_CACHE m.id, m.sku, m....
2
votes
2answers
68 views

How can I simplify this UNION?

Here's the case - I have the following query: SELECT DISTINCT post_id FROM ( ( SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date FROM wp_related_people AS rp JOIN ...
2
votes
1answer
3k views

Mysql search matching multiple values in multiple columns

I need to speed up a search. Currently we let our users input a name: John Doe Then in Java we split that string to create multiple values (or "terms") ["John", "Doe"] and loop through the terms ...
2
votes
2answers
229 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) ...
2
votes
2answers
136 views

Get count of processed queries

I have been searching a while to find a way to retrieve the amount of queries my postgres database is processing and responding to. I found various statistics about disk I/O, insert/update statistics ...
2
votes
1answer
630 views

Help with SELECT queries

I need help with some queries. I struggled do find out how to do it, but I think I finally caught on or at least got the results I was looking for in the database. These are the tables (given by my ...
2
votes
1answer
439 views

Does mysqlhotcopy block SELECT statements from different clients?

I would like to create a backup of different MyISAM tables on a server using mysqlhotcopy. These tables are about 6GB in size. I would like to know, does mysqlhotcopy block users from SELECTing (...
2
votes
0answers
48 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, o....
2
votes
1answer
1k 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, ...
2
votes
0answers
640 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 swap....
2
votes
1answer
5k views

I have to update all the rows of a Column in a table. Here is my query

So My tables are People: People_name(VARCHAR 100), People_id (INT, PRIMARY KEY AUTO INCREMENT) Tracks: Track_id(INT PRIMARY KEY), People_id_references (int ,Foreign key references People(...
2
votes
0answers
217 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 friend....
2
votes
1answer
126 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
3answers
1k 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
2answers
3k views

How to delete rows from a table when we're joining and return data?

I have two queries: The first one is as below: SELECT * FROM batch bp INNER JOIN sender_log sl ON sl.id=bp.log_id AND bp.protocol='someprotocol' The second query is used to delete rows: ...
1
vote
2answers
98 views

Manage select results in sql

We need to manipulate a set of records returned from a select query. This can be done in : Either by saving the whole record to a table variable and then do the required process. We can do like this ...
1
vote
1answer
15k 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
3answers
11k views

Select query to retrieve values even if are null

I need some help in a query. Need to retrieve data from 1 table, even if one of the field has null value. Here's an example: Select name, SUM(credit) as credit From expenses Where name like 'vendor0%'...
1
vote
2answers
120 views

Select rows from T such that sum(T.quantity) is greater than an argument

TLDR: I need a way to limit my select such that only rows needed to fill my new order are returned. Hey, I'm looking for a better way to do a query on the simplified toy table below. Right now I do a ...
1
vote
2answers
2k views

How to count the rows from 1 table but from two different column

SELECT distinct Major, COUNT(Major) AS MajorCount FROM [College Management System].[dbo].[ApplicantCourseDetails] group by Major SELECT distinct MinorI, COUNT(MinorI) AS MinorCount FROM [College ...
1
vote
1answer
12k 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
1answer
42 views

How to select data from differents servers with a single query?

I have a challenge! I need select from 4 different tables from 4 different servers. Example: Select * from server1.tableS1.sys.view Select * from server2.tableS2.sys.view Select * from server3....
1
vote
2answers
355 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
3k 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
1answer
426 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
99 views

how do I use a value from the select in a joined subquery?

I'm trying to avoid a stored procedure or bringing it into code but they may be my only alternatives. Here's my sql. Does anyone know any SQL voodoo that could make this happen without a stored ...
1
vote
1answer
70 views

SELECT query types and optimization

I'm using a database monitoring system that, amongst other data, shows the following values for SELECT queries: number of joins that use ranges on the first table number of joins that do a full scan ...
1
vote
2answers
2k 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
86 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
2answers
2k 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? ...
1
vote
2answers
7k 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
4answers
587 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
37 views

Increment fixed value in select clause

How might one increment a "fixed" value in the SELECT clause of a query, so that each row has a different value set? For an obviously not working example that illustrates the intention: SELECT ...
1
vote
1answer
45 views

(MYSQL) Select union of rows depending on row before?

I have a table in MySQL that looks like this: CREATE TABLE IF NOT EXISTS `mytable` ( `id` int(11) NOT NULL, `data` VARCHAR(255), `otherid` int(11), ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT ...
1
vote
2answers
77 views

How to get the value of column that the same for few rows by selecting values from other columns?

Since I am not a MySQL expert, I am having trouble determining how to ask a search engine for advice on this topic. I have the following table: +------------+--------------+-------------------+ | ...
1
vote
2answers
360 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 <= '2015-...
1
vote
1answer
63 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 ...