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.
1
vote
1answer
38 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 ...
0
votes
1answer
44 views
Order of where clause evaluation
If I have a MySQL table which has content from the last 3 months I actually do the following:
SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
FROM `mDelivered`
where _campaign = 2
and `session` = ...
-1
votes
2answers
57 views
How to do division from select statements in sql server?
I am trying to create a select statement that can return a table of 1 column.
Basically, I am looping through all the records of NewHire, and then using its id, to select stuff from another table. I ...
1
vote
2answers
221 views
SELECTing multiple columns through a subquery
I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them.
SELECT DISTINCT petid, userid,
(SELECT ...
-1
votes
0answers
28 views
Using If statements in stored procedure breaks it [migrated]
This is my stored procedure:
USE [Hires_new]
GO
/****** Object: StoredProcedure [dbo].[ps_selectNewHireWorkPeriodsSQL] Script Date: 05/14/2013 12:59:28 ******/
SET ANSI_NULLS ON
GO
SET ...
0
votes
0answers
20 views
How to merge a select statement with a calculated value as new columns? [migrated]
In my sql server code, I have this select statement
select distinct
a.HireLastName,
a.HireFirstName,
a.HireID,
a.Position_ID,
a.BarNumber,
a.Archived,
...
0
votes
0answers
41 views
How to loop through a select statement? [migrated]
I have this select statement
declare @t table (Percentage float)
DECLARE @acc INT
SET @acc = 1
DECLARE @max INT
select @max = max(HireID) from NewHire
WHILE (@acc <= @max)
BEGIN
IF ...
1
vote
1answer
45 views
Select records that do not have associations outside a certain list
I have a mySQL table of nodes and each node can be associated with many categories. I have a list of category ids that are acceptable (498, 499). I want to select only nodes who do not have ...
1
vote
1answer
30 views
How to add additional tables/fields into a query with multiple inner joins?
Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result?
SELECT ...
1
vote
1answer
90 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 ...
1
vote
4answers
6k views
Retrieving row count without using Count function
I wrote a query below which shoots me a syntax error why would it do so,
SELECT MAX('Row') FROM
(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS)
Error Desc:
Incorrect syntax near ...
-1
votes
3answers
104 views
Add Contents in a Column and make them 0 [closed]
http://sqlfiddle.com/#!3/96f11/3
In the above fiddle, I need the required output in the Fiddle.
ie., The UserID column in Filtered Table and Main table are equal.
I need to get the Amt column data ...
1
vote
2answers
91 views
Getting SELECT to return a constant value even if zero rows match
Consider this select statement:
SELECT *,
1 AS query_id
FROM players
WHERE username='foobar';
It returns the column query_id with value 1 along with a player's other columns.
How would ...
0
votes
1answer
63 views
Optimization of a select statement
I'm using MySQL and have a table user_data like this:
user_id int(10) unsigned
reg_date int(10) unsigned
carrier char(1)
The reg_data is the unix timestamp of the ...
0
votes
1answer
96 views
Error “column does not exist” in a SELECT with JOIN and GROUP BY querry
I'm using PostgreSQL 9.1 with a Ruby on Rails application.
I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id ...
4
votes
5answers
6k views
How can I use a default value in a Select query in PostgreSQL?
I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this?
E.g. ...
0
votes
2answers
89 views
MySQL: Join one Row in a table with two Rows in other table
In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations):
`users` `user_login`
+--------------+ ...
1
vote
2answers
29 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 ...
-4
votes
2answers
140 views
Get Hierarchial Data in Self-Referencing Table (Parents , Childs) [closed]
In the following Fiddle,
sqlfiddle
I will pass a sno and I need all the parents of that sno in a table. And all the children of that sno in another table
Please refer to this question. Since ...
-1
votes
2answers
102 views
Select query having count and variable
I have a table like the one below.
I need to form a select query. I'm going to pass a RefID like
SELECT SNo FROM Register WHERE RefID = 1
And beside the SNo in the Statement, I need to have ...
2
votes
2answers
58 views
How to JOIN two table to get missing rows in the second table
In a simple voting system as
CREATE TABLE elections (
election_id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255),
CREATE TABLE votes (
election_id int(11),
user_id int(11),
FOREIGN KEYs
for ...
1
vote
1answer
68 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` ...
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)?
3
votes
1answer
38 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 ...
2
votes
1answer
117 views
Selecting minimum value using a subquery
I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating.
We have a table for prices ...
2
votes
1answer
41 views
Search within same ID accross several rows
My table has the following structure:
+----+---------+------------+-------------+
| id | word_id | meaning_id | sentence_id |
+----+---------+------------+-------------+
| 1 | 1 | 15333 | ...
5
votes
6answers
6k views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
7
votes
2answers
19k views
oracle - list users with access to certain tables
I'm sure this has been asked before but I can't seem to find the relevant details for the following.
Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
0
votes
2answers
67 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 ...
3
votes
2answers
283 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 ...
0
votes
2answers
88 views
SELECT only values THAT are different
I have two instances user1 and user2. They have colors
u_id | color<br>
1 | red<br>
1 | green<br>
2 | red<br>
2 | blue<br>
I need a mysql request, that ...
0
votes
0answers
24 views
table(s) sync question [closed]
i have a sync script that queries several different wordpress databases every 15 minutes to keep a main "feed" table up to date. it totals approximately 50,000 queries (99% of them are Selects, for ...
3
votes
2answers
3k views
Can someone explain why select with nolock will query a potion of updated data?
I was reading the answer from here (from stackoverflow, I think should ask in here)
NOLOCK means placing no locks at all.
Your query may returns portions of data as of before UPDATE and
...
0
votes
2answers
116 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
1answer
819 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 ...
2
votes
3answers
316 views
Count consecutive rows in mysql
I would like to get the count of consecutive rows in MySQL. It is a large database I try to avoiding the joins. Here gadget_id means vehicle and every 20/30 second the vehicle sends the current ...
4
votes
1answer
87 views
Does the order of fields in SELECT query matter when using composite indexing?
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I ...
3
votes
3answers
103 views
Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates
We have an unusual database structure (I leave the reasoning for another question), that in it's current form requires a regular truncation and recreation of allot of row (about 1 million).
We're ...
1
vote
2answers
279 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
5k views
How to cast an integer to a boolean in a MySQL SELECT clause?
I'm new here so be kind to me. I have the following scenario:
I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value ...
1
vote
1answer
179 views
How to re-arrange rows to best fit within groups?
I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100.
mixing_order id num_items cat order_in_cat cumulative_sum folder
1 ...
2
votes
1answer
71 views
Select columns from 4 tables
First thing, I am totally new to SQL. I saw some tutorials, videos, tried searching but didn't find what I needed. I know it has asked before, but I couldn't find it.
What I need to find is basically ...
1
vote
1answer
111 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 ...
3
votes
2answers
114 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 ...
2
votes
1answer
813 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 ...
0
votes
2answers
150 views
How to mix pairs of rows without JOIN (in a temporary table)?
I have a TEMPORARY table containing pairs of key/value as
id col1 col2 type pair
1 key1 key2 key 1
2 value1 value2 value 1
3 key3 ...
0
votes
1answer
92 views
Retrieving a fixed range of rows from Postgresql
I have a query:
select qid,ansid,ans from table1 where askerid='something'
This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the ...
2
votes
0answers
210 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
549 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
...
2
votes
1answer
628 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 ...
