DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

2
votes
1answer
3k views

How do I completely remove MySQL from a 64-bit windows 7 machine?

I'm new to MySQL and struggling to find a version and workbench which is stable on my 64 bit Windows 7 machine. I've decided to attempt to completely remove MySQL from my machine and to restart the ...
2
votes
2answers
1k views

Running a CTE query in a loop using PL/pgSQL

I'm trying to execute query that is repeatedly called in a loop using plpgsql -the loop iterates over another table (named coordinates) that contains top left and bottom right latitude/longitude ...
1
vote
0answers
135 views

Use of hash functions in group by?

I'd use a listagg() to generate a list of components a certain customer has and compare it to another list generated the same way somewhere else to detect inconsistencies. The problem is that ...
1
vote
2answers
116 views

How can I improve this query

I have the following table: CREATE TABLE `twitter_relationships` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `source_twitter_id` bigint(20) NOT NULL, ...
2
votes
4answers
158 views

SELECT Statements only please

I have a user that wants to use SQL Server Management Studio (SSMS) to run SELECT statements. I don't want him to be able to modify data. I can't regulate his permissions through Windows ...
1
vote
3answers
135 views

Query missing one date; only happened today

This should be pretty reproducible I hope. I'm on Oracle 11g. This query, which I use as part of another query, is acting strangely: SELECT TRUNC(SYSDATE - ROWNUM) DATEITEM ...
2
votes
0answers
517 views

SQLite Performance Tuning

I have the following query, which fetches data from only one table. It is a query which is supposed to return data for an autocomplete function. Autocomplete data can be in text1 or text2. The ...
1
vote
1answer
54 views

Query from database

I have a table named mip. The table has primary key id, a foreign key product_id and a column usage_type. product_id is a primary key id to a table named product. It is one to many relationship from ...
0
votes
0answers
249 views

How do I get the date that each user last logged onto the server / database in SQL Server 2005

Currently, I have the following code that gets the DBNAME, USERNAME, ROLE_NAME for each user on the database. I need to get the last logged in date for each user to fill in the LASTLOGIN_DATE. ALTER ...
3
votes
2answers
1k views

Unable to disable SQL statement logging in PostgreSQL 9.1

I am running PostgreSQL 9.1 on Windows Server 2008 R2. I enabled SQL statement logging to the stderr log by modifying my postgresql.conf file, but now I can't seem to disable the logging without ...
2
votes
1answer
508 views

Oracle sequence generation performance during persists of large data sets

In the context of an Oracle sequence, when persisting > 200 million records, is there a performance hit on the database if the sequence cache is the default value of 10000? That is to say does ...
1
vote
1answer
1k views

Convert spatial points data to line data for use in Line Layer in SQL Server Reporting Services (SSRS)

I have a query that returns the fields Order, Lat, Long, SpatialData, <OtherStuff>. SpatialData is a point. I want to create a line layer that draws lines between my points based on the order ...
1
vote
1answer
424 views

The best way to log access to SSRS reports

We have a number of SSRS reports created using BIDS 2008 on SQL Server 2008 r2. The business wants us to log access to these reports. For example who and when run the Report A. What is the best way ...
1
vote
1answer
152 views

Does MySQL compress the stored data?

When I export a database in MySQL, it gives me a .sql file. Let's say this .sql file is 80 MB. Are they compressed in order to reduce disk space usage? Is that 80MB real physical-storage size?
2
votes
2answers
4k views

Parallel inserts cause deadlock on DB2

The scenario is the following: The application attempts to insert into the same table from two parallel threads making 4000 insertions in each thread (separate transaction per thread). It causes the ...
2
votes
1answer
93 views

What are the effects of GRANT DELETE?

What is the difference between: GRANT DELETE ON TABLE1 TO ROLE1 and GRANT DELETE TO ROLE1 One of our DBAs executed the second version by mistake. I want to be certain I understand all the ...
1
vote
1answer
484 views

Can I install sql server on Win XP machine with user profile on D drive?

