Be Excellent To Each Other

It's one of the first things I learnt at sequel school

You'll never guess what...

HHAHAHAH

HAHA

HAHAHAHAHHAHAHAHAHAHHAHA

.

21 days, too. Impressive.

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.

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?

Wait, can I just do this with ANDs and am just very stupid?

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

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?

Actually I can dump the middle clause, I think.

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)

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.)

Those yanks gave you a new name did they, 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.

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.

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.

Fuck you too.

Ahem.

Well I've written it now ain't I?

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

You started off being anti-big where clauses, now you're claiming credit for being on team code factory.

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

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...

...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.

Can you create temp tables in a read only db?

Nope

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 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.

why not

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

?

is safer.