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

learn more… | top users | synonyms (2)

0
votes
1answer
21 views

How to get when MySQL instance went down last

Is it possible to determine when a MySQL instance went down last? Is there a log file or literally anything at all that logs the server status in MySQL? I have trolled through countless articles ...
3
votes
2answers
254 views

How to get difference in value over 15min interval?

I have this table: The 2 important columns here are domainid and Soldtickets. Soldtickets show how many tickets were sold today. I make a call to my server and it tells me that I made the call ...
1
vote
1answer
15 views

Select row from non-empty node value in xml column using xml query

I am trying to select the rows from the table by putting the condition on the xml column. The condition like if there is date value on the node ‘Completedon’ in the xml column then select that row. ...
2
votes
1answer
38 views

Iterative SQL Delete Statement

The following is a simple version of a delete I need to perform. delete from table_name where order_date < (getdate()-60) go This is deleting a large number of blobs and to clean up the table I ...
1
vote
1answer
31 views

Difference between Generate Script option at database level and backup and restore option

When doing that generate scripts on the database level what difference does it do from doing a backup and restore on the database? It seems to me that they both provide the same purpose. What would be ...
0
votes
1answer
28 views

Oracle : Is my DISTINCT function working properly?

I have alphanumeric values as my primary key. I am querying my database in two different ways and finding different results. It is almost certain that I might be missing some key point in my queries. ...
0
votes
0answers
14 views

How to find out user activity for calls made from an application server?

I have to log user activity on the SQL server for the calls made from SAP application server, the problem is the Application server does not send any User information along with each call and uses a ...
0
votes
0answers
30 views

Group by periods in SQL

Each register is an event in a location with an id, but events take place "in clusters" (e.g zero to two events every day from 31/02/2014 to 10/03/2014, all with the same id). I need to detect the ...
2
votes
1answer
36 views

Stored procedure unable to compare

