Be Excellent To Each Other
https://www.beexcellenttoeachother.com/forum/

SQL Help
https://www.beexcellenttoeachother.com/forum/viewtopic.php?f=3&t=10861		 Page 2 of 3
Author:  myp [ Wed Nov 02, 2016 12:22 ]
Post subject:  Re: SQL Help
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
Author:  Cras [ Wed Nov 23, 2016 16:04 ]
Post subject:  Re: SQL Help
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...
Author:  Doctor Glyndwr [ Wed Nov 23, 2016 16:10 ]
Post subject:  Re: SQL Help
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
Author:  Doctor Glyndwr [ Wed Nov 23, 2016 16:14 ]
Post subject:  Re: SQL Help
.
Author:  Cras [ Wed Nov 23, 2016 16:16 ]
Post subject:  Re: SQL Help
:DD
Author:  GazChap [ Wed Nov 23, 2016 16:23 ]
Post subject:  Re: SQL Help
21 days, too. Impressive.
Author:  Grim... [ Wed Nov 23, 2016 17:13 ]
Post subject:  Re: SQL Help
Cras wrote:
Grim... wrote:
That said - ID columns, motherfucker!


I could add an ID column easily - but it amuses me more not to.
Author:  Jem [ Wed Nov 23, 2016 17:38 ]
Post subject:  Re: SQL Help
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.
Author:  Cras [ Fri Oct 20, 2017 12:25 ]
Post subject:  Re: SQL Help
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:

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


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


Any ideas?
Author:  Cras [ Fri Oct 20, 2017 12:29 ]
Post subject:  Re: SQL Help
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
Author:  Mr Russell [ Fri Oct 20, 2017 12:37 ]
Post subject:  Re: SQL Help
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
Author:  GazChap [ Fri Oct 20, 2017 12:40 ]
Post subject:  Re: SQL Help
Yes, you can do that with just ANDs.
Author:  Cras [ Fri Oct 20, 2017 13:37 ]
Post subject:  Re: SQL Help
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!

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?
Author:  Cras [ Fri Oct 20, 2017 13:41 ]
Post subject:  Re: SQL Help
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))
Author:  Bamba [ Fri Oct 20, 2017 14:07 ]
Post subject:  Re: SQL Help
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)
Author:  Cras [ Fri Oct 20, 2017 14:12 ]
Post subject:  Re: SQL Help
That should work. I tend to overly bracket because it helps my readability when I'm constructing long clauses
Author:  Bamba [ Fri Oct 20, 2017 14:31 ]
Post subject:  Re: SQL Help
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?
Author:  Doctor Glyndwr [ Fri Oct 20, 2017 14:35 ]
Post subject:  Re: SQL Help
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.
Author:  Cras [ Fri Oct 20, 2017 14:42 ]
Post subject:  Re: SQL Help
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.
Author:  Doctor Glyndwr [ Fri Oct 20, 2017 14:44 ]
Post subject:  Re: SQL Help
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.)
Author:  DavPaz [ Fri Oct 20, 2017 14:46 ]
Post subject:  Re: SQL Help
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?
Author:  Cras [ Fri Oct 20, 2017 14:48 ]
Post subject:  Re: SQL Help
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
Author:  Doctor Glyndwr [ Fri Oct 20, 2017 14:57 ]
Post subject:  Re: SQL Help
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.
Author:  Cras [ Fri Oct 20, 2017 15:01 ]
Post subject:  Re: SQL Help
Woohoo!
Author:  Cras [ Fri Oct 20, 2017 15:02 ]
Post subject:  Re: SQL Help
It's already 42 lines of code. This will definitely work and never go wrong.
Author:  Grim... [ Fri Oct 20, 2017 15:12 ]
Post subject:  Re: SQL Help
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.
Author:  Cras [ Fri Oct 20, 2017 15:26 ]
Post subject:  Re: SQL Help
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.
Author:  Grim... [ Fri Oct 20, 2017 16:14 ]
Post subject:  Re: SQL Help
Code:
(SELECT * FROM x WHERE y = z)
UNION
(SELECT * FROM x WHERE a = b)
LIMIT 100
Author:  Doctor Glyndwr [ Fri Oct 20, 2017 16:36 ]
Post subject:  Re: SQL Help
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.
Author:  Cras [ Fri Oct 20, 2017 16:39 ]
Post subject:  Re: SQL Help
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.
Author:  Cras [ Fri Oct 20, 2017 16:40 ]
Post subject:  Re: SQL Help
Grim... wrote:
Code:
(SELECT * FROM x WHERE y = z)
UNION
(SELECT * FROM x WHERE a = b)
LIMIT 100


Well I've written it now ain't I?
Author:  Doctor Glyndwr [ Fri Oct 20, 2017 16:42 ]
Post subject:  Re: SQL Help
Cras wrote:
Ahem.

I'm confused. What is happening?
Author:  Bamba [ Fri Oct 20, 2017 16:51 ]
Post subject:  Re: SQL Help
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.
Author:  Grim... [ Fri Oct 20, 2017 17:03 ]
Post subject:  Re: SQL Help
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
Author:  Cras [ Fri Oct 20, 2017 17:06 ]
Post subject:  Re: SQL Help
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.
Author:  Cras [ Fri Oct 20, 2017 17:07 ]
Post subject:  Re: SQL Help
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


I'd be interested to know how.
Author:  Grim... [ Fri Oct 20, 2017 17:10 ]
Post subject:  Re: SQL Help
Here's my .csv:
Code:
1,2000
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
Author:  Doctor Glyndwr [ Fri Oct 20, 2017 17:11 ]
Post subject:  Re: SQL Help
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.
Author:  Cras [ Fri Oct 20, 2017 17:35 ]
Post subject:  Re: SQL Help
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.
Author:  Joans [ Fri Oct 20, 2017 18:24 ]
Post subject:  Re: SQL Help
Can you create temp tables in a read only db?
Author:  Cras [ Fri Oct 20, 2017 18:28 ]
Post subject:  Re: SQL Help
Nope :(
Author:  Cras [ Fri Oct 20, 2017 18:29 ]
Post subject:  Re: SQL Help
Not in SQL Server, anyway
Author:  Joans [ Fri Oct 20, 2017 18:30 ]
Post subject:  Re: SQL Help
Back to your mega code then, I guess.
Author:  Joans [ Thu Dec 21, 2017 11:06 ]
Post subject:  Re: SQL Help
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?
Author:  Cras [ Thu Dec 21, 2017 11:26 ]
Post subject:  Re: SQL Help
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.
Author:  Cras [ Thu Dec 21, 2017 11:29 ]
Post subject:  Re: SQL Help
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...
Author:  Joans [ Thu Dec 21, 2017 12:05 ]
Post subject:  Re: SQL Help
I can dish out the rights to anyone I want to, so I suppose yeah, that's probably easier.
Author:  Cras [ Mon Mar 04, 2019 15:59 ]
Post subject:  Re: SQL Help
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.
Author:  Doctor Glyndwr [ Mon Mar 04, 2019 16:22 ]
Post subject:  Re: SQL Help
why not

SELECT racer_name, min(time) FROM data GROUP BY 1

?
Author:  Grim... [ Mon Mar 04, 2019 17:04 ]
Post subject:  Re: SQL Help
Code:
SELECT racer_name, MIN(time) FROM table GROUP BY racer_name

is safer.
Page 2 of 3 All times are UTC [ DST ]
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/