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.
0
votes
1answer
1k 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
821 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
...
20
votes
1answer
1k views
USING construct in JOIN clause can introduce optimization barriers in certain cases?
It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.
I mean this key word:
SELECT *
...
0
votes
0answers
164 views
PostgreSQL Function PERFORM [closed]
CREATE OR REPLACE FUNCTION "insertarNuevoArticulo"(nombrearticulo character varying, descripcion text, idtipo integer, idfamilia bigint, artstock integer, minstock integer, maxstock integer, idmarca ...
2
votes
2answers
483 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
2answers
212 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 ...
5
votes
2answers
4k views
How to do a case sensitive search in WHERE clause?
I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings.
Any idea on how to do a case sensitive search in SQL query?
2
votes
1answer
173 views
select for update gives error on indexed column
here is my code:
mysql_query("start transaction");
$q="select max(id) from test for update";
$r=mysql_query($q);
$row=mysql_fetch_array($r);
for ($k=0;$k<100000;$k++)
{
$q="insert into test values ...
8
votes
2answers
22k 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 ...
-3
votes
1answer
311 views
Best practices for finding and updating a handful of records?
Seems like a simple question, but wondering what is the "best" way to do this if you were "dotting your i's and crossing your t's".
7
votes
3answers
2k views
What does the term “projection” refer to in an SQL query (or view definition)
In Oracle's document The Query Optimizer, under View Merging, I found the following information
The view merging optimization applies to views that contain only
selections, projections, and ...
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
434 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 ...
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
...
2
votes
1answer
273 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
2answers
239 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 ...
5
votes
4answers
546 views
Multiple operations using WITH
Is there a way to execute multiple operations using the WITH statement?
Something like
WITH T AS
(
SELECT * FROM Tbl
)
BEGIN
OPEN P_OUTCURSOR FOR
SELECT * FROM T;
SELECT COUNT(*) INTO ...
0
votes
3answers
145 views
How can I select data and the total to cursors?
I have a table like this:
ID | Name
------------
1 | AA
2 | AA
3 | AA
4 | BB
I want to select all rows where Name like 'AA' in a cursor and get the count from this result in another ...
5
votes
3answers
1k views
What is the default order of records for a SELECT statement in MySQL?
Suppose you have the following table and data:
create table t(k int, v int,index k(k)) engine=memory;
insert into t values(10, 1), (10, 2), (10, 3);
When issuing select * from t where k = 10 with ...
2
votes
3answers
2k 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?
1
vote
1answer
286 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 ...
2
votes
2answers
669 views
Does the SELECT statement count into DML?
Looking at the name Data Manipulation Language (DML) I would assume, that all contained statements are actually for manipulating data.
As far as I know the SELECT statement can only be used to query ...
4
votes
2answers
1k views
Has SELECT an INSERT clause?
I know the form
Select * into new_table from old_table
statement. Bur recently I found 2 posts here and here which use it like
SELECT * FROM NhlPlayer
INSERT INTO PlayerBackups
I find nothing in ...
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
...
1
vote
1answer
199 views
Shift db data to another column
I have the following table
id | ev_id | ev_loc_id | a | b | prev_a | prev_b
If I the (ev_id AND ev_loc_id) not exist in the table then I just insert them, otherwise I'm putting ...
6
votes
2answers
2k views
How to use variables inside a select (SQL Server)?
If I want to compute a column and use the result in more than 1 column, how do I do this without making the computing twice?
Example:
SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as ...
1
vote
1answer
147 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
4answers
7k 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
vote
2answers
2k 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
112 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 ...
0
votes
2answers
83 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')
...
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. ...
