An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

4
votes
2answers
397 views

MAX for each subset

I have a table that looks like this: +---------------------------------------------+ | EVENT_ID | ITEM_ID | PERSON_ID | EVENT_DATE | +---------------------------------------------+ | 123 | 1 ...
1
vote
1answer
93 views

Sql DateDiff Question

Select distinct DATEDIFF(ww, GETDATE(), siparis_test) AS Hafta The first day of week is sunday in the above function. How can I change it so that monday is the first day? Thanks in advance.
6
votes
1answer
685 views

What is a residual predicate?

Although I was able to find the definition of predicates online at MSDN: Is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE ...
1
vote
3answers
1k views

How to choose the last recorded added without sorting

I've got this simple table: RecordID (int PK), UserID (int FK), DTime (datetime), Length (int) I need to run a simple query: SELECT TOP(1) * FROM uTable WHERE UserID = @userid ORDER BY DTime DESC ...
2
votes
2answers
307 views

How to get a user's friends' names?

I have a users table and friends table. The user with ID 1 has 4 friends (with IDs 2,3,4,13). I want to get names and surnames of users 2,3,4 and 13. I can to it with standard queries but it will slow ...
5
votes
6answers
413 views

Running a database without constraints

We're looking at utilizing a new proprietary high performance database engine for OLAP and OLTP scenarios. One important aspect of it right now is that it does not support foreign key or other ...
7
votes
4answers
3k views

How do I do a complex GROUP BY in MySQL?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
1
vote
1answer
681 views

Select statement to retrive xml output as below

