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

SQL Help
https://www.beexcellenttoeachother.com/forum/viewtopic.php?f=3&t=10861		 Page 1 of 3
Author:  Cras [ Tue Nov 01, 2016 16:30 ]
Post subject:  SQL Help
Hello SQL-knowing chums!

I'm banging my head against a wall on this one. I'm getting close, but it's just not giving me the right answer.

Gaywood and Grim... have been doing a load of marathons lately! They've stored all their race information in this table, 'races':

Quote:
Racer | RaceDate | Position | Completed
Grim... 10/3/16 4 Y
Grim... 12/3/16 4 Y
Grim... 1/2/16 1 Y
Grim... 10/5/16 NULL N
Grim... 5/4/16 6 Y
Gaywood 6/4/16 NULL N
Gaywood 17/1/16 2 Y
Gaywood 10/9/16 4 Y
Gaywood 4/8/16 4 Y
Gaywood 10/3/16 4 Y



Now, they're trying to get some friends to join in. So they've got another table too for all their friends, 'friends'. Most of their friends haven't done any races yet.

Quote:
Racer
Gaywood
Grim...
Curiosity
Bobbyaro


Now, they're pretty competitive, so they only care about the most recent results. Also they want to annoy their friends who haven't joined in yet. So this is the output they want:

Quote:
Racer | Position | Completed
Grim... NULL N
Gaywood 4 Y
Curiosity NUL:L NULL
Bobbyaro NULL NULL


This is proving challenging. Lots of having to do groupings, but then the groupings bugger up the filtering by most recent, etc. This is what I've got so far:

Code:
select f.Racer, r.Position, r.Completed
from   (select Racer, MAX(Races.[RaceDate]) as RecentRace, Position, Completed
             from races) group by races.Racer, Races.Position, Races.Completed) as r
right outer join friends as f
on r.Racer=f.Racer
group by f.Racer, r.Position, r.Completed


It's horridly complicated, probably wildly inefficient, and it still doesn't bloody work properly. Thoughts?
Author:  Grim... [ Tue Nov 01, 2016 17:04 ]
Post subject:  Re: SQL Help
What output are you trying for? I can't see why Grim... is NULL.

[edit] Oh, most recent, right? Hang on.
Author:  Grim... [ Tue Nov 01, 2016 17:08 ]
Post subject:  Re: SQL Help
[edit] Wait, that won't work
Author:  devilman [ Tue Nov 01, 2016 17:20 ]
Post subject:  Re: SQL Help
I'd be tempted to break the task up a little (because I'm crap at SQL mainly). Does it work up to the point you include the ones who haven't run yet?

Of course the real answer is to stop people running marathons.
Author:  Grim... [ Tue Nov 01, 2016 17:23 ]
Post subject:  Re: SQL Help
This seems to be it.

Code:
SELECT * FROM friends f
LEFT JOIN (SELECT * FROM races ORDER BY RaceDate DESC LIMIT 1) r ON r.Racer = f.Racer
Author:  Cras [ Tue Nov 01, 2016 17:33 ]
Post subject:  Re: SQL Help
Grim... wrote:
This seems to be it.

Code:
SELECT * FROM friends f
LEFT JOIN (SELECT * FROM races ORDER BY RaceDate DESC LIMIT 1) r ON r.Racer = f.Racer


I think that works for this data set, because the subquery returns Gaywood's race and it's the overall most recent race - but because that subquery returns only one result, if Grim... had completed his most recent race I don't think it would return that info, would it?
Author:  Cras [ Tue Nov 01, 2016 17:35 ]
Post subject:  Re: SQL Help
devilman wrote:
I'd be tempted to break the task up a little (because I'm crap at SQL mainly). Does it work up to the point you include the ones who haven't run yet?

Of course the real answer is to stop people running marathons.


Yeah - I mean I could just iterate through the values in friends and run a query for each one, but the real world usage for this will have hundreds of 'friends' so it seems pretty inefficient.
Author:  Mr Dave [ Tue Nov 01, 2016 17:37 ]
Post subject:  Re: SQL Help
Just get Myp to run it. Only 2 friends makes it easy.
Author:  Mr Russell [ Tue Nov 01, 2016 19:54 ]
Post subject:  Re: SQL Help
RemindMe! Tomorrow
Can you make a sqlfiddle out of it?
Author:  Grim... [ Tue Nov 01, 2016 22:05 ]
Post subject:  Re: SQL Help
Cras wrote:
Grim... wrote:
This seems to be it.

