Be Excellent To Each Other

I think you copy and paste it, and any edits to that generate a new URL

Although fiddle appears to be proper fucked

Does something like this work to get started, or have I really misunderstood it?

That would work absolutely fine - but it won't get you the rest of the info, and that's where it starts falling apart.

In that case I simply can't see a way of doing it without adding a unique ID to the racing table.

Here's a SQLFiddle schema, if it helps anyone else.



Edit -- oh, I see, you can't actually run any queries.

It'd help more if SQL fiddle wasn't broken

OK, so you need to rank the whole races table but for each racer.
This StackOverflow question looks similar:
http://stackoverflow.com/questions/7473 ... h-customer

And this one looks to be very similar to how Grim... was suggesting to do it:
http://stackoverflow.com/questions/3313 ... h-customer

Wait, shit, I think I've got the filthiest idea about how to do it without an ID table.

Check out this shit:



Probably going to run into all sorts of trouble if you have two races on the same day, mind.

[edit] Oh, except then it won't order it anyway, so I don't think it would actually be a problem.

Works a treat, but yeah it's a little bit filthy

The real world data has that data actually be a timestamp which goes down to the millisecond, so clashes are highly unlikely.

Thanks all!

Have you tried more inner joins? Inner joins always help.

I know SQL

Quoted for posterity when your data goes down in flames because this assumption was incorrect.

I don't think it matters - the date is key for the ordering, so if you've got matching dates it's not going to work anyway.

That said - ID columns, motherfucker!


An inner join would be the opposite of what you want, as you need the null rows.

Listen, leave this to the sequel experts

k, soz

I could add an ID column easily - but it amuses me more not to.

Have you tried truncating the table?

I'm reminded of a recent request from one of my colleagues in Canada who need a custom data extract doing. I told him I'd need to look how the data was structured, but before I'd had chance to look into it, I got this reply



A little knowledge is a dangerous thing.

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

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.