Is it possible to install sql server on a windows xp machine where the user profiles are created on a non system root directory, that is, on D drive rather than C drive? I am trying to install SQL ...
0
votes
3answers
2k views

How to attach .mdf file to SQL Server 2005 without an .ldf file?

I want to attach a database to SQL Server 2005 but I have only its .mdf file How can I attach it?
3
votes
2answers
889 views

Deprecating SQL Server, need to redirect clients to new server

I am in the process of deprecating an old SQL Server server and replace it with a new server. My old SQL Server is a named instance called: SQL\Development My new SQL Server is a default instance ...
1
vote
2answers
3k views

Move postgresql database from one server to another

I have created a (spatial) database at my home which is several gigabytes and I want to move the whole database as it is from one computer my works computer. Nothing fansy, I will create a new ...
2
votes
1answer
1k views

SQL Query - Set Date Variable by Table/Column Reference

How do I set the @DATE variable by using a table/column reference? Right now I've set it manually as you can see below with the opening: SET @DATE = '2012-03-31 01:00:00'; The table from which I ...
-1
votes
2answers
214 views

Information sources for multiple hierachy trees in a single table

I need some quality sources - books, websites etc. - to educate myself about putting multiple hierarchy trees in a single table using SQL. I'm looking for some good theoretical and practical ...
6
votes
3answers
6k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
3
votes
3answers
6k views

Alternative query to this (avoid DISTINCT)

Note: I work with MSSQL 2008, but I guess it's valid for many others DB engines I have this table "Users": UserID User CountryID 1 user 1 1 2 user 2 2 3 user 3 3 4 user ...
1
vote
1answer
1k views

Production SQL Server High CPU for a Single Query

I have a single query which is consuming a larger amount of CPU than I would expect in SQL 2008. I am not able to determine why the CPU is spiking for this single query. I can trigger an ...
3
votes
1answer
116 views

Slow MySQL query, not sure why

I'm working with an intranet application for property tax collection, and I've hit a snag. The following view runs much slower than I'd expect: create or replace view propertytaxessub0 as select ...
2
votes
1answer
153 views

Is there a name for a query that returns results which have sub headings?

I inhered a set of queries / macros from the previous employee in my position and one of the other people here showed me a query result (output from when the query worked correctly) which had ...
2
votes
0answers
700 views

SQL fulltext accent insensitive

I am using SQL Server fulltext containstable and the column which is indexed has collation Czech_CS_AS, so why when I have word "byt" it returns records with word "být" too. I thought that the ...
1
vote
2answers
6k views

Sql Server agent can not start

I can not start Sql Server agent to create job. Can anyone help me? . I'm using Windows 7 Ultimate , Sql server 2008 Rc2 See this images:
4
votes
2answers
365 views

Database design for part-number generator

Crossposted to Stack Overflow: Database design for part-number generator I'm working on a generator for part numbers, where each space will have a meaning and each space will have a number of ...
2
votes
2answers
9k views

How to Export SQL Server Database To A File For Later Import To Different Computer

I'm using the Sql Server 2012 Import and Export data (64) i'm not sure what options i should be picking to do what i want. I just want to be able to export all the data in the database to a file which ...
2
votes
1answer
908 views

Find which numbers in [1, 161] are not in the result set?

How to find which two number in [1, 161] is not in the result set? Can I find this two numbers using sql command? mysql> select blog_id from wp_blogs; ... | 149 | | 150 | | 151 | | ...
2
votes
1answer
4k views

execute() vs executeQuery() in MySQL Connector C++

With MySQL Connector C++, I can use the sql::Statement::execute() function if I am going to modify the database (INSERT, UPDATE, DELETE, MERGE), or the sql::Statement::executeQuery() function if I am ...
2
votes
1answer
790 views

Oracle With Statement and Delete Together

