Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (1)

-4
votes
0answers
75 views

how to get one of the values in group by?

For the following tables stock_moves product_id purchase_line_id state product_uom_qty ----------------------------------------------------------- 4018 1230 assigned ...
0
votes
0answers
17 views

How to get row position in sqlite after sorting?

I have table, UUID | INT | LONG AAA | 1 | 123 BBB | 3 | 241 CCC | 2 | 145 And i sort it by INT value. SELECT * FROM table ORDER BY int_val DESC then i want to get a row from table and ...
1
vote
0answers
29 views

How do I disable constraint checking during schema migration?

How do I disable FOREIGN KEY checking for the time of database schema migration? Currently I found engine-based commands like: PostgreSQL: SET CONSTRAINTS ALL DEFERRED; MSSQL: EXEC ...
0
votes
0answers
16 views

List Network Servers on SSMS Browse Servers

My question is how will I show available network server on Browse for Server > Network Servers of Sql Server Management Studio? FYI, I am able to connect to existing and known servers even if it is ...
11
votes
2answers
79 views

PostgreSQL Recursive Descendant Depth

I need to calculate the depth of a descendant from it's ancestor. When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH ...
2
votes
1answer
26 views

OFFSET and LIMIT on complex query

I am doing a pretty complex query in Postgres 9.3.4: SELECT p.* FROM unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600 , 17804, 20717, 27598, 27599}'::int[]) ...
1
vote
2answers
103 views

Sql getting debit, credit and balance issue

I am using the query below to get Debit/Credit Balance from table statement SELECT t.[InvoiceID], t.S_Type as Type, t.Date, t.Debit, t.Credit, b.Balance FROM Statement as t CROSS apply ...
0
votes
1answer
48 views

Why do string functions on SQL based database servers start at position 1 instead of 0? [closed]

This has always bothered me. It seems that string functions in SQL based servers always start at position 1 (at least this is the case with MySQL, SQL Server, Oracle and Postgres). For example the ...
1
vote
0answers
40 views

Slow query - PostgreSQL 9.2

I've got a Query that takes some time to be executed... Can you guys help to improve the total time? Thank you! QUERY: SELECT file.inode_id AS file_id, file.parent_inode_id AS ...
0
votes
1answer
43 views

Recursive relationship in oracle

I have a table which stores the customer , related customer and count of related customers. The related customer can have again further related customers it's like a recursive relationships. I ...
-3
votes
1answer
33 views

Problem with INSERT INTO [closed]

I can't make INSERT INTO Booking work. My code: INSERT INTO Gaest (pasNr ,fuldNavn , adresse, tlf , email ) VALUES ('1234', 'Simon jensen', 'Heimdalsgade 6, 9000','6691612' , ...
0
votes
0answers
6 views

Using start and end event logs to create a table/view containing spans between the times of each log [duplicate]

To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following: ------------------------------------- | user | event | team | timestamp | ...
0
votes
3answers
50 views

Using start and end event logs to create a table/view containing spans between the times of each log

To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following: ------------------------------------- | user | event | team | timestamp | ...
3
votes
2answers
89 views

What to do when your AlwaysOn cluster loses quorum?

I was reviewing our company DR procedures and when I looked online for solutions to an AlwaysOn Cluster losing quorum, to compare to. I was three pages into google results before finding the first SE ...
2
votes
2answers
46 views

select/union output from two columns into three

I'm sure there is an easy answer to this, but my PostgreSQL isn't up to it at the moment. I have a table hostname_facts with three columns: hostname name value hostname is a list of ...
3
votes
1answer
58 views

Nested SQL Query (Query a subset of data)

Assume I have the following structure... Structures - ID - Name Fields - ID - StructureID - Key These table hold simplistic 'structures' (Think very simplistic tables). There is then an ...
3
votes
1answer
87 views

Is it possible to extract the multiple variable parts of data into different columns without a procedure?

I'm currently trying to do an analsis of a table containing a couple thousand rows of data. I have managed to restrict the data to what I really need, but I'm not sure if I can split it up further ...
0
votes
0answers
22 views

