Grim... wrote:
Lonewolves wrote:
Have you tried truncating the table?
Hey, I know about that! If you have queries that are running slow, truncating the tables they're run on makes future ones way faster!
It's one of the first things I learnt at sequel school
Doctor Glyndwr wrote:
Cras wrote:
The real world data has that data actually be a timestamp which goes down to the millisecond, so clashes are highly unlikely.
Quoted for posterity when your data goes down in flames because this assumption was incorrect.
You'll never guess what...
Cras wrote:
Doctor Glyndwr wrote:
Cras wrote:
The real world data has that data actually be a timestamp which goes down to the millisecond, so clashes are highly unlikely.
Quoted for posterity when your data goes down in flames because this assumption was incorrect.
You'll never guess what...
HHAHAHAH
HAHA
HAHAHAHAHHAHAHAHAHAHHAHA
21 days, too. Impressive.
Cras wrote:
Grim... wrote:
That said - ID columns, motherfucker!
I could add an ID column easily - but it amuses me more not to.
For the first ecommerce website I ever built, I relied on session IDs alone for something - can't remember what it was - on the basis that a session ID clash would be highly unlikely.
It took 8 years, but it happened.
It took 8 years, but it happened.
Gaywood and Grim... have done a powerful job of recruiting friends for their marathon runs, and there's now 1000 different people! We're tracking all their marathon runs in a database table that looks a bit like this:
Now I've got soooo much data, I want to know what the old data is so I can like get rid of it or something, save those precious bytes. But I'm powerful sexist. So what I'm looking for is a select query that will give me the races that are more than 3 months old if the runner is a dude, or more than 6 months old if the runner is a chick. And I want 10 results (if there are 10 results).
So something like this (pseudocode)
Where for the example table above I'd want the answer
Any ideas?
Code:
Race_ID Runner Date_of_Marathon Time Sex
1 Grim... 20170601 240 M
2 Gaywood 20170910 226 M
3 Jem 20161210 210 F
4 Mimi 20170522 220 F
1 Grim... 20170601 240 M
2 Gaywood 20170910 226 M
3 Jem 20161210 210 F
4 Mimi 20170522 220 F
Now I've got soooo much data, I want to know what the old data is so I can like get rid of it or something, save those precious bytes. But I'm powerful sexist. So what I'm looking for is a select query that will give me the races that are more than 3 months old if the runner is a dude, or more than 6 months old if the runner is a chick. And I want 10 results (if there are 10 results).
So something like this (pseudocode)
Code:
select top 10 race_id from races where (datediff(mm,getdate(),date_of_marathon)>6 if sex='m') or (datediff(mm,getdate(),date_of_marathon)>3 if sex='f') order by Date_of_marathon
Where for the example table above I'd want the answer
Code:
1
3
3
Any ideas?
Wait, can I just do this with ANDs and am just very stupid?
Code:
select top 10 race_id from races where (datediff(mm,getdate(),date_of_marathon)>6 and sex='m') or (datediff(mm,getdate(),date_of_marathon)>3 and sex='f') order by Date_of_marathon
It seems like the ANDs should work.
If not, you could use the CASE WHEN Statement:
WHERE (datediff(mm,getdate(),date_of_marathon)> CASE WHEN Sex='m' THEN 6 ELSE 3 END
If not, you could use the CASE WHEN Statement:
WHERE (datediff(mm,getdate(),date_of_marathon)> CASE WHEN Sex='m' THEN 6 ELSE 3 END
Yes, you can do that with just ANDs.
Okay, now we're getting complicated. I'm going to take this to real world because turning it into an analogy is too much of a pain. Basically, we've got 50 odd mobile apps that staff use. We have a non-usage policy where we blow away the app after 60 days of the user not opening it. We've now got a requirement to modify that - most apps will have 60 days, some will have no expiry at all, and others will have a custom value that could be any number of days. What that means is I need to build a custom sql query on the fly that can handle all those scenarios. This is what I've come up with. It's ugly as piss but I think it will work. Please sanity check it for me!
Breaks down into ((age>x and app=1) or (age>y and app=2)) which handles apps with custom expiry, (((age>x and app=1) or (age>y and app=2)) and ((app<>3) and (app<>4))) which then excludes those that have no expiry, and or (age>z and app<>1 and app<>2 and app<>3 and app<>4)) which handles all the apps with the standard expiry. Doing it like that I can feed any number of clauses within each grouping to build the query. This doesn't have to be fast, it's a background job.
Thoughts?
Code:
select * from apps where (((age>x and app=1) or (age>y and app=2)) and ((app<>3) and (app<>4))) or (age>z and app<>1 and app<>2 and app<>3 and app<>4))
Breaks down into ((age>x and app=1) or (age>y and app=2)) which handles apps with custom expiry, (((age>x and app=1) or (age>y and app=2)) and ((app<>3) and (app<>4))) which then excludes those that have no expiry, and or (age>z and app<>1 and app<>2 and app<>3 and app<>4)) which handles all the apps with the standard expiry. Doing it like that I can feed any number of clauses within each grouping to build the query. This doesn't have to be fast, it's a background job.
Thoughts?
Actually I can dump the middle clause, I think.
Code:
select * from apps where ((age>x and app=1) or (age>y and app=2)) or (age>z and app<>1 and app<>2 and app<>3 and app<>4))
I don't think you need to bracket those first two clauses up together do you? Assuming that, and for some extra readability, how about:
select *
from apps
where age>x and app=1
or age>y and app=2
or age>z and app not in (1,2,3,4)
select *
from apps
where age>x and app=1
or age>y and app=2
or age>z and app not in (1,2,3,4)
That should work. I tend to overly bracket because it helps my readability when I'm constructing long clauses
Speaking generally though, wouldn't you be better having a ref data table that's got an entry for each app and it's attendant expiry period and just doing a lookup on that at launch time rather than baking the logic into a specific query?
What Bamba said. Build another table with static data with expiry dates for the apps that aren't the default 60 days. It's going to be a lot better than expanding your nested WHERE clauses to 50+ tuples.
Love to, but I'm running with read-only DB rights for this, so I have to be able to build the whole thing into a single select query. I've got that data, it's sat in a config file on the box I'm building the query from, but it's not in SQL, hence having to construct the long complicated query on the fly.
Can't pull CSV files in? (Disclaimer, it's been 4 years since I touched any database any of the rest of you have ever heard of, I'm rusty.)
Doctor Glyndwr wrote:
Can't pull CSV files in? (Disclaimer, it's been 4 years since I touched any database any of the rest of you have ever heard of, I'm rusty.)
Those yanks gave you a new name did they, Rusty?
Doctor Glyndwr wrote:
Can't pull CSV files in? (Disclaimer, it's been 4 years since I touched any database any of the rest of you have ever heard of, I'm rusty.)
Not into the DB, no - which is the only place it'd be useful in the sql query itself
Ok, so you need a code generator that'll read your CSV and spit out the query.
Code generators are great and definitely never a hacky solution you later regret. You can do the thing. I believe in you.
Code generators are great and definitely never a hacky solution you later regret. You can do the thing. I believe in you.
Woohoo!
It's already 42 lines of code. This will definitely work and never go wrong.
Just generate the SQL query. Give a shit if it's big, SQL is good at that.
If it makes like easier, couldn't you do a separate query for each app? 50 is piss all.
If it makes like easier, couldn't you do a separate query for each app? 50 is piss all.
I only want 100 results though, across all apps - so if I was doing it in separate queries I'd struggle with how many to pull for each query. It's not awful, I think I've done it in a mere 49 lines of horrible code.
Code:
(SELECT * FROM x WHERE y = z)
UNION
(SELECT * FROM x WHERE a = b)
LIMIT 100
UNION
(SELECT * FROM x WHERE a = b)
LIMIT 100
Doctor Glyndwr wrote:
Ok, so you need a code generator that'll read your CSV and spit out the query.
Grim... wrote:
Just generate the SQL query.
Fuck you too.
Doctor Glyndwr wrote:
Doctor Glyndwr wrote:
Ok, so you need a code generator that'll read your CSV and spit out the query.
Grim... wrote:
Just generate the SQL query.
Fuck you too.
Doctor Glyndwr wrote:
It's going to be a lot better than expanding your nested WHERE clauses to 50+ tuples.
Ahem.
Grim... wrote:
Code:
(SELECT * FROM x WHERE y = z)
UNION
(SELECT * FROM x WHERE a = b)
LIMIT 100
UNION
(SELECT * FROM x WHERE a = b)
LIMIT 100
Well I've written it now ain't I?
Cras wrote:
Ahem.
I'm confused. What is happening?
Doctor Glyndwr wrote:
Cras wrote:
Ahem.
I'm confused. What is happening?
Confusion is happening, so you're actually completely on top of the situation.
Got to say, I'd have just used Excel to write the SQL code for me.
COME AT ME, PEOPLE WHO DON'T WANT TO LOSE ALL THEIR DATA
COME AT ME, PEOPLE WHO DON'T WANT TO LOSE ALL THEIR DATA
Doctor Glyndwr wrote:
Cras wrote:
Ahem.
I'm confused. What is happening?
You started off being anti-big where clauses, now you're claiming credit for being on team code factory.
Grim... wrote:
Got to say, I'd have just used Excel to write the SQL code for me.
COME AT ME, PEOPLE WHO DON'T WANT TO LOSE ALL THEIR DATA
COME AT ME, PEOPLE WHO DON'T WANT TO LOSE ALL THEIR DATA
I'd be interested to know how.
Here's my .csv:
So on C1 I bang in
And then I make it go all the way down the rows and then I copy it into a text editor and fix the end.
BOSH
Code:
1,2000
2,1000
3,10000
2,1000
3,10000
So on C1 I bang in
Code:
=CONCATENATE("(SELECT * FROM mytable WHERE id = '",A1,"' AND age > '",A2,"') UNION ")
And then I make it go all the way down the rows and then I copy it into a text editor and fix the end.
BOSH
Cras wrote:
You started off being anti-big where clauses, now you're claiming credit for being on team code factory.
I'm against writing a big WHERE clause by hand, because it'll be confusing to read and hence hard to update without introducing hard-to-catch bugs. But codegenning it from a CSV should be a way of coping with that.
Alternatively...
Cras wrote:
Love to, but I'm running with read-only DB rights for this, so I have to be able to build the whole thing into a single select query.
...find whatever prick is responsible for this constraint and bellow at them to be less shite.
Well for one it's a vendor product. Creating tables in vendor databases tends to come with feedback like 'removal of support'.
Also principle of least privilege, innit. If it can be done with minimal rights, do it with minimal rights.
Also principle of least privilege, innit. If it can be done with minimal rights, do it with minimal rights.
Can you create temp tables in a read only db?
Not in SQL Server, anyway
Back to your mega code then, I guess.
This might be a stupid question, but here goes.
I have two databases (same server), let's call them a & b.
I have a stored procedure on database a which updates a record in a table on database b.
I want to call this procedure from an external app, which works, but the calling user then doesn't have permission to update the table in database b.
I don't really want to permanently change the permissions on database b (because it's not mine, so they could get changed back and break stuff).
I was trying to grant and revoke permissions as part of the procedure, but that doesn't work as they're in different databases. Thinking about it though, I'm guessing even if that worked the user calling the stored procedure wouldn't have permission to grant themselves permission on the table in database b.
My plan b is a terrible clunky C# thing involving impersonation, but the former seems the better option if I can do it?
I have two databases (same server), let's call them a & b.
I have a stored procedure on database a which updates a record in a table on database b.
I want to call this procedure from an external app, which works, but the calling user then doesn't have permission to update the table in database b.
I don't really want to permanently change the permissions on database b (because it's not mine, so they could get changed back and break stuff).
I was trying to grant and revoke permissions as part of the procedure, but that doesn't work as they're in different databases. Thinking about it though, I'm guessing even if that worked the user calling the stored procedure wouldn't have permission to grant themselves permission on the table in database b.
My plan b is a terrible clunky C# thing involving impersonation, but the former seems the better option if I can do it?
I don't think there's really any way around the fact that if you're updating a table in b, you need to have rights to update a table in b.
Do you 'own' the server they're running on? Give the account SQL-wide rights and it won't matter what they do at the DB level. Not exactly good practice, but...
I can dish out the rights to anyone I want to, so I suppose yeah, that's probably easier.
For the purposes of this question let's say I'm tracking race times. Each row in the table is the name of a racer, and their time. There are many entries for each racer, for example:
Steve, 3:30:00
Karen, 3:46:00
Steve, 3:26:00
Steve, 3:29:00
Karen, 3:52:00
What I want to pull out is each racer's best time. I'm currently doing this:
Select * from (select racer, racetime, row_number() over (partition by racer order by racetime asc) as time_rank from races) a where a.time_rank=1
The problem is I've got six million rows and that query is bastard expensive. It's taking about 30s to run which is far from ideal.
Any ideas how I could do this more efficiently?
Cheers.
Steve, 3:30:00
Karen, 3:46:00
Steve, 3:26:00
Steve, 3:29:00
Karen, 3:52:00
What I want to pull out is each racer's best time. I'm currently doing this:
Select * from (select racer, racetime, row_number() over (partition by racer order by racetime asc) as time_rank from races) a where a.time_rank=1
The problem is I've got six million rows and that query is bastard expensive. It's taking about 30s to run which is far from ideal.
Any ideas how I could do this more efficiently?
Cheers.
why not
SELECT racer_name, min(time) FROM data GROUP BY 1
?
SELECT racer_name, min(time) FROM data GROUP BY 1
?