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

3
votes
2answers
242 views

Inner Join or direct data save in SQL Server

I am developing a social network site and expecting very good response from people so expecting huge users (maybe more than million in years). My website is holding user information table and all its ...
3
votes
1answer
5k views

SELECT LIMIT 1 per column value?

Lets say I have the following table ----------------------------- | user_id | comment | ----------------------------- | 2 | thats cool | | 2 | awesome | | 3 | ...
3
votes
1answer
2k views

MySQL Insert into two tables using new IDs

I'm trying to select data already in the database and insert it into two tables using the generated ID from table one. table1 contains configuration options, table2 contains pricing data. Table2 uses ...
3
votes
1answer
136 views

postgres filtering select based on combination of joins

I am having trouble filtering a request that depends on a combination of child elements (one-to-many relationship) Please consider the following example. CREATE TABLE sample_container ( id serial ...
3
votes
1answer
91 views

PostgresSQL: Get single attribute of UDT in SELECT statement

I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work. Creation of type ...
3
votes
1answer
623 views

Tricky selection of grouped rows, selecting based on values of two distinct but related rows

Say I have a table with the following information (sorted here for display purposes) taskid | reference | centreid | date_out --------------------------------------------------- 10001 | ...
3
votes
2answers
4k views

How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values?

I'm running MySQL 5.0.88 (Coldfusion8) I have a product search which I'm querying number-of-results as well as min/max prices/rebates across the product table. I also want to include a string of ...
3
votes
1answer
227 views

Combine - Select Into, with Insert Into Select

I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables. I'm automating a coworker's sql scripts. ...
3
votes
1answer
5k views

how to fetch different data on compare rows of two tables

I have two tables in my database, one is user_app table in which user info are stored, table are as: +--------------+--------------+------+-----+-------------------+-----------------------------+ | ...
3
votes
2answers
488 views

Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments?

In my recent project which is about social networking for an Asian country, I'm in doubt whether I use the below SQL statement for counting records: SELECT COUNT(id) shareLikeNo FROM ...
2
votes
5answers
303 views

How define select, if bridge pair may have 3 players?

Normaly a pair has 2 players, but if there is an odd number of players at the club, one 'pair' has 3 players. Table, members: pair, player_id 1 1 1 2 2 3 2 4 3 5 3 6 4 7 4 8 5 9 5 10 6 11 6 12 6 ...
2
votes
3answers
667 views

How to query efficiently from Postgres to select special words?

Let's say that I have a table called words with very many records. Columns are id and name. In the words table I have for example: 'systematic', 'سلام','gear','synthesis','mysterious', etc. NB: ...
2
votes
3answers
255 views

Using expressions in the SELECT list to thin down a CASE statement

I have a query I'm working through rewriting - it has a huge bunch of case statements in the select list that all basically look like this: select Column1 = case when (Something = 2) and ...
2
votes
3answers
73 views

Platform independent select ordered by primary key

Is there a reasonably standard way to perform a select on a single table and have the result set ordered by primary key without having to specify the primary key condition explicitly? Ideally I wish ...
2
votes
1answer
2k views

SQL Select WHERE row updated

USE [RF_WORLD] GO /****** Object: Trigger [dbo].[lastdeathtrigg] Script Date: 08/20/2012 10:52:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ...
2
votes
1answer
550 views

How to avoid duplicating information in a series of nested SELECT statements?

I'm working on an SQL query that puts together customer history based on past purchases (the industry is venue ticketing). The report will be LARGE, something like 80-90 columns, with one column per ...
2
votes
2answers
1k views

Is there a benefit/drawback to adding LIMIT 0,1 when performing SELECT on unique key field?

I am using a MySQL database and perform many select like these: SELECT MyField FROM MyTable WHERE MyKey = 'some_value'; The purpose is to retrieve a field a value if the corresponding row exists. ...
2
votes
3answers
6k views

Return int value from boolean field in SQL select

Is there a way to return an int value for a boolean field in a sql select statement?
2
votes
1answer
7k views

Multiple “GROUP_CONCAT”s with WHERE clause

In a table as id name type info 1 BMW car yes 2 Reno car no 3 IBM electronics no 4 Sony electronics yes 5 Mazda ...
2
votes
2answers
1k views

Select rows with repeat data within certain timeframe

As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on ...
2
votes
1answer
253 views

Difference data between adjacent rows

I have a table (called Visits) with ReqTime DATETIME NOT NULL column AND Duration INT UNSIGNED column. Duration should be set to the difference of ReqTime for two adjacent rows. But Duration is not ...
2
votes
4answers
836 views

Null Or Multiple Parameters When Querying

I have a stored procedure that has a parameter that must take a comma delimited varchar list of values that can be turned into a table of values or can be NULL, such as @Values VARCHAR(10) = NULL ...
2
votes
2answers
1k views

Optimized query to fetch a record from 10 million entries

A table has 2 fields | UniqueKey | TimeStamp | ------------------------- | xfsddddq | 1024125412 | | xfstttdx | 1024125413 |< | xfsdxxau | 1024125415 | Estimated, there are 10 million ...
2
votes
2answers
71 views

Are these extra Index fields needed?

I've designed a table as below for users who register a quiz: CREATE TABLE `registered_quiz` ( `rq_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `user_id` smallint(5) unsigned NOT NULL, ...
2
votes
2answers
1k views

How expensive are SELECT FROM LIKE statements from a design perspective?

I have a situation running out of control regarding an ever increasing number of small static files I need to access randomly in a large directory structure. I MUST reduce the number of those files ...
2
votes
2answers
306 views

How do redundant indexes impact query performance?

The indexes are affecting the ADD/UPDATE operations however I was wondering whether the redundant indexes will affect the SELECT query performance or not? How the redundant indexes are handled by ...
2
votes
1answer
184 views

Query table A for any rows that are like any search term in table B

I need some help writing a select statement (I still don't think in sets!). Here's the scenario: I have table A with column "Name varchar(20)". I have table B with column "SearchTerm varchar(20)". I ...
2
votes
1answer
227 views

Search a Redis Database

Imagine having a tun of hashes in a Redis Database, each representing a user object. How would you get the user with for example the lowest registration date? The user where ts_registered is the ...
2
votes
1answer
3k views

MySQL query to evaluate ranges in date and time elements of a datetime column separately

TABLE EventID, EventTime (DATETIME) 1 2013-09-29 23:55:00.0 2 2013-10-01 00:05:00.0 3 2013-09-29 23:55:00.0 4 2013-09-29 23:45:00.0 5 2013-10-02 23:05:00.0 6 ...
2
votes
1answer
95 views

How to select nodes where all children is satisfied?

I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on. ...
2
votes
1answer
91 views

JOIN 2 tables, and query data from them 3 times

Basically I have 2 tables: post_meta and posts. post_meta has a post_id, key and value: post_id key value 6343 make Chevy 6343 model Volt 6794 make Chevy 6794 model ...
2
votes
2answers
114 views

HELP with SQL WHERE syntax

I have to pull out a range of entries of a database, all based around two pieces of data. In itself thats fine. An example of the data is: Record ID Tags 1 1423 Day, Sun, Warm 2 ...
2
votes
1answer
368 views

Select consecutive number of rows starting from some number

What I need is to select 100 consecutive rows from a table, starting at some defined point without ordering data. As far as I can observe, inserted data to a table is stored in no order but is ...
2
votes
1answer
3k views

Oracle error “too many values”, must be solved with nested SELECTs

I have hit upon a "too many values" error in Oracle, when working with some intricate nested SELECTs. A simplified description of my problem, on a toy problem describing the hierarchy in a company. ...
2
votes
3answers
2k views

Does the MySQL primary key affect the efficiency of a SELECT query?

Does the primary key serve any purposes apart from uniquely identifying a specific row? For example, a table with an autoincremented primary INT key could greatly aid in search due to the option of ...
2
votes
2answers
4k views

SQL Xml returning empty root tag on empty record set

I am working on xml explicit to generate user defined nodes in xml output using Sql Server 2005 Express edition. The below code works fine, just that i couldn't manage to generate a empty node when ...
2
votes
1answer
42 views

MySQL - Get Minimum Value, Data From Multiple Tables

Good evening all, first post on the DBA forums. I am having real issues working out how to accomplish a seemingly simple task within MySQL as a model for PHP code later on. I am not exactly a MySQL ...
2
votes
2answers
477 views

show table name + number of records for each table in a mysql innodb database

How to list all tables in the current database, together the number of rows of the table. In other words, can you think of a query to come up with something like this in mysql? ...
2
votes
1answer
104 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
124 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
307 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
1k 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
1answer
5k views

How do I select items from a table where a single column must contain two (or more) values?

I have a MySQL database table that references different words and their locations in documents. I want to return the IDs of documents that contain all of the words. Here is an example table. docid ...
2
votes
1answer
784 views

How can I make a select statement get blocked?

Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables. To prepare the ...
2
votes
1answer
325 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
141 views

Search within same ID accross several rows

My table has the following structure: +----+---------+------------+-------------+ | id | word_id | meaning_id | sentence_id | +----+---------+------------+-------------+ | 1 | 1 | 15333 | ...
2
votes
1answer
2k 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
390 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
50 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, ...
2
votes
2answers
53 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 ...