If I have a string in a column on a row in a table like this
1 2 2 2 2 2 2
How would I count the occurrence of a substring 2 inside the string. Assume nothing other than a space-delimiter of " ".
For the purposes of this, let's treat the numbers as substrings
Sample data
CREATE TABLE foo
...
It is easy to demonstrate that many date/time formats other than the following two are vulnerable to misinterpretation due to SET LANGUAGE, SET DATEFORMAT, or a login's default language:
yyyyMMdd -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff -- date dash separated, date/time...
@ypercubeᵀᴹ I didn't, I was just contributing to the discussion. My impression was Erik was talking about an occasion where I pinged Aaron on the matter shortly before or after him, possibly outside SE, too.
Which I think I did, probably on SQL Performance, possibly more than once. I'm just not sure how recent that was and if Erik could indeed have some of those occasions in mind.
@AndriyM I could be mistaken. I thought Aaron said something to me about you having told him about it recently. I don't have any more detail than that.
By any means this is exactly what was said, I mistakenly used to think that yyyyMMdd hh:mm:ss.fff was one of ISO 8601 formats, even was sure it was listed in the Wikipedia page, but eventually found out my mistake
The problem with that is that if you used to think that, it wasn't "mistakenly." yyyyMMdd hh:mm:ss.fff really is ISO 8601.
@EvanCarroll At first I thought I saw that format among the valid ISO formats on the Wikipedia page, which made me think that format was an ISO format. Later I discovered that the format is not there. I concluded that my previous opinion was mistaken. That's what "mistakenly" means in my comment.
very briefly -> in **SQLite** if I create an External Content FTS4 Table based on a non-empty table `CREATE VIRTUAL TABLE Tab_fts USING fts4(content="TabSource", a, b)` would I be able to search something from it right after? `SELECT * FROM Tab_fts WHERE TabSource MATCH 'hello'`
> NOTE: ISO 8601 defines date and time separated by "T". Applications using this syntax may choose, for the sake of readability, to specify a full-date and full-time separated by (say) a space character. [...] ISO 8601 states that the "T" may be omitted under some circumstances.
> NOTE: ISO 8601 defines date and time separated by "T". Applications using this syntax may choose, for the sake of readability, to specify a full-date and full-time separated by (say) a space character. [...] ISO 8601 states that the "T" may be omitted under some circumstances.
> NOTE: ISO 8601 defines date and time separated by "T". Applications using this syntax may choose, for the sake of readability, to specify a full-date and full-time separated by (say) a space character. [...] ISO 8601 states that the "T" may be omitted under some circumstances.
Can you emulate any of this stuff in this question.
Does this work for you inside of MS SQL. SELECT CAST(x AS timestamp) FROM ( VALUES ( '20100330' ), ( '20100330 12:00:00.000' ), ( '2010-03-30 12:00:00.000' ), ( '2010-03-3012:00:00.000' ), ( '20100330T12:00:00.000' ), ( '20170313 23:22:21.020' ), ( '2017-03-13 23:22:21.020') ) AS t(x);
I'm using Rextester, I can't even get the results that the OP is getting.
What does "may be omitted" mean generally, anyway? Can it really mean the same as "may be replaced with a space"? Or at least is it conventional to treat the two as meaning the same thing specifically in computer technology?
@EvanCarroll The string format you are asking about is sensitive to language only when cast to the older smalldatetime and datetime types. This behaviour is preserved for backwards-compatibility reasons.
@dezso Why is he referring to himself as being Swedish? ‘Being Swedish, I find it a bit amusing that Sweden had the weirdest implementation.’ (speaking of switching to the Gregorian calendar)
I'm wondering if the following date is ISO8601 compliant :
2012-03-02 14:57:05.456+0500
(for sure, 2012-03-02T14:57:05.456+0500 is compliant, but not that much human readable !)
IOW, is the T between date and time mandatory ?
@AndriyM Presumably that section was contributed by Erland Sommarskog.
> I like to thank the following persons who provided valuable suggestions and input for this article: Steve Kass, Aaron Bertrand, Jacco Schalkwijk, Klaus Oberdalhoff, Hugo Kornelis, Dan Guzman and Erland Sommarskog.
@AndriyM from what I see, it may be his father who moved to Sweden. Or whatever. The name is definitely Hungarian (I know Tibors only from Hungary and Slovakia, but 'Karaszi' is definitely not arch-Slovakian)
SELECT * FROM
(
SELECT t.*,
row_number() over (partition by t."Internal_reference",t."Movement_date" order by t."Movement_date") as "count"
FROM (
SELECT "Internal_reference",
MAX("Movement_date") AS maxtime
FROM dw."LO-D4_Movements"
GROUP BY "Internal_reference") r
INNER JOIN dw."LO-D4_Movements" t
ON t."Movement_date" = r.maxtime AND t."Internal_reference" = r."Internal_reference"
) AS a
ORDER BY a.count DESC
My aim is to try to have a similar version with access and without the row_number()
I did this
SELECT * FROM
(
SELECT
t."Internal_reference",
t.from_code,
t.to_code,
t."Movement_date",
t.shipment_number,
t."PO_number",
t."Quantity",
t."Movement_value",
t."Site",
t."Import_date",
COUNT(t."Internal_reference") AS "count"
FROM (
SELECT "Internal_reference",
MAX("Movement_date") AS maxtime
FROM dw."LO-D4_Movements"
GROUP BY "Internal_reference") r
INNER JOIN dw."LO-D4_Movements" t
ON t."Movement_date" = r.maxtime AND t."Internal_reference" = r."Internal_reference"
@AndyK In your first query the ROW_NUMBER column is aliased as count, which, combined with the COUNT as the ROW_NUMBER's replacement in the second query, looks as if you think that COUNT and ROW_NUMBER are the same thing. Do you realise that they are not?
I believe similar questions have been asked but I can't quite find a solution that works for me.
I've got a database that I use to sort through digitised books and their pages and I'm trying to sort through several thousand pages that contain maps. Of the two tables I'm using the first lists all...
Do we have ROW_NUMBER function in MS Access? If it has then please let me know any syntax for it as I am stuck here. I have tried forums but I get sql server syntax. Following is my query:
select
ROW_NUMBER() OVER (ORDER BY t.TID) AS uni ,
t.TSource as [Source],
t.TText as [Text],
...
How to achieve the Row number over partition by in the MS access .I Google but cant find information can you please how to do this
RowNumber Over(Partition by city Order By EmployeeID)
My Data looks like this
DOC_TYPE Ino
3a 1800xxc1
3b 1810xxc2
3c 1700xxc3
3a 1700xxc4
3a 1800xxc5
3...
To whoever maintains SQLFiddle: I think, humbly, that the site is giving away too much on a few errors that can be reproduced by inputting a string (') in front of the parameters. eg. dbfiddle.uk/…
although, generally attacks that manage to touch database level are all shell (webshell) based so it would be interesting to see a real scenario where a person would actually root through a comment on a mysql dump... dang, sounds dope
@AaronBertrand I remember commenting about it somewhere on one of your blog posts, but I'm not sure when or where. Don't seem to have bookmarked the location, but I'll probably take another look, even if just to satisfy my own curiosity.
@PaulWhite I updated my answer to reflect this, thanks.
Also, for the room, Tibor is definitely Swedish, or at least he has lived in Sweden for the ~20 or so years I've known him, though I am sure people are right about name origins.
@NelsonCasanova Thanks, I appreciate the input. In this case I'm not sure I need to hide that — the schema and code will be on GitHub anyway, and it's useful to see the error. I've neatened it up a bit (dbfiddle.uk/…) and will add some custom error handling at some point. I've added some custom error handling to catch that particular case but only to make it more user friendly!
Let's make a few assumptions:
a. I have table that looks like this:
a | b
---+---
a | -1
a | 17
...
a | 21
c | 17
c | -3
...
c | 22
b. I have millions of rows with value a in the a column, similar for other values (e.g. c).
c. Most of my queries will read all or most of the values...
I am a fresher for database tuning, I am trying to learning improve my PostgreSQL database performance, So I search lots of articles in google
There are many advices are Read/Write Splitting for MySQL, because of read will affect write.
But when I reading PostgreSQL official documentation that ...
The question is that, at work, we only use Windows. But all my tools are located on VM. We need to maintain different compilers (C++Builder, VStudio 2008 & VStudio 2015). In fact I only use host machine for Office356, and eventually it can be installed on another VM. I'd like to install a light-weight linux version just to save memory resources.
I moved the prior messages to The Trash since the conversation is, again, totally off-topic. @ErikE - I appreciate that you made some great observations - if you want to debate like that, perhaps create a room where the rest of us don't need to see it. Thanks!
@EvanCarroll Ok do that if you like. I'll let you know if it causes an issue or if we'd prefer you not to do that for whatever reason I can't think of in advance.
Paul, I got a workaround to that issue around applying a function (such as DATEADD) to a sorted column but SQL Server no longer considering the data to be ordered, if you're interested
A friend and I teach an internal SQL performance class
inside the company that we work for
so I was blown away from that perspective too
as in, "wow, he explained that so much better than we do"
to be honest I was disappointed in the level of the class
I thought it would be a higher level. The one you described may have been the all day one about parallelism. I did try to ask as many questions as seemed reasonable though
you might explain it in your article. I didn't have time to read it properly
but I don't see how it can't be a bad implementation of the operator
for the sort issue, Adam said that he has used CLR functions
I might not get the details quite correct. But you mark the CLR function as preserving order. Then you can use it as a substitute for DATEADD or whatever you need to do. He did say that he spent a lot of time struggling with that same problem
I think what bothers me the most about this dense_rank() problem is that is struggle to explain it. I bet math has a name more specific than "sequence offsets." Maybe I should write to numberphile
@JoeObbish Yeah it's a problem with workshops, especially SQLSat. Chances of getting attendees that will genuinely benefit from a level 500 talk is pretty small.
People often overestimate the level they're at.
@JoeObbish A TOP(1) won't spill to disk, if the 1 is a literal anyway. Perhaps you could share a repro at some stage.
@EvanCarroll I'm not aware of a neat term for it. People often use analogies like Olympic medals.
@PaulWhite Yes, I understand what you mean. I think that his content made a lot of sense for the people in the room. It was a pleasure to attend the seminar but I couldn't help but feel some disappointment
@JoeObbish I vaguely remember a moderately complex/hairy setup where an unsafe clr aggregate wrote rows to a shared memory structure, which was then used by a streaming function with the ORDER property. GUID as a key if memory serves.
It is easy to demonstrate that many date/time formats other than the following two are vulnerable to misinterpretation due to SET LANGUAGE, SET DATEFORMAT, or a login's default language:
yyyyMMdd -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff -- date dash separated, date/time...
I'm wondering if the following date is ISO8601 compliant :
2012-03-02 14:57:05.456+0500
(for sure, 2012-03-02T14:57:05.456+0500 is compliant, but not that much human readable !)
IOW, is the T between date and time mandatory ?
How to achieve the Row number over partition by in the MS access .I Google but cant find information can you please how to do this
RowNumber Over(Partition by city Order By EmployeeID)
My Data looks like this
DOC_TYPE Ino
3a 1800xxc1
3b 1810xxc2
3c 1700xxc3
3a 1700xxc4
3a 1800xxc5
3...
Let's make a few assumptions:
a. I have table that looks like this:
a | b
---+---
a | -1
a | 17
...
a | 21
c | 17
c | -3
...
c | 22
b. I have millions of rows with value a in the a column, similar for other values (e.g. c).
c. Most of my queries will read all or most of the values...
I am a fresher for database tuning, I am trying to learning improve my PostgreSQL database performance, So I search lots of articles in google
There are many advices are Read/Write Splitting for MySQL, because of read will affect write.
But when I reading PostgreSQL official documentation that ...