I am trying to use WITH statement and DELETE together. But I get ORA-00928 error. Basically my code is like following WITH TO_DELETE AS ( SELECT TableKey FROM SOME_TABLE WHERE SomeConditions LIKE ...
1
vote
3answers
812 views

Restoring an entire SQL Server database instance

I have a server that we will be completely reformatting then rebuilding. Everything will be rebuilt exactly as it is now - same filenames, same directories, etc. After SQL Server is installed, I ...
2
votes
1answer
2k views

change encoding of database from latin1_swedish_ci to utf8

I have an huge database in latin1_swedish_ci. Now i need to convert all data to utf8 collation. My question is about the consistency of the information. When I do this change it is possible corrupt ...
1
vote
3answers
615 views

Compare two tables amount with respect to priortization of FundType

I have two tables one is PaymentDetail and another is Ledger Here i want to prioritize paymodetype in manner that cash should be considered first for R,A and then I FundType, then Credit Card, ...
1
vote
1answer
452 views

Merge 2 tables and fetch data one by one

I have two tables: product & offers. products has (prod_id, category_id, prod_name, brand, price, status) offers has (offer_id, prod_id, offer_type, discount_amt, validity) I want to display ...
3
votes
2answers
323 views

Do MySQL sub-queries essentially use as much overhead as separate queries?

If I have a query like this: SELECT name FROM ( SELECT * FROM `table` WHERE id IN (1,40,300) ORDER BY AnotherColumn ) From a bandwidth and overhead standpoint, is that the same thing as doing ...
4
votes
3answers
50k views

How to select distinct for one column and any in another column?

I need to query an SQL database to find all distinct values of one column and I need an arbitrary value from another column. For example, consider the following table with two columns, key and value: ...
1
vote
2answers
290 views

User-defined sort order for a query

I'm storing user preferences for sort order in a table: $queryHistory = "SELECT orderby,ascdesc FROM user WHERE UserID= ".$id; $resultHistory = doQuery($connection, $queryHistory); $numHistory = ...
1
vote
1answer
256 views

query optimization delete nested query [closed]

Can the following query be optimized ? DELETE FROM COLOR_TAGS WHERE COLOR_PROJECTID = $id AND NoteID IN( SELECT ID FROM note_note WHERE BibID=$bid AND isdel=0);
4
votes
2answers
32k views

How do I use the IF…ELSE condition in a WHERE clause?

I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT. Should this work? CREATE OR REPLACE package body If_Else_Pack IS PROCEDURE Moving ...
5
votes
1answer
1k views

Mysql sub query returns more than 1 row

SELECT * FROM wp_posts WHERE ID IN ( (SELECT courses FROM wp_category WHERE CatID =401) OR (SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) ...
4
votes
3answers
25k views

Selecting two values from the same table but under different conditions

I want to grab a value from a table into two different columns for different values from the same table. Use this query as an example (notice how the select is on the same table aliased as 2 different ...
3
votes
1answer
83 views

linking databases for reporting

I am attempting to design a simple reporting feature for some financial market data. I have a few sets of data, and I am trying to figure out what will be the best way to organize the tables, in ...
2
votes
1answer
3k views

Why Is oracle ignoring my index?

This query is not too complex: select trunc(created_date, 'MONTH') as created_date, al.op_name, al.region, al.alarm_type, COUNT(1) as total_new, SUM( (SELECT ...
6
votes
3answers
2k views

Should order of column in where clause be same as the index order?

I was reading this use-the-index-luke.com which explains in detail how indexes work. One of the things this person has re-iterated that the order of indexes matter a lot and to make the query fast the ...
1
vote
1answer
1k views

Trying to Build TreeView Query in T-SQL

I have parent records in two tables, Table1 and Table2. Some records in Table3 are children of Table1, and some are children of Table2. I would like to build a query that I can use to populate a ...
3
votes
1answer
597 views

Retrieving queries when the hour of starting timestamp is less than the hour of ending timestamp

I have a CTE-based query in which I retrieve hourly intervals between two given timestamps. My query works as following: Getting start and end datetimes (let's say 07-13-2011 10:21:09 and 07-31-2011 ...