Code:
SELECT * FROM friends f
LEFT JOIN (SELECT * FROM races ORDER BY RaceDate DESC LIMIT 1) r ON r.Racer = f.Racer


I think that works for this data set, because the subquery returns Gaywood's race and it's the overall most recent race - but because that subquery returns only one result, if Grim... had completed his most recent race I don't think it would return that info, would it?

Should do. That's the order of the subquery.
Author:  Cras [ Tue Nov 01, 2016 22:38 ]
Post subject:  Re: SQL Help
I mean LIMIT 1 means the subquery can only contain 1 row. So when you join that with the friends table you can only possibly have 1 row that contains any data in the results.
Author:  Grim... [ Tue Nov 01, 2016 22:39 ]
Post subject:  Re: SQL Help
It contains one row per row, if that makes sense.
Author:  MrChris [ Tue Nov 01, 2016 22:41 ]
Post subject:  Re: SQL Help
Wtf.
Marathon whats?
Author:  Grim... [ Tue Nov 01, 2016 22:41 ]
Post subject:  Re: SQL Help
Note that on a large dataset I suspect it would be horrendously slow.

Bro do you even unique identifier?
Author:  Cras [ Tue Nov 01, 2016 22:42 ]
Post subject:  Re: SQL Help
Am I sorely misunderstanding how something in there works then? It doesn't rerun the subquery for each value of racer, does it?
Author:  Cras [ Tue Nov 01, 2016 22:44 ]
Post subject:  Re: SQL Help
Grim... wrote:
Note that on a large dataset I suspect it would be horrendously slow.

Bro do you even unique identifier?


How would an ID help, in this instance?
Author:  Grim... [ Tue Nov 01, 2016 22:47 ]
Post subject:  Re: SQL Help
Cras wrote:
Am I sorely misunderstanding how something in there works then? It doesn't rerun the subquery for each value of racer, does it?

Yup. It'll optimise hard, but that's essentially what it does.

A unique identifier on the friends table would let you join with an integer rather than a text field and index way better.

Even making a CRC 32 hash would help.

Obviously I'm assuming you have a rather larger dataset than the one presented.
Author:  Grim... [ Tue Nov 01, 2016 22:48 ]
Post subject:  Re: SQL Help
Also, note that I'm testing in MySql, and I assume you're using MSSql. If it doesn't work with subqueries in the same way it's a bit silly though.
Author:  MrChris [ Tue Nov 01, 2016 22:51 ]
Post subject:  Re: SQL Help
Marathon wank sessions? Marathon burger eating sessions? Marathon firefly watching? WHAT
Author:  Cras [ Tue Nov 01, 2016 23:02 ]
Post subject:  Re: SQL Help
Grim... wrote:
Also, note that I'm testing in MySql, and I assume you're using MSSql. If it doesn't work with subqueries in the same way it's a bit silly though.


It bloody should do. It uses TOP instead of LIMIT but fuck all difference there. I'm just astonished that I'm actually that wrong about how subqueries work.
Author:  Findus Fop [ Tue Nov 01, 2016 23:08 ]
Post subject:  Re: SQL Help
Is this for song wars?
Author:  Grim... [ Tue Nov 01, 2016 23:37 ]
Post subject:  Re: SQL Help
Cras wrote:
Grim... wrote:
Also, note that I'm testing in MySql, and I assume you're using MSSql. If it doesn't work with subqueries in the same way it's a bit silly though.


It bloody should do. It uses TOP instead of LIMIT but fuck all difference there. I'm just astonished that I'm actually that wrong about how subqueries work.

