Tagged Questions

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

4
votes
1answer
2k 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
87 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 ...
22
votes
1answer
3k 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 * ...
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
2answers
858 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 ...
7
votes
2answers
13k 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
294 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 ...
13
votes
2answers
60k 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
721 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
8k 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 ...
5
votes
2answers
14k 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
674 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
4k 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
367 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
302 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 ...
7
votes
4answers
2k 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
188 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 ...
8
votes
3answers
5k 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
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?
1
vote
1answer
343 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 ...
3
votes
2answers
1k 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 ...
12
votes
7answers
15k 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
256 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
5k 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
162 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
12k 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 ...
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
179 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 ...
1
vote
2answers
99 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') ...
9
votes
5answers
14k 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. ...