Hi could someone provide me some clues or solution to retrive record sets like below note: i read the msdn documentation but leads me no where but hair loss :( Just for the assumption think i have 2 ...
5
votes
1answer
1k views

Print Parameters in Dynamic SQL

I've used dynamic SQL for many tasks and continuously run into the same problem: Printing values of variables used inside the Dynamic T-SQL statement. EG: Declare @SQL nvarchar(max), @Params ...
4
votes
1answer
2k views

nest SELECT in INSERT

I'm trying to do something like: INSERT INTO stories (vid, ip, body, timestamp) VALUES ( (SELECT vid+1 FROM stories ORDER BY vid DESC LIMIT 1), INET_ATON('127.0.0.1'), 'test', ...
3
votes
2answers
5k views

Emulate REGEXP like behaviour in SQL

I posted the same on stackoverflow (please, let me know if I have to delete one). I'm working on a DB2 database and as far as I can see regexp is not supported (without additional libraries). So I ...
3
votes
1answer
598 views

Oracle query against SQL Server database giving inconsistent results

We have multiple Oracle 11.2.0.2 databases with og4odbc database links to a SQL Server 2008 database. A particular table in the SQL Server database has two Varchar(36) columns. The following ...
3
votes
1answer
4k views

How do I calculate values based on the previous row after skipping the first 12 rows?

I'm looking for help to write this query. I have included some sample data below. My initial premise is that all the values from the 13th row onward will be dynamic and the first 12 rows will be ...
9
votes
4answers
8k views

Methods of speeding up a huge DELETE FROM <table> with no clauses

Using SQL Server 2005. I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...
3
votes
2answers
1k views

Select XML values when attribute involved

I have a table that stores XML values in field: OtherData. An XML value from one row: < file path="C:\WINDOWS\system32\svchost.exe" /> I would like to select all rows that have an ...
3
votes
2answers
333 views

Searching for a Data Dictionary for SQL or Oracle

I am looking for metadata specifications (data dictionary) for SQL Server and Oracle databases. By metadata map, I mean a definitive description of what objects make up database model and their ...
1
vote
2answers
667 views

How to group a resultset by day?

I have a table with a LastUpdated field that is DateTime and wanted to know how can I group months of data collected by chronological days such that I can derive the number of records in each day ...
2
votes
3answers
234 views

multi-table mysql query

I am trying to make a multi-table query that I am not quite sure how to do properly. I have User, Message, Thread, and Project. A User is associated with Message/Thread/Project as either the Creator ...
2
votes
1answer
655 views

Query between two related tables

I have two tables. One of them (user_profile table) has a field named "user_profile_id" and the other one (user_friend table) has two fields named "user_profile1_id" and "user_profile2_id" which are ...
5
votes
2answers
4k views

The SELECT permission was denied on the (View) object after explicit grant select on view to user

I have a database with a View Named '1098Statement' We are doing testing for an application with user name 'appuser'. I have tried the following solutions to granting select on the view and keep ...
5
votes
4answers
549 views

Group data by non-unique keys by distinct time range

I have data in my table like following... PROJ_NBR STATUS START_DT AL20 AC 1/14/2010 4:31 AL20 AC 1/14/2010 4:32 AL20 AC ...
1
vote
1answer
992 views

SQL Server 2005: Integration Services Stored Packages is empty. Why?

I am trying to import, export SSIS packages. I know they are there. When I query msdb.dbo.sysdtspackages90 I get 22 packages. But when I connect to Integration Services on the server, the stored ...
8
votes
3answers
4k views

How to differentiate between SQL and PL/SQL?

I know the question might sound too stupid, but I never understood this part. SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for ...
1
vote
1answer
8k views

Where all can the /*+ PARALLEL */ hint be used?

It works with SELECT and UPDATE statements. Are there some more statements with which it works? I was able to use it with create, but am not sure if the hint was actually considered. SQL> create ...
2
votes
2answers
3k views

In Oracle, how to globally enable parallel execution?

In Oracle 11g, is there a way to tell the database to use parallel execution whenever possible by setting a flag etc, rather than using PARALLEL hints with every SQL statement?
3
votes
2answers
127 views

I'm performing a post-mortem on an Oracle database that's crashed its' host. Where should I begin?

I have an Oracle database (10.2.0.1.0 I believe) on a Windows 2003 server that I believe has successfully crashed the OS itself twice. Debates on platforms and stability aside, the evidence suggests ...
0
votes
1answer
609 views

One record, multiple updates in one query (MySQL)

I run into a problem for which I didn't find an answer anywhere: If I have a table in which one specific value(here amount) from multiple records can add into a target record(from the same table), can ...
5
votes
2answers
515 views

Date alignment and pair matching extraction best done with TSQL or C#?

For starting with: my title sucks, so help me figure out a new one? I can't post all the SQL here (over 30k characters for the lot of it), so I stuck it on pastebin.com The problem: I get an XML ...
1
vote
3answers
255 views

Selecting an entry that spans adjacent months

So i have a database table that contains the a "event name", "start-date", "end-date" columns which is used in a calendar application im working on. The events get shown one month at a time, and that ...
3
votes
2answers
105 views

How do you handle adding nullable columns to each other in a query?

Currently whenever I write a query that is adding columns that can contain nulls I resort to wrapping each field in isnull or coalesce, such as coalesce(score1,0) + coalesce(score2,0). Is there a ...
4
votes
1answer
340 views

what is the first schema checked?

I am a little confused right now. Can someone answer me with a simple answer to this question. I have a user [user_test] and has [db_owner] role assigned to him for one database [database_test]. ...
13
votes
1answer
2k views

How to get the MAX row

In SQL Server I've always found it a pain to get the max rows for a dataset, I'm looking for a list of the methods to retrieve the max rows with some guidance on performance and maintainability. ...
3
votes
2answers
563 views

Why can't I use newid() in a user-defined table-valued function?

I recently found out that SQL Server 2005 doesn't allow the use of newid() within user-defined functions. Why is this? I have an alternate solution that suits my needs, so I'm not looking for ways to ...
2
votes
2answers
1k views

What is the best way to filter or otherwise manipulate the results returned by a stored procedure?

Often I find myself wanting to filter or order the results returned by execing a stored procedure - for example to order the output of sp_who2 by login or CPU Time, or filter it by host name. What is ...
5
votes
4answers
7k views

Building Dynamic Oracle Where Clause

I'm working on application that uses dynamic query to do a select statement based on user input, after discussing security with DBAs they want me to convert my dynamic select statement into Stored ...
5
votes
1answer
615 views

Case Behavior Changed Between 11.2.0.1 and 11.2.0.2

I have a case open with Oracle and they say they can't reproduce this. Running the following in 11.2.0.1 or lower produces two rows from both of these queries, but in 11.2.0.2 the second query ...
15
votes
3answers
3k views

Traversing tree-like data in a relational database using SQL

Is there a way to traverse tree data in SQL? I know about 'connect by' in Oracle, but is there another way to do this in other SQL implementations? I'm asking because using 'connect by' is easier ...
6
votes
1answer
622 views

adding an order by to this query returns faster than without, why?

Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen? SELECT TOP (500) ...
4
votes
2answers
157 views

Randomizing table contents and storing them back in the table

I have a table with at least a million records in it. These rows were created by a custom app that reads several SharePoint site collections and stores the item urls in the table. Now, since we read ...
1
vote
1answer
438 views

What does HSJOIN mean in an Explain Plan?

I have the following EXPLAIN PLAN results from a query on my DB2 database. 0 SELECT STATEMENT ( Estimated Costs = 5.928E+02 [timerons] ) 1 RETURN 2 HSJOIN 3 [O] ...
4
votes
1answer
4k views

How can I query names using regular expressions?

How could I search for a specific pattern in a column? For example, I like to get all user names that start with either letter A or B. (btw: this is tagged oracle, but it might be interesting in ...