It's a fucking odd use of one, but it does work.
Author:  Cras [ Tue Nov 01, 2016 23:40 ]
Post subject:  Re: SQL Help
I'm convinced you're wrong, but I can't be arsed checking until tomorrow :D
Author:  Grim... [ Tue Nov 01, 2016 23:45 ]
Post subject:  Re: SQL Help
Bet you a pint.
Author:  Cras [ Tue Nov 01, 2016 23:51 ]
Post subject:  Re: SQL Help
Aye
Author:  Mr Russell [ Wed Nov 02, 2016 9:43 ]
Post subject:  Re: SQL Help
I am intrigued as to whether this works but have no pint based interest.
Author:  Cras [ Wed Nov 02, 2016 10:50 ]
Post subject:  Re: SQL Help
As I expected, everything except the data for the 1 most recent race is null in the output.

Unless mySQL really does do subqueries totally differently, that ain't gonna work.
Author:  Mr Russell [ Wed Nov 02, 2016 11:04 ]
Post subject:  Re: SQL Help
Do you want the most recent race for each unique racer where each racer could have a different most recent race date, or the most recent race for the whole result set that is shared by different racers?
Author:  Cras [ Wed Nov 02, 2016 11:06 ]
Post subject:  Re: SQL Help
Mr Russell wrote:
Do you want the most recent race for each unique racer where each racer could have a different most recent race date, or the most recent race for the whole result set that is shared by different racers?


The former. So the resulting output will have one line for each racer, and their corresponding most recent race results if they have ever done a race
Author:  Cras [ Wed Nov 02, 2016 11:07 ]
Post subject:  Re: SQL Help
I done a fiddle - can the URL just be shared?
Author:  Mr Russell [ Wed Nov 02, 2016 11:09 ]
Post subject:  Re: SQL Help
Cras wrote:
I done a fiddle - can the URL just be shared?

I think you copy and paste it, and any edits to that generate a new URL
Author:  Cras [ Wed Nov 02, 2016 11:10 ]
Post subject:  Re: SQL Help
Although fiddle appears to be proper fucked
Author:  Mr Russell [ Wed Nov 02, 2016 11:16 ]
Post subject:  Re: SQL Help
Does something like this work to get started, or have I really misunderstood it?

Code:
SELECT Races.Racer, f.RaceDate FROM Races
LEFT JOIN (
SELECT Racer, MAX(RaceDate) as RaceDate from Friends
Group BY Racer
) f ON f.racer = Races.Racer
Author:  Cras [ Wed Nov 02, 2016 11:20 ]
Post subject:  Re: SQL Help
Mr Russell wrote:
Does something like this work to get started, or have I really misunderstood it?

Code:
SELECT Races.Racer, f.RaceDate FROM Races
LEFT JOIN (
SELECT Racer, MAX(RaceDate) as RaceDate from Friends
Group BY Racer
) f ON f.racer = Races.Racer


That would work absolutely fine - but it won't get you the rest of the info, and that's where it starts falling apart.
Author:  Grim... [ Wed Nov 02, 2016 11:23 ]
Post subject:  Re: SQL Help
In that case I simply can't see a way of doing it without adding a unique ID to the racing table.

Code:
SELECT * FROM friends f
LEFT JOIN (
    SELECT * FROM races r
    WHERE id = (
        SELECT id FROM races r2
        WHERE r.racer = r2.racer
        ORDER BY r2.racedate DESC LIMIT 1
    )
) r3 ON r3.racer = f.racer
Author:  Doctor Glyndwr [ Wed Nov 02, 2016 11:25 ]
Post subject:  Re: SQL Help
Here's a SQLFiddle schema, if it helps anyone else.

ZOMG Spoiler! Click here to view!
CREATE TABLE races
(`Racer` varchar(7), `RaceDate` varchar(7), `Position` varchar(4), `Completed` varchar(1))
;

INSERT INTO races
(`Racer`, `RaceDate`, `Position`, `Completed`)
VALUES
('Grim...', '10/3/16', '4', 'Y'),
('Grim...', '12/3/16', '4', 'Y'),
('Grim...', '1/2/16', '1', 'Y'),
('Grim...', '10/5/16', NULL, 'N'),
('Grim...', '5/4/16', '6', 'Y'),
('Gaywood', '6/4/16', NULL, 'N'),
('Gaywood', '17/1/16', '2', 'Y'),
('Gaywood', '10/9/16', '4', 'Y'),
('Gaywood', '4/8/16', '4', 'Y'),
('Gaywood', '10/3/16', '4', 'Y')
;


