1
голосов
2ответов
154 просмотров

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, ...

1
голосов
3ответов
244 просмотров

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?

1
голосов
3ответов
875 просмотров

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...

15
голосов
3ответов
11736 просмотров

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?

98
голосов
6ответов
210076 просмотров

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.

1
голосов
3ответов
1004 просмотров

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?

1
голосов
7ответов
478 просмотров

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 ...

0
голосов
2ответов
1652 просмотров

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 ...

1
голосов
2ответов
243 просмотров

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 ...

3
голосов
2ответов
1377 просмотров

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...

1
голосов
4ответов
2963 просмотров

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,...

-1
голосов
1ответов
580 просмотров

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...

3
голосов
5ответов
30836 просмотров

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?

0
голосов
1ответов
623 просмотров

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...

0
голосов
4ответов
192 просмотров

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

1
голосов
3ответов
515 просмотров

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...

2
голосов
3ответов
334 просмотров

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...

3
голосов
1ответов
2532 просмотров

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...

0
голосов
2ответов
1574 просмотров

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...

0
голосов
2ответов
121 просмотров

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.

0
голосов
2ответов
1617 просмотров

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 ...

4
голосов
4ответов
18806 просмотров

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 ...

4
голосов
2ответов
382 просмотров

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...

5
голосов
6ответов
19364 просмотров

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...

0
голосов
2ответов
72 просмотров

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...

0
голосов
4ответов
6822 просмотров

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...

61
голосов
7ответов
79665 просмотров

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 ...

0
голосов
2ответов
189 просмотров

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.

0
голосов
3ответов
92 просмотров

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 ...

11
голосов
2ответов
61905 просмотров

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...