You can do this as a simple subtraction of DENSE_RANK() operations:
WITH IdTypes AS (
SELECT
date,
id_type,
Dense_Rank() OVER (ORDER BY date)
- Dense_Rank() OVER (PARTITION BY id_type ORDER BY date)
AS Seq
FROM
tmp
)
SELECT
Min(date) AS begin,
...
@ypercubeᵀᴹ I had that experience once where I used ROW_NUMBER() and then later it didn't work until I switched to DENSE_RANK(). That probably explains it.
@ypercubeᵀᴹ I'm proud of my "PREORDER BY", it's childish and immature but damnit I think it's awesome. :)
@ypercubeᵀᴹ I'd be really curious to see the execution plan on my answer to know if it is satisfied by a single sequential scan like Erwin's answer.
he mentioned using MERGE but MS says it's a bad idea
"Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it does not exist, or updating the row if it does match. When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements."
@ypercubeᵀᴹ Hmmm, I don't know... what I found was that SQL Server performed quite well using this technique on a table that had no indexes. I dimly recall seeing a single scan, but don't trust that memory.
@ypercubeᵀᴹ Do you think it's worth rewriting my query to avoid the CTE, then?
Hi peeps. I know I'm bad about being on SO. Anyone have any opinions on when Google Cloud Spanner would be a better choice than Azure SQL?
Looking for basic hosting for typical relational data that can be scaled quickly with web/mobile access. Lots of users, limited rows and tables.
Read-heavy. It's a mobile app for viewing schedule information and choosing events from a fixed inventory of shows on particular stages to personal schedules.
Normally, in the past, I would have spun up an AWS with SQL Server, but now I'm thinking pure cloud is probably something for this case where the DB is not going to have many of the special things I'm used to in SQL Server like Agent, SSIS etc.
Is there anyway at all, ever to change the behavior of comments in psql. Take the query below. Execute it.
CREATE TABLE foo
AS
SELECT x AS id,
-- x AS id2,
x AS id3
FROM generate_series(1,50) AS x;
Run that in psql. Then run \e. Now at least, for me what I see in my editor is the l...
I wonder if the people that downvote my self-answered questions do so because they legitimate assess them to be sub-par contributions to DBA.SE, or because they view them to be self-promotion or something.
@PaulWhite Ha ha, I thought that this suggested edit was adding the extra or unnecessary indentations instead of actually removing them. Glad you accepted it. Cheers.
eh, asking well-defined questions about CORE RDBMS seems very on topic to me. "list" is just because that's the form the answer takes. It could be implicit. "Where do I find what extensions PostgreSQL ships with."
but sure, why people vote the way the do was the question behind the gripe
@ErikE it's a similar plan to the other solutions, but much more elegant imo. Erwin's function avoids the sorts entirely, you can't 'beat' that — though I'd imagine your solution would actually be faster.
I just saw this picture making the latest rounds today on Reddit’s /r/socialism. The picture has a quote attributed to Malala Yousafzai.
The caption reads,
I am convinced
socialism is the
only answer and
I urge all
comrades to
take this
struggle to a
victorious
conclusion. ...
After boasting online of the incident, the men told NBC News they had good intentions when they knocked the rock over, saying they were preventing it from hurting anyone.
I am made two parameter (Year - Month) in order to show reports for sales based on these parameters. I made them dynamic by specifying values to them (year: 1995-1996-1997) and (Month: Jan to Dec). Then used filters in the Tablix to connect datafields to the parameters. Every time I choose the year with the month I get an error in my report!
Error Type: Cannot compare data type systems.string and system.Object[].please check the data type returned by the filter expression for dates
@ypercubeᵀᴹ Old SQL Server used to parallelize hash using a separate hash table per thread, either distributing rows between threads, or broadcasting all rows to all threads. New parallel hash uses a single shared hash table that all threads cooperate to build (using lock-free CPU primitives) and probe.
And batch mode means 64-900 rows are processed at once, instead of strictly iteratively (row-by-row per operator).
Today marks two years since I started working with SQL Server :) I quickly found this site, and I’m glad I did, since I didn’t have anyone to teach me (though there was a friend who advised me to add NOLOCK to everything). I learned more than just answers from folks here – I learned how to think, analyze, and investigate. I have a rewarding career now, my life is tangibly better, and it’s because of people like y’all. Thank you!
So I have a database for which I have configured the ROWS (.mdf) database file with autogrowth ON and set to max size to 10Gb.
After a while, I get an alert that my tempdb log has filled a drive and I figure that my database's size is stuck on the limit at 9990mb or something, unable to extend a...
SET LANGUAGE FRENCH;
SELECT
*,
Problem = CASE WHEN Month(d.DateValue) = 3 THEN '' ELSE 'X' END
FROM
(
SELECT 'yyyyMMdd HH:mm:ss.fff', Convert(datetime, '20170307 23:22:21.020')
UNION ALL SELECT 'yyyy-MM-dd HH:mm:ss.fff', Convert(datetime, '2017-03-07 23:22:21.020')
UNION ALL SELECT 'yyyy-MM-ddTHH:mm:ss.fff', Convert(datetime, '2017-03-07T23:22:21.020')
UNION ALL SELECT 'yyyy-MM-dd', Convert(datetime, '2017-03-07')
UNION ALL SELECT 'yyyy/MM/dd', Convert(datetime, '2017/03/07')
Three safe date literal formats I've found in SQL Server.
In his (Bad habits to kick : mis-handling date / range queries), he states: > the only truly safe formats for date/time literals in SQL Server, at least for DATETIME and SMALLDATETIME, are:
YYYYMMDD
YYYY-MM-DDThh:mm:ss[.nnn]
But in his (What do BETWEEN and the devil have in common?), he uses your first one (yyyyMMdd HH:mm:ss.fff), so I don't know.
@EvanCarroll the gold hammer allows you to close any question tagged with your gold badge. However, you cannot add the tag to a question, then close it with immediate effect.
pgadmin vs pgadmin-3
There are two seperate products
PgAdmin 3.
PgAdmin 4.
PgAdmin doesn't currently mean anything until PgAdmin 3 dies off.
As you may know, many of us from the pgAdmin team have been hard at work on pgAdmin 4 for some time now. pgAdmin 4 is a complete rewrite of pgAdmin...
do we have a procedure for nuking a question that is clearly stupid with a bad chosen answer that doesn't make any sense? dba.stackexchange.com/questions/120890/…
Can someone confirm that in SQL Server, a "non-logged operation" such as a bulk copy really is a "minimally logged" operation, in the sense that there is still a t-log entry created, just with far less data in it than if one were to perform the inserts one at a time?
I'm trying to convince an older guy who remembers that in SQL Server 7 non-logged is literally not logged, that it's really always been minimally logged, but it's not working.
Using postgresql-unit
I'm big on types. I would use an extension for this. If you're not in the US and working with Imperial Units, take a look at Christoph Berg's brainchild postgresql-unit. First build the extension
sudo apt-get install bison flex build-essentials libpq-dev
git clone "https:/...
Unless, you know, he is right and 7.0 did not log at all. But then it would not be able to recover after a failure during the import. I don't know how plausible this is
I need to have an entity that has two separate candidate keys where one of them is the primary key, which is a surrogate identity key, and another one that is an alternate key representing the real unique attribute. I need to have them both separately in my entity. Please note that I am not refer...
please i'm working on a project, i'm kind of hanged. Please how can I COUNT NUMBER OF SAME VALUE in a ROW.. Not counting number of rows in a table but number of value in a row. eg.
1 2 2 2 2 2 2
I want to know number of times that "2" appears in the row.