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.
2
votes
5answers
219 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 ...
8
votes
3answers
705 views
Why do I have to select from the dual table?
This works in the major Relation Database Management Systems most likely to appear on StackOverflow/dba.stackexchange, being SQL Server, MySQL, PostgreSQL and SQLite (WebSQL).
select 'abc' abc, 1 ...
1
vote
2answers
400 views
GROUP BY two columns
I want to count two columns, but need to get the results of first column in one row.
SELECT country, COUNT(*)
FROM table1
GROUP BY country, type
This query gives me
country type COUNT(*)
...
2
votes
1answer
179 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 ...
1
vote
1answer
148 views
How to COUNT number of rows with LIMIT?
I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too?
Currently, I use two separate queries to do so as
SELECT COUNT(*) FROM col WHERE CLAUSE
...
3
votes
1answer
150 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 | ...
0
votes
2answers
119 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 ...
2
votes
1answer
540 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. ...
4
votes
1answer
192 views
Select one name per person where spelling variations exist
I have a source table that contains Employee IDs and names. The employee names may be listed multiple times with variations on the exact spelling (see 'source table' for example - note the middle ...
3
votes
2answers
463 views
Select query according to date
We have a table to store the Events.
Table has got a schema like
EventID INT Primary Key
EventName NVARCHAR(100)
StartDate Datetime
EndDate Datetime
So in the front end we want to show the events ...
2
votes
2answers
273 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 ...
3
votes
1answer
798 views
SELECT LIMIT 1 per column value?
Lets say I have the following table
-----------------------------
| user_id | comment |
-----------------------------
| 2 | thats cool |
| 2 | awesome |
| 3 | ...
1
vote
1answer
2k 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
261 views
Merge 2 tables and fetch data one by one
I have two tables: product & offers.
products has (prod_id, category_id, prod_name, brand, price, status)
offers has (offer_id, prod_id, offer_type, discount_amt, validity)
I want to display ...
4
votes
2answers
2k views
How do I use the IF…ELSE condition in a WHERE clause?
I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT.
Should this work?
CREATE OR REPLACE package body If_Else_Pack IS
PROCEDURE Moving
...
5
votes
1answer
268 views
Mysql sub query returns more than 1 row
SELECT * FROM wp_posts WHERE ID IN
(
(SELECT courses FROM wp_category WHERE CatID =401) OR
(SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) ...
1
vote
1answer
809 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
2answers
257 views
WHERE clause is not filtering on LESS THAN and GREATER THAN or BETWEEN
I am trying to select MTR values that are between the values of 0 and 4. What am I doing wrong?
SELECT
DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable
WHERE MyID = 2074163
AND MTR >= 0 ...
2
votes
2answers
974 views
How do I get the current and next greater value in one select?
I have a InnoDB table 'idtimes' (MySQL 5.0.22-log)
with columns
`id` int(11) NOT NULL,
`time` int(20) NOT NULL, [...]
with a compound unique key
UNIQUE KEY `id_time` (`id`,`time`)
so there can ...
1
vote
1answer
341 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 ...
2
votes
1answer
539 views
Simple Oracle query hangs depending on WHERE clause
Not used to using Oracle, but we have a large database where a non-unique query like
SELECT * FROM employees where department = 'HR'
is working, results list up no problem.
But when I do
...
3
votes
2answers
997 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 ...
2
votes
2answers
537 views
Sub-query executes 1 time for a parent query or more?
If i apply a static data in where clause is it faster then applying a sub-query. Example:
(The query returns 5 records as a result set)
SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = ...
7
votes
4answers
893 views
Benefits of using backtick (`) in MySQL queries?
In MySQL we can create queries with or without the backtick (`) symbol. Example:
SELECT * FROM TEST;
SELECT * FROM `TEST`;
Both works fine in mysql-console.
Is there any technical difference ...
1
vote
1answer
525 views
How do I perform a UNION query on a dynamic list of tables?
My database has an unknown amount of tables, named with the same prefix followed by a random string: _ct_<random_string>.
Each of these tables has the same data structure.
I also have another ...
2
votes
1answer
757 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
-- ...
3
votes
1answer
1k 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:
+--------------+--------------+------+-----+-------------------+-----------------------------+
| ...
0
votes
0answers
85 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 ...
1
vote
0answers
85 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 ...
3
votes
3answers
941 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 ...
0
votes
1answer
43 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` ...
3
votes
5answers
227 views
Single query to return counts over different IDs in a single record
select count(title_id)as algodata from titles where pub_id =1389
select count(title_id)as binnet from titles where pub_id =0877
select count(title_id)as newmoon from titles where pub_id =0736
...
4
votes
2answers
1k views
SELECT TOP in MySQL
How can I do this in MySQL?
SELECT TOP 50 PERCENT * FROM table
What's the simplest way to do it without running to separate queries (if possible)?
0
votes
2answers
77 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
0answers
96 views
Can I use MySQL to check for multiple patterns with related elements (like S=2,M=4,L=2)?
Just wondering if this is possible to do in MySQL:
I have two search fields where users can search for sizes&quantities, for example:
S=2, M=2, L=2, XL=4
Right now I'm doing a big construct ...
3
votes
2answers
280 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 ...
3
votes
1answer
762 views
Any disadvantages of “SQL_BUFFER_RESULT” for MySQL SELECT?
I've been researching the usage of SQL_BUFFER_RESULT. Mostly it is referred to as an aid to reduce table lock issues.
It seems to be a good option to use.
However I cannot seem to find any ...
1
vote
1answer
305 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 ...
1
vote
3answers
56 views
How can I query to do this?
I have 2 tables, in feeds I have my feeds and (feeds.ID) is the primary key. second table is items. and feeds.ID is realted to items.feed_ID, so with a simple inner join i can extract the data of each ...
2
votes
1answer
749 views
Getting unique names when the IDs are different (DISTINCT ?)
I've got this table:
id | name
1 | John
2 | John
3 | Peter
4 | Mike
5 | Mike
And I want to get the unique names, getting the latest one in the list. I could go for:
SELECT DISTINCT name ...
2
votes
2answers
195 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 ...
0
votes
3answers
343 views
Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?
I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
2
votes
2answers
66 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,
...
0
votes
1answer
70 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?
3
votes
1answer
137 views
Single AND operator and multiple OR operators
I wanted to select a record_id with a user_id of 1 and category_id can be 1, 2, or 3.
Every SELECT statement should return 1 record.
View my sample table here: http://i49.tinypic.com/2ezjtk8.png
How ...
1
vote
4answers
333 views
cross self join to get more rows - better solution?
I have a table employees:
+----------------+------------------+
| Field | Type |
+----------------+------------------+
| id | int(10) unsigned |
| persons_id | ...
3
votes
2answers
273 views
Select total number of records from common table expression
We are using common table expressions to temporarily save the search results.
WITH Results (
------
)
AS
(
select ---
)
SELECT * FROM ...
0
votes
1answer
996 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
------ ----- ...
4
votes
1answer
761 views
SELECT with large WHERE IN clause taking long time
I'm pretty new at all this so be gentle :).
I have large medical DB where I need to extract data for patients based on selected doctor(s).
Right now I build a patient list by
SELECT patID
FROM ...
1
vote
2answers
68 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
...