Slow Query - Optimize - PostgreSQL 9.2

I've got a slow query.. I'd like to make it faster.. Make add an index? Query: SELECT j.clientid AS client_id, ni.segment_index AS note_id, f.inode_id AS file_id, ...
1
vote
1answer
30 views

Trying to understand how an auto increment primary key is better than no primary key and some other primary key questions

I am trying to understand primary keys better, how to use them effectively in table design and in queries. First, are the primary keys themselves used in WHERE clauses? For example, if I have a table ...
6
votes
2answers
152 views

SQL Server: Accurate Row Estimates without Histogram?

I'm exploring the optimizer in SQL Server, particularly histograms and I'm seeing some strange, results. If I execute the query below, where I know there is no histogram on the field, SQL Server ...
1
vote
3answers
108 views

Restart of rownumber based on count in other table in oracle sql

I have two tables which do not have a common key. I want to do a join on these two tables without a cartesian join. Table 1 has around 40,000 rows (the count varies from day to day in production) ...
2
votes
1answer
56 views

How to get number of active users with one SQL-Query?

I am running a site similar to craigslist where users can create classifieds and write/reply messages. In order to get an idea of activity I would like to get the amount of active users per month. ...
-1
votes
1answer
72 views

SQL query without temporary table

I want to modify this sql query from this post: List databases and tablespace in SQL Server 2014. IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL DROP TABLE #space CREATE TABLE #space ( ...
0
votes
0answers
8 views

Collect data with central management servers and keep them in local table [duplicate]

I have 50 servers. I manage them from a central management server. I could open a query and get result for each one at the same time. But I want to do it in an automated way, like a job, to collect ...
4
votes
3answers
66 views

Collect data with central management servers and keep them in local table

I have 50 servers. I manage them from a central management server on SQL Server 2014 Enterprise Edition. I don't use repository. I could open a query and get result for each one at the same time. But ...
1
vote
1answer
43 views

SQLite query rows nearest to hour

My question is similar to the one asked here. I'm new to SQL and I'm creating a weather app that stores part of the data locally in a db, so I'm writing the query using the sqlitedatabase java class ...
-2
votes
1answer
32 views

Merging two rows when there is a duplicate value in one of the columns [closed]

Consider a table T1, having columns C1, C2, C3... C1 C2 C3 CAD PF1 100 CAD PF2 200 USD PF3 300 Result should be C1 C2 ...
7
votes
1answer
82 views

Why does adding a smallint column to a postgres query increase execution time 60X?

Here I have what seems to me to be a routine table and a table creation script that I have put in /tmp/try.sql. I am running postgres under OSX 10.10.2. Here is the table: cow_dev=# \d carc ...
2
votes
1answer
55 views

I need help with a SQL-query that results in an error

I get the following error when I try to delete a record in the user table that has an entry in the feedback table: e.g. when a record in the feedback table has a reference to the user table ...
1
vote
2answers
35 views

Update audit database name in triggers and views of main database?

I have a a system with two databases, main database and audit database. A lot of the triggers and table views in the main database and audit database are referencing from one database to the other. No ...
1
vote
3answers
66 views

How to retrieve the latest guid value after you have inserted a new row in realtime

Goal: Retrieve the latest guid value in real time after you have inserted the the value in the table Problem: Don't know how to do it Info: *You can only add a new vale that is address and zip code ...
0
votes
0answers
17 views

How to dynamically query values into their own columns [duplicate]

First of all, I'm aware that the wording may not be perfect, I'll glad to fix it with better suggestions. I have a table that relates some costumers to some products, like so: costumer_id ...
1
vote
1answer
69 views

SQL current timestamp

I have made a featureclass for a utility company where they can register measures set up to mitigate problems with rats, called "mitigation measure". From this feature class it is possible to state if ...
2
votes
0answers
39 views

Database Corruption - object in sys.parameters does not have a matching row in sys.objects

I'm using SQL Server 2008 R2 and I have a strange db corruption. I ran dbcc check db and the output is: Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=1) in sys.parameters ...
4
votes
1answer
392 views

Why did adding a secondary index not increase select performance?

I have a table in MySQL called messages, which looks like this : id (primary key) | description | created_at(timestamp) This table is supposed to hold chat messages between users of my ...
0
votes
2answers
50 views

SQL select default parameter [closed]

I have a table with columns like name, age, email etc. For some columns I also have parameters to control the behaviour of queries. I want to show the column's value if the corresponding parameter has ...
0
votes
1answer
41 views

Three queries returning 1 row each

So.. I've got: 3 DB Queries returning 1 row each .... SELECT value FROM ja_settings WHERE varname='clickatel_api_id' SELECT value FROM ja_settings WHERE varname='clickatel_user' SELECT value FROM ...
7
votes
3answers
145 views

How to convert default instance to named instance in SQL Server 2012?

How to convert default instance to named instance in SQL Server 2012? By mistake I have chosen default instance option and installed. Now I have to change to named instance. How to change?
3
votes
2answers
47 views

How do you join tables at different points based on data in the first table?

Complicated question: I have 3 tables that I am joining to pull data. The first is a 'shift' table; the second a 'schedule' table, and the last is a 'shift_info' table. shift_info ...
0
votes
2answers
53 views

SQL temporary variable query

Not sure what am I doing wrong in this query, there seem to be a syntax error in the subquery AND typeId = (SELECT @id = typeId FROM EmpType, near @id =. I'm looking at getting the typeid in a ...
2
votes
1answer
50 views

What Host Name is my application connecting to SQL Server on

We have this 1 node broken cluster that I have been given the joy of fixing. Wont go into the reasons why its only got one node but I am planning on migrating this off onto a newly built cluster. So ...
3
votes
1answer
38 views

How to convert following mysql query to oracle?

I have a mysql query like following, Column time contains date in long format. SELECT FROM_UNIXTIME(CEILING( Time/('20160'*1000)) *'20160', '%Y:%m:%d %h:%i') AS Time, SUM(CreatedInstanceCount) AS ...
1
vote
1answer
43 views

Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table?

I've heard MySQL admin claim that ALTER on MySQL table creates an intermediate copy of the table during the operation (don't know if the type of table involved were supposed to be MyISAM or InnoDB), ...
3
votes
2answers
60 views

SQL Server “fix” query for Hash Match instead of Nested Loops

At the moment I have a view that is running very slow. It takes about 3 minutes to execute. This suddenly happened. To debug what was going on I used an old dataset and compared both queries. On the ...
1
vote
1answer
74 views

Simple trigger to update two columns in one table

I want to create a trigger MySQL that updates two fields at the same time. Whenever a record is added or inserted, I want the contents to be copied to one field or the other, depending on which one ...
3
votes
3answers
145 views

Choosing a PK: VARCHAR(2) or SMALLINT(2)?

Today we were discussing the following: Brazil has 27 states and each one has its own abbreviation (just like USA). So we've got RJ for Rio de Janeiro, SP for São Paulo, MG for Minas Gerais and so ...
8
votes
6answers
515 views

Switching values in a column with one update statement

You find that an error in a system has been incorrectly naming men (M) as women (W) and vice versa in the database. The columns only allows for one character. Without using any temp tables, write one ...
3
votes
2answers
98 views

Solving MySQL Query

I am trying to solve the following query with these data: Some assumptions will need to be made. Please list any such assumptions Users: +---------+---------+--------+-----+--------+ | USER_ID | ...
-5
votes
2answers
44 views

#1064 - You have an error in your SQL syntax; [closed]

I am trying to create index on a specific column, My syntax CREATE INDEX indexname ON test1; But i got a error #1064 - You have an error in your SQL syntax; check the manual that corresponds to ...
3
votes
3answers
204 views

Selecting only one duplicate

I have a database of purchase orders and stockroom checkouts, and it seems that the stockroom checkouts tends to get duplicates inserted (we source this data from an older system nightly). What would ...