CREATE TABLE friends
(`Racer` varchar(9))
;

INSERT INTO friends
(`Racer`)
VALUES
('Gaywood'),
('Grim...'),
('Curiosity'),
('Bobbyaro')
;


Edit -- oh, I see, you can't actually run any queries.
Author:  Grim... [ Wed Nov 02, 2016 11:30 ]
Post subject:  Re: SQL Help
It'd help more if SQL fiddle wasn't broken :(
Author:  Mr Russell [ Wed Nov 02, 2016 11:32 ]
Post subject:  Re: SQL Help
Cras wrote:
Mr Russell wrote:
Does something like this work to get started, or have I really misunderstood it?

Code:
SELECT Races.Racer, f.RaceDate FROM Races
LEFT JOIN (
SELECT Racer, MAX(RaceDate) as RaceDate from Friends
Group BY Racer
) f ON f.racer = Races.Racer


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

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
Author:  Grim... [ Wed Nov 02, 2016 11:34 ]
Post subject:  Re: SQL Help
Wait, shit, I think I've got the filthiest idea about how to do it without an ID table.
Author:  Grim... [ Wed Nov 02, 2016 11:36 ]
Post subject:  Re: SQL Help
Check out this shit:

Code:
SELECT * FROM friends f
LEFT JOIN (
    SELECT * FROM races r
    WHERE CONCAT(r.racer, r.racedate) = (
        SELECT CONCAT(r2.racer, r2.racedate) FROM races r2
        WHERE r.racer = r2.racer
        ORDER BY r2.racedate DESC LIMIT 1
    )
) r3 ON r3.racer = f.racer


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.
Author:  Cras [ Wed Nov 02, 2016 11:43 ]
Post subject:  Re: SQL Help
Grim... wrote:
Check out this shit:

Code:
SELECT * FROM friends f
LEFT JOIN (
    SELECT * FROM races r
    WHERE CONCAT(r.racer, r.racedate) = (
        SELECT CONCAT(r2.racer, r2.racedate) FROM races r2
        WHERE r.racer = r2.racer
        ORDER BY r2.racedate DESC LIMIT 1
    )
) r3 ON r3.racer = f.racer


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


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

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

Thanks all!
Author:  myp [ Wed Nov 02, 2016 11:45 ]
Post subject:  Re: SQL Help
Have you tried more inner joins? Inner joins always help.

I know SQL
Author:  Doctor Glyndwr [ Wed Nov 02, 2016 11:47 ]
Post subject:  Re: SQL Help
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.
Author:  Grim... [ Wed Nov 02, 2016 11:49 ]
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.

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!

Lonewolves wrote:
Have you tried more inner joins? Inner joins always help.

An inner join would be the opposite of what you want, as you need the null rows.
Author:  myp [ Wed Nov 02, 2016 11:59 ]
Post subject:  Re: SQL Help
Grim... wrote:
Lonewolves wrote:
Have you tried more inner joins? Inner joins always help.

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

Listen, leave this to the sequel experts
Author:  Grim... [ Wed Nov 02, 2016 12:02 ]
Post subject:  Re: SQL Help
Lonewolves wrote:
Grim... wrote:
Lonewolves wrote:
Have you tried more inner joins? Inner joins always help.

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
Author:  Cras [ Wed Nov 02, 2016 12:04 ]
Post subject:  Re: SQL Help
Grim... wrote:
That said - ID columns, motherfucker!


I could add an ID column easily - but it amuses me more not to.
Author:  myp [ Wed Nov 02, 2016 12:06 ]
Post subject:  Re: SQL Help
Have you tried truncating the table?
Author:  devilman [ Wed Nov 02, 2016 12:11 ]
Post subject:  Re: SQL Help
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

Quote:
Do you mind querying select * from the database to see what we could have?


A little knowledge is a dangerous thing.
Author:  Grim... [ Wed Nov 02, 2016 12:16 ]
Post subject:  Re: SQL Help
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!
Page 1 of 3 All times are UTC [ DST ]
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/