Все вопросы: [sql]
10 859 вопросов
SQL Server Union of a single label header and a boat load of data columns
I have a SELECT * FROM #tmp and it returns 131 records. I wish to prepend this with a label so the result is format=transaction, followed by the rows each containing 131 columns. It is cumbersome to do the following; is there a nicer method? SELECT 'format=transaction', NULL, NULL, NULL, NULL, ...
How can I SELECT 16 records at random from a range of rows in MySQL?
I want to display a list of 16 of the most popular items in my database, but I want that list to be different every time. So from say, the top 50 downloaded items, choose 16 at random and return that in the result. Is that possible with just one query?
Join instead of correlated subquery
CREATE TABLE BlogPosts ( PostID INT PRIMARY KEY not null, PostTitle NVARCHAR , BlogID int, TotalComments int ) May this query be simplified with any Join instead of correlated subquery? SELECT TOP 5 * FROM BlogPosts as t0 WHERE t0.PostID = (SELECT TOP 1 t1.PostID FROM BlogPo...
How to do the equivalent of a 'Tsql select into', into an existing table
using tsql, sqlserver 2005. I would like insert records from table table2 into an existing table table1 as easily as I could enter it into a new table table1 using: select facilabbr, unitname, sortnum into table1 from table2 Any ideas?
SQL Server Script to create a new user
I want to write a script to create a admin user ( with abcd password ) in SQL Server Express. Also I want to assign this user admin full rights.
MySQL conditional SELECT statement
If there are records that have a field containing "X", return them, else return a random record. How the heck do you do this?
SQL/MySQL structure (Denormalize or keep relational)
I have a question about best practices related to de-normalization or table hierarchy relationships. For a simple example, let's say I have an app that allows a user to make a payment for an order. I save the order information in the orders table, and I have another table for the payment called ...
UNION ALL versus CONNECT BY LEVEL for generating rows
I was wondering which is a better/faster/more efficient way of turning arbitrary strings into columns: UNION ALL SELECT my_field, CASE WHEN my_field = 'str1' THEN ... ... END, ... FROM ( SELECT 'str1' AS my_field FROM DUAL UNION ALL ...
Tracing SQL queries created by Ruby-on-Rails
In the web application I am currently developing, I have quite a few database queries being performed. I would like to know what parts of the code are producing these queries so that I can perhaps refactor the code to reduce them. Is there an easy way to do this? Typically, the database queries ...
Implementing and indexing User Defined Fields in an SQL DB
I need to store a large table (several millions or rows) that contains a large number of user-defined fields (not known at compile time, but probably around 20 to 40 custom fields). It is very important (performance-wise) for me to be able to query the data based on those custom fields: i.e. "Sel...
Avoiding full table scan
I have the following query which obtains transactions from the transactions table and transaction detail. Both tables have a big amount of entries, so this query takes a while to return results. SELECT * FROM transactions t LEFT JOIN transac_detail tidts ON (tidts.id_transac = t.id); However,...
How can I set an alias name in SQL Server
IF RTRIM(@GLDD_LEDGER_CODE)= '00' BEGIN SELECT @SYS_REM =GLMA_ACCT_NAME FROM GLAS_GL_MASTERS WHERE GLMA_COMP_CODE = @COMP_CODE AND GLMA_LDGR_CODE = '00' AND GLMA_DEPT_NO = SUBSTRING(@GLDD_ACCT_CODE, 2, 2) AND GLMA_ACCT_CODE = SUBSTRING(@GLDD_ACCT_C...
How to copy a table schema and constraints to a table of different database?
What SQL can be used to copy the schema of a specified table to a table in different database?
Help in writing Query for hall booking date availability search
I need help in writing query to find the time slot availability for a hall booking system.. details are given below I have a Hall table which stores the hall details and HallBooking table which stores the start and from time of the bookings done.. Hall - HallId - Name HallBooking - HallBooking...
SQL use GROUP BY such that it results in the original SELECT
I have a SQL query that is something like SELECT SUM(price) FROM budget GROUP BY {{PLACEHOLDER}} where {{PLACEHOLDER}} will be replaced in the code. Is it possible to replace it by something that will result in the same output as the following statement? SELECT price FROM budget
SQL statement queries parameters become case sensitive how so?
I'm running a SQL query on my mysql database but for some reason its case sensitive like if an entry exists as 'NAME = Andrew' running a sql query like: SELECT * WHERE NAME Like 'and%' Doesn't work but SELECT * WHERE NAME Like 'And%' does work? WHat gives I'm running a php mysql set up her...
T-SQL: Why is my query faster, if I use a table variable?
can anybody explain me why this query takes 13 seconds: SELECT Table1.Location, Table2.SID, Table2.CID, Table1.VID, COUNT(*) FROM Table1 INNER JOIN Table2 AS ON Table1.TID = Table2.TID WHERE Table1.Last = Table2.Last GROUP BY Table1.Location, Table2.SID, Table2.CID, Table1.VID...
Adding new row datagridview to use default values
I have the following SQL that creates a table and inserts the first row of data without a problem. The default DateTime values are also inserted as expected. CREATE TABLE Jobs ( [Id] int PRIMARY KEY IDENTITY(1,1), [JobName] nvarchar(256) Default 'SomeName', [CreateDate] DateTime...
Can I select distinct/group by characters in a field in SQL?
I'd like to run a query which for a given field will count the instances of a particular character. For example if I had a table called 'Friends' with a 'Names' field containing rows: Edward, James, Mike. I'd get the output: A 2, D 2, E 3, I 1, and so on...
Archiving Reports SQL 2005 Reporting Services
Is there a way to configure the Report Server to Archive the reports to the Report Server or to a path on the File System. I need to save the reports generated as .pdf files.
Calling stored procedure and passing parameter from another stored procedure
This is my stored procedure that deletes rows according to array that was passed to it as XML parameter: BEGIN DECLARE @docHandle int EXEC sp_xml_preparedocument @docHandle OUTPUT, @XMLDoc DELETE Comments WHERE ID IN (SELECT * FROM OPENXML(@docHandle, '/values/value1', 2) WITH (value1 ...
mysql query to dynamically convert row data to columns
I am working on a pivot table query. The schema is as follows Sno, Name, District The same name may appear in many districts eg take the sample data for example 1 Mike CA 2 Mike CA 3 Proctor JB 4 Luke MN 5 Luke MN 6 Mike CA 7 Mike LP 8 Proctor MN 9 Proctor JB 10 Proctor MN 11 Luke MN As you ...
Handling circular data in Oracle's SQL
There is a problem that don't know how to resolve only with SQL (I could do it with PL/SQL, but it should be doable only with SQL). I have a table (in fact it's not a table but the result of a with query) with contains pair of values like this: column1 column2 --------- --------- v...
How to validate sql query syntax?
java 1.4 Sql server 2000 i am taking input of sql query (for validation of field value against values retrieved by executing sql query) from admin user which will be stored in database and later i will executing sql query corresponding to field.Before inserting sql query in database i want to va...
How do I programatically perform a Modify on all stored procedures in my database in SQL 2008
What I want to do is simulate right clicking a stored procedure an selecting Modify, then execute so that my stored procedure runs. Some of the tables in our database have changed and not all the sp's have been modified. ie old SP = ALTER PROCEDURE [dbo].[myProcedure] SELECT name, address, t...
Pagination with hasMany association cakePHP
I have two tables: Contestant and Votes Contestant hasMany Votes I've tried doing a count(Vote.id) as Votes so I can place it on the recordset and just paginate them but I have no idea where to place it. I did it on the fields array but it gave me the total count of votes regardless of the cont...
Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?
I have my business-logic in ~7000 lines of T-SQL stored procedures, and most of them has next JOIN syntax: SELECT A.A, B.B, C.C FROM aaa AS A, bbb AS B, ccc AS C WHERE A.B = B.ID AND B.C = C.ID AND C.ID = @param Will I get performance growth if I will replace such query with this: SELECT ...
how to select a particular table from a storedprocedure which returns multiple table?
I have a storedproc which has multiple select statement. When it is executed in sql server , it returns multiple tables. I need a query which will select a particular table from the storedproc e.g. sp_help. Please help.
Does this query guarantee me a 'race free' PK value?
I was just reading How to avoid a database race condition when manually incrementing PK of new row. There was a lot of good suggestions like having a separate table to get the PK values. So I wonder if a query like this: INSERT INTO Party VALUES( (SELECT MAX(id)+1 FROM (SELECT id ...
MYSQL SUM GROUP BY
I'm working on a high school grading system. At my school, grades can be changed by reworking problems and I store these changes with dates. I have a function that properly returns averages because the most recent grade is flagged with a "current" field with a value of '1'. I'd like to make the...