Hi I tried implementing the below stored procedure which always takes my if condition as true and implements . CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertOpen`(in uid varchar(11),in S_ID ...
2
votes
1answer
58 views

How to automatically fix Orphaned Users with PowerShell and SQLCMD

I'm trying to properly capture output from SQLCMD in SQL Server 2012. Here is the PS script: $dbname = "MyDB" $InstanceTarget = "localhost\SQLEXPRESS" $FixOrphansSQL=@" WITH login_CTE (name, type, ...
1
vote
1answer
57 views

Select column values linked to exact set of values in another column

Given the below chats_users table in Postgres 9.5, with integer columns chat_id and user_id, I want to find the chat_id associated with an exact set of user_ids, for example: user_id IN (1, 3) => ...
0
votes
1answer
51 views

How to get top 5 rows of given Id? [duplicate]

I have the following code: DECLARE @Latest TABLE ( DomainId bigint, [date] datetime2 ) DECLARE @Rows int DECLARE @History int INSERT INTO @Latest (DomainId, date) SELECT DomainId, ...
0
votes
0answers
6 views

exporting selective data to excel in php

I have a table called tbl_order which has three columns: credit_card debit_card selected_payment_method i want to export to excel value of column 1 or 2 only depending upon the selected payment ...
0
votes
0answers
11 views

dba_tab_partitions we have all columns except MAXVALUE column

i want to cross check all partitions were created properly or not according to the timestamp, TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', is there any way to cross check partitions??? ...
0
votes
1answer
28 views

What is DBCC66 and DBCC63 files

Tried to get some clarity but can't find any thing googling or searching in forums. We got a couple of files in the Data folder of one of our MS SQL servers databasename.ndf_MSSQL_DBCC66 ...
-1
votes
2answers
57 views

Count txn date in oracle database [closed]

We want to count transaction date/time as result screenshot above, Please helps check code as below, and the code as below is when run the result as screenshot below, Thanks all guy. for r ...
0
votes
1answer
28 views

Restoring multiple snapshots with SQL Server 2008

I noticed I cannot have multiple snapshots of a SQL Server database and then restore from one of them (without I believe, deleting all but the one I want to restore). Is there a way to keep multiple ...
0
votes
3answers
29 views

Aggregate and GROUP BY conflict with HAVING?

I don't know if my logic in my query is incorrect, or if I simply have a syntax error, or both. For each customer that had more than two orders, I want to list the customerID and the total number of ...
0
votes
0answers
38 views

Error 1129: Host '' blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

When I run a python code which connects to mysql, I see this error; "Error 1129: Host '' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'". Here is what I have ...
1
vote
1answer
45 views

Aggregate sampler function for programmer express predictions

Are there any SQL-standard or DBMS implementation that offers "sample aggregator" for predictably repeated columns? I will explain, see bold text at the end. PS: this question is about "little ...
1
vote
1answer
35 views

Deleting aged postgresql rows with tricky condition

I have two postgresql tables I need to clean up as part of maintenance. The tables, as described in my Django front-end, are: class Group(models.Model): owner = models.ForeignKey(User) unique ...
1
vote
0answers
46 views

How to convert .ibd files to .sql [closed]

Could any one please tell how to convert .ibd , .frm, .MYD, .MYI FILES TO .SQL . Or is there any way to open this files in php my admin .. Please tell how to convert this to .sql file
0
votes
1answer
48 views

Not exists in sql query

I'm using oracle database I need to display customer id and customer name of those customers who have not purchased at all from any retail outlet using not exists My query: select ...
3
votes
2answers
79 views

Database model about libraries

I need to make database model about libraries. In that model I'm tracking more libraries and every library has it's own moderators. Every moderator can make categories of the books and than add books ...
0
votes
1answer
28 views

Select records not associated to a specific tag in a many-to-many relation

as shown in this sql-fiddle I have a bridge table events_tags representing a many-to-many between events and tags. I need the fastest way to find all events not associated to a given tag t. The ...
3
votes
2answers
46 views

IN Clause - listing values VS query

My app runs a lot of queries like this SELECT ... FROM Table WHERE CategoryID IN (SELECT CategoryID FROM OtherTable WHERE ..) To save performance I decided to cache the second subquery in the ...
3
votes
1answer
65 views

Can this SQL Query be improved

Related to one of my previous questions: How to select a subset from a query result I now have this Query: CREATE TABLE #JourneyLogs ( JourneyStartId INT, JourneyEndId INT, ...
1
vote
1answer
31 views

How do i insert a value into table2 from a trigger on table1

i have 2 tables with a column name WHO, and im using these 2 tables to create a view. where FSwho.who = FStasks.who if i enter a new record into FStasks where FStasks.who = LIZ, but theres no record ...
3
votes
2answers
50 views

PostgreSQL: Generate a series of dates for each group in a table

I have a balances table in PostgreSQL 9.3 that looks like this: CREATE TABLE balances ( user_id INT , balance INT , as_of_date DATE ); INSERT INTO balances (user_id, balance, as_of_date) VALUES ...
4
votes
2answers
144 views

Is there a way to SELECT n ON (like DISTINCT ON, but more than one of each)

I have a table us_customers that looks like this (with hundreds of thousands of rows): +----------+----------+ | id | us_state | +----------+----------+ | 12345678 | MA | | 23456781 | AL ...
1
vote
2answers
37 views

Counting rows from a subquery

Simple: I would like to count the number of rows from the sub-query. Note that status is whether the host is online or not. Bad code SELECT COUNT(ip_address) FROM `ports` ( SELECT DISTINCT ...
2
votes
1answer
29 views

Schema name, table name and row count

My database has 50+ schemas. Each schema has around 100 tables and some views. I want a query that tells me the schema name, table name, and total number of rows in each table. The below query: ...
0
votes
0answers
29 views

Is there any way to know which SP executed from “exec sp_execute” in SQL profiler?

I have traced a SQL profiler and got some process tracings. I got one command in which the stored procedure is executing by the command exec sp_execute Can I trace that which stored procedure had ...
-4
votes
2answers
52 views

Query returns NULL for current date

I want to query user quota for the current date. I am using the following code: SELECT (SUM(acctinputoctets)+SUM(acctoutputoctets)) AS Total FROM radacct where (acctstarttime between ...
2
votes
1answer
166 views

Query Time is doubled because of a column in select

I have a Stored Procedure which is calculating some data and inserting it into a Temp Table. Then I am selecting the Data from the Temp Table. Select AssetId = iAssetId, ..... SpeedKM = ...
2
votes
2answers
62 views

Why am I able to query for more than 1024 columns but I can not make a view of the same query?

My boss is requesting a view with greater than 1024 columns where in he will only select a few columns he wants when he queries the view so that the actual returned columns are less than 1024. Why am ...
0
votes
1answer
101 views

Error importing SQL Azure Bacpac to SQL Server 2014 LocalDb

Downloaded a v12 SQL Azure bacpac, and trying to import it into a SQL Server 2014 LocalDB... I get: TITLE: Microsoft SQL Server Management Studio ------------------------------ Could not load schema ...
1
vote
0answers
57 views

Max returns multiple rows [closed]

I am quite sure I am missing something. Could someone tell me why these two tables are not returning the same row count, please? SELECT Person_id, COUNT(*) FROM table GROUP BY person_id; And these ...
0
votes
0answers
31 views

Distinct Rows Based on Field Value

I can't seem to get around this problem. I am trying to query into a SQL table I have and limit the results. I have an org_id. Also a field named address_id. The table has a "primary_address" column ...
1
vote
1answer
31 views

Mapping Fields based on unknown relationship

I have a challenging SQL query to work out (SQL Server). I have one table that will show mappings between the two tables. So below, field F1 is a scanner id and table two, has the scanner Id in F1. ...
1
vote
1answer
44 views

SQL getting IDs from one table with multiple entries in another table

In my database I have two tables: Persons and PersonAttributes. One Person may have several Attributes associated with it. What I would like to do is get a list of all Persons(/IDs) that have at ...
-1
votes
1answer
66 views

how can I add multiple values in a single column?

I'm working on a project and I have a scenario We have a vendors table, some vendors have many contact no. how can i add multiple phone numbers on a single contact no. column?
4
votes
1answer
58 views

How to get all connected values as a single column

I'm trying to use CTE recursion. This is my scenario SQL Fiddle. It is a simple tree structure: CREATE TABLE [dbo].[AL]( [IdAL] [int] IDENTITY(1,1) NOT NULL, [ID1] [int] NOT NULL, [ID2] ...
0
votes
1answer
22 views

Where are sql profiler trace files stored?

Can't seem to find an answer to this.. During a recent sql profile I noticed C:\ was rapidly filling up. Does anyone know exactly where these files are stored? is it part of SSMS?
2
votes
2answers
74 views

Does UDP Port 1434 need to be open for named instances with static TCP?

I have built a named instance which is running on a static port, 50101. The network guys have opened the port on the firewall to allow incoming connections. I have asked them open UDP port 1434 too, ...
2
votes
1answer
88 views

Optimize performance for query with CTE using a multicolumn index

I've got this slow query: WITH flks AS (SELECT id FROM public.ja_feedlog WHERE gtime BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2016-01-24')::BIGINT AND EXTRACT(EPOCH FROM ...
2
votes
1answer
44 views

Is there any particular reason LIMIT 0 is allowed?

I've just tried running SELECT * FROM tbl LIMIT 0, which unsurprisingly, returns 0 results. I could see an argument for making it be an error, because it will never return any result, but then again ...
-1
votes
1answer
29 views

Trend Calculation [closed]

I have a table with the below data: FHANUMBER Quarter Value ------------------------- 001 1 5 001 2 4 001 3 3 001 4 2 I need ...
10
votes
3answers
140 views

Theil-Sen estimator function in T-SQL

Does anyone have a Theil-Sen regression function written in T-SQL? I found one written in Perl, but I'm not able to recode it into SQL.
2
votes
3answers
76 views

Update a column for every row of a large table to a unique value in a set range

We have a SQL Server table with over 3 million rows in it. Each row represents a product. These products are provided by about 500 suppliers, and are updated each night via a file from each supplier. ...