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
5k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
1
vote
1answer
131 views

Can I use Try_Cast to give me a query result column with different datatypes?

I have a column that is varchar(max). It contains mostly valid XML. But sometimes it has invalid xml (that I still need). I am trying to make a view for this table that allows the value to display ...
2
votes
2answers
150 views

What can I do to make mysql use the expected indices?

What can I do to make mysql use the expected indices? I've got 4 tables, two containing resources, and the others containing historical changes. One pair uses indexes correctly, the other doesn't, ...
3
votes
0answers
299 views

SELECT COL_NAME(969262708, ORDINAL_POSITION) returns a NULL value in one of the rows

So I've got a Stored Procedure that's copying data from one table to another. It calls this other SP to get the column names. I didn't write this darn thing, but here's that part: ALTER PROCEDURE ...
6
votes
1answer
321 views

Landed as BI, but databases are a big WTF, what to do?

Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
1
vote
2answers
435 views

Challenge. Split database 500 records in two smaller datasets, randomly

I have a database with 500 records. I want to split these records to 75% and 25% *randomly*in order to use the different datasets for training and testing to machine learning algorithms. Does anyone ...
1
vote
1answer
106 views

Big jump in search time for Postgres Index query for results with high selectivity

I am doing some performance comparison of databases and lucene for full-text searching. So I use Postgres to create an Index for the data to search: CREATE INDEX bodies_index ON bodies USING gin ...
1
vote
4answers
15k 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
1answer
677 views

Restoring SQL Server 2012 database onto 2008

I am trying to restore a SQL Server 2012 database after I set the compatibility level to SQL Server 2008 by following the instructions as listed on: ...
0
votes
4answers
3k views

Database Restore Failed Due to Incompatible Version of SQL Server [duplicate]

I am working on different two SQL Server , I need to transfer database from one server to another but problem is both server has different version. Below is Image of Error message. Using SELECT ...
1
vote
0answers
58 views

MySQL not using resources?

Hi I have a decent setup: 16Gb RAM and a 4 cored CPU. I'm on a 64-bit Windows OS with MySQL Ver 14.14 Distrib 5.5.24 Win64 (x86). I am also using InnoDB. The query I am using to test the setup is a ...
3
votes
1answer
740 views

Create SQL Script for Statistics

I wanna create script my whole table, including all statistics and indexes. In SSM, When I right click table and create script, it gives me script for Indexes for not for statistics. If I right ...
1
vote
1answer
205 views

Separating tables vs having one table

At the moment I have a table setup that looks somewhat like this: create table tbl_locationcollections ( id int(11) PRIMARY KEY IDENTITY, --(Primary Key), name varchar(100) not null, ...
-1
votes
3answers
115 views

Add Contents in a Column and make them 0 [closed]

http://sqlfiddle.com/#!3/96f11/3 In the above fiddle, I need the required output in the Fiddle. ie., The UserID column in Filtered Table and Main table are equal. I need to get the Amt column data ...
4
votes
2answers
800 views

How to sync a database with an external full text engine?

How do you keep your external full-text engine (Lucene, Solr, etc) in sync with your database data? For example, keeping track of inserts, deletes, updates. My current solution involves a big union ...
4
votes
1answer
1k views

Postgres: count(*) vs count(id)

I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*). My question is ...
3
votes
2answers
4k views

Creating a PostgreSQL SERIAL column using pgAdmin3

When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)? Should we keep this type defined by pgAdmin? Manually create the table with SQL ...
1
vote
1answer
69 views

Summation query with complex grouping criteria

I have a database of coded actions with this structure. {User, Date, ActionCode} I need to generate reports based on dates and action codes, and the action codes are not clean because we have to ...
4
votes
2answers
9k views

Extract part of string based on nth instance of character

I am trying to find a way to extract part of the strings below. I need everything between the 6th and 7th \ character. \\fileServerA\d$\LiteSpeed\Wednesday\ServerA\Tlog\DBA1_TLOG_20110504_0333.SLS ...
1
vote
2answers
233 views
0
votes
4answers
135 views

Selecting the first purchase item of each customer

I have a table like this: CREATE TABLE SALES ( CID NUMBER(4), PDATE DATE, ITEMCODE NUMBER(4), ITEMNAME VARCHAR2(20) ); INSERT INTO SALES VALUES (1111,'15-JAN-2011',4001,'LUX'); INSERT INTO SALES ...
0
votes
1answer
83 views

Combine multiple queries into single query

I am trying to run the following query: SELECT Freight.Freight_No, Freight.Available, Locomotive.Loco_No, Locomotive.Available, Driver.Driver_No, Driver.Name, ...
1
vote
2answers
212 views

“Rewinding” a Postgresql database

I have heard that Postgresql uses an append-only format for it's databases, and if this is true I should be able to 'rewind' the database to a previous point in time by removing the commits that came ...
1
vote
1answer
181 views

GROUP BY with OR/AND clause?

Lets say my database looks like this: Table: messages +----+------+----+----------+----------+ | id | from | to | double_1 | double_2 | +----+------+----+----------+----------+ | 1 | 1 ...
2
votes
1answer
491 views

Paging in SQL Server 2012 vs. previous versions

I have used lot of paging code in SQL Server 2008. The format is With CTE( -- SQL With RowNumber ) -- SELECT ALL Page N This is working fine. But I have heard that SQL Server 2012 has better ...
5
votes
1answer
1k views

How to improve performance on PostgreSQL when using multiple concurrent processes?

Seeing query performance which is not satisfactory on our Python application, which runs several processes that use SQLAlchemy core to access a PostgreSQL 9.2 database. We may have around 100 - 200 ...
1
vote
1answer
83 views

How can I identify the number of times a View is called in SQL Server?

How can I identify the number of times a View was called in SQL Server? I am looking for an answer that is similar to this below which shows how it can be done for stored procedures. How can I ...
1
vote
3answers
784 views

Is transaction log lost after backup on SQL Server?

I have database that has regular nightly backup (full recovery, full backup). I noticed some irregularities in data and wanted to check out transaction log to see what happened. I restored earlier ...
1
vote
1answer
470 views

How to enhance the SQL query performance for thousands and millions of huge data

Now Our team are involved in a forum data analysis project. Its target data is so huge(I think). We should find the potential informations from it. Such as the popular products,the customers ...
2
votes
2answers
96 views

Identity proceeds as normal, but with a leading 10…?

So I noticed a very strange thing when tinkering with my database. I had just inserted a row which was ID 47, then after inserting some more, I realised that somehow my Identity had skipped all the ...
0
votes
1answer
30 views

Simple query not using indexes

My query is not using indexes. It is a simple query. Can someone explain why that's the case? The query: select TIME_FORMAT(start_time,'%H:%i') start_time, TIME_FORMAT(end_time,'%H:%i') ...
0
votes
3answers
5k views

Oracle SUM - ORA-00937: not a single-group group function

For every journey want to sum them to work out the distance between. SELECT Journey.Journey_No, Stages.Stage_ID, SUM(Stages.Distance_Between) FROM Journey, Journey_Stages, Stages WHERE ...
2
votes
2answers
624 views

SQL3550W: … target column has been defined as GENERATED ALWAYS

I am trying to export + import data from a table in a db2 database to another. But while running the export command I am getting the following error. SQL3550W The field value in row "26" and column ...
0
votes
2answers
237 views

Storing data in PostgreSQL: One table or two?

I've just started using PostgreSQL 9.2 and my data consists of product prices at various points in time, usually a different price every month. Question: Because every product can have different ...
3
votes
3answers
1k views

Relational vs Non-Relational Database for Events Database

I'm trying to find out whether an SQL or no-SQL solution would be better for creating an events database. I'm creating a ticketing system, similar to ticket master. I know that for either ...
4
votes
1answer
77 views

Under what circumstances can a division expression reduce the number of rows in a query's result?

I'm experiencing a problem with a query that I've written. Given a table with the schema: create table somevals(division varchar(100), subdivision varchar(100), ...
0
votes
2answers
68 views

Accessing Database without using SQL

This might be some ambiguous but I have not much info about this so I need some start point; As a general idea; in conventional RDBMS', there are a DB engine and storage of data. We use the language ...
10
votes
5answers
21k 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. ...
8
votes
3answers
946 views

SQLITE: A problem of tags and products

I'm searching for a way to create a query to do the following: Let's consider 3 tables: products: List of products tags: list of tags tag_ties: table used to associate a tag to a product Let's ...
6
votes
4answers
27k views

Call a stored procedure from a trigger

I have created a stored procedure in mysql using the following syntax. DROP PROCEDURE IF EXISTS `sp-set_comment_count`; DELIMITER $$ CREATE PROCEDURE `sp_set-comment_count` (IN _id INT) BEGIN -- ...
1
vote
4answers
825 views

Unsure how to implement certain constraints and relations in this relational database schema

I am in the middle of designing the dB for school project- airline ticketing database.(Using MySQL workbench) So far, I came up with the following design: Here are couple things I can't figure out: ...
0
votes
2answers
7k views

MySQL: Join one Row in a table with two Rows in other table

In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations): `users` `user_login` +--------------+ ...
2
votes
1answer
546 views

Replacing a PHP loop with a MySQL Query

I have a MySQL database table where I store a user_id and a hash each time someone logs in. The hash is stored in a persistant cookie so it doesn't change as users log in and out of the site. The ...
3
votes
1answer
832 views

Update “NULL” string to Actual NULL value

I have a table that contains NULL values but the problem is that some of the values are actually string "NULL" and not actual NULLS so when you trying something like where date is null it will not ...
0
votes
2answers
91 views

How to write a script to push the server time to out?

Background: I am programming a database in Ms-Access and using SQL-server as the Back-end. But since my database will be a multi-user platform I need to make sure that whenever a user is posting data ...
0
votes
1answer
306 views

exec xp_cmdshell 'sc \\<servername/serverip> start <service_name>'

When I execute this on sql server on another machine (in same sub net) I am getting "Access is denied". But when I did it from command prompt (sc \ start ) it is working. Please advice why I am ...
1
vote
1answer
546 views

import csv data stored in a blob column

Here's what i need to do: Users will upload CSV files into SQL Server 2012 blob column Each night, i would like to take each file and import the data into table I will have 2 web sites using the ...
1
vote
2answers
3k views

Oracle SQL, how to filter on a set by a rank?

I am struggling with the following SQL query right now: http://sqlfiddle.com/#!4/736a7/12 select uc.* from usercredential uc inner join users u on u.userid = uc.userid inner join credential c on ...
0
votes
2answers
1k views

Only allow one checked row in a Column in SQL Server [duplicate]

Background: I've only just begun programming in SQL... barely begun even. But I have been thrown into the deep-end left to find a way and learn to program SQL. Question: Is is possible to have a ...
0
votes
2answers
154 views

SQL Server Change Tracking

I work for a company of about 80 employees and they all seem to need access to our database on a regular basis. I don't have time to write applications to meet all of their needs so I'm just going to ...