Be Excellent To Each Other

And, you know, party on. Dude.

All times are UTC [ DST ]




Reply to topic  [ 110 posts ]  Go to page 1, 2, 3  Next
Author Message
 Post subject: SQL Help
PostPosted: Tue Nov 01, 2016 16:30 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
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?

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 17:04 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
What output are you trying for? I can't see why Grim... is NULL.

[edit] Oh, most recent, right? Hang on.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 17:08 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
[edit] Wait, that won't work

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 17:20 
User avatar
Comfortably Dumb

Joined: 30th Mar, 2008
Posts: 10753
Location: Sunny Stoke
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.

_________________
Consolemad | Under Logic
Curse, the day is long
Realise you don't belong


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 17:23 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
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

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 17:33 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
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?

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 17:35 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
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.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 17:37 
User avatar
Paws for thought

Joined: 27th Mar, 2008
Posts: 16497
Location: Just Outside That London, England, Europe
Just get Myp to run it. Only 2 friends makes it easy.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 19:54 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 11731
RemindMe! Tomorrow
Can you make a sqlfiddle out of it?

_________________
Always proof read carefully in case you any words out

Do some price comparison on games, DVDs, books or CDs at Find It Cheapest.com
Image


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 22:05 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
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.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 22:38 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
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.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 22:39 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
It contains one row per row, if that makes sense.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 22:41 
User avatar
INFINITE POWAH

Joined: 1st Apr, 2008
Posts: 30334
Wtf.
Marathon whats?

_________________
http://www.thehomeofawesome.com/
Eagles soar, but weasels don't get sucked into jet engines.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 22:41 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
Note that on a large dataset I suspect it would be horrendously slow.

Bro do you even unique identifier?

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 22:42 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
Am I sorely misunderstanding how something in there works then? It doesn't rerun the subquery for each value of racer, does it?

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 22:44 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
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?

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 22:47 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
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.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 22:48 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
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.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 22:51 
User avatar
INFINITE POWAH

Joined: 1st Apr, 2008
Posts: 30334
Marathon wank sessions? Marathon burger eating sessions? Marathon firefly watching? WHAT

_________________
http://www.thehomeofawesome.com/
Eagles soar, but weasels don't get sucked into jet engines.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 23:02 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
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.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 23:08 
User avatar
Prince of Fops

Joined: 14th May, 2009
Posts: 2745
Is this for song wars?


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 23:37 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
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.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 23:40 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
I'm convinced you're wrong, but I can't be arsed checking until tomorrow :D

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 23:45 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
Bet you a pint.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Tue Nov 01, 2016 23:51 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
Aye

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 9:43 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 11731
I am intrigued as to whether this works but have no pint based interest.

_________________
Always proof read carefully in case you any words out

Do some price comparison on games, DVDs, books or CDs at Find It Cheapest.com
Image


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 10:50 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
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.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:04 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 11731
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?

_________________
Always proof read carefully in case you any words out

Do some price comparison on games, DVDs, books or CDs at Find It Cheapest.com
Image


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:06 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
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

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:07 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
I done a fiddle - can the URL just be shared?

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:09 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 11731
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

_________________
Always proof read carefully in case you any words out

Do some price comparison on games, DVDs, books or CDs at Find It Cheapest.com
Image


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:10 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
Although fiddle appears to be proper fucked

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:16 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 11731
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

_________________
Always proof read carefully in case you any words out

Do some price comparison on games, DVDs, books or CDs at Find It Cheapest.com
Image


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:20 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
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.

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:23 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
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

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:25 
User avatar

Joined: 30th Mar, 2008
Posts: 32190
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.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:30 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
It'd help more if SQL fiddle wasn't broken :(

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:32 
Awesome
User avatar
Yes

Joined: 6th Apr, 2008
Posts: 11731
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

_________________
Always proof read carefully in case you any words out

Do some price comparison on games, DVDs, books or CDs at Find It Cheapest.com
Image


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:34 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
Wait, shit, I think I've got the filthiest idea about how to do it without an ID table.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:36 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
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.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:43 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
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!

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:45 
User avatar
Can't re-member

Joined: 27th Mar, 2008
Posts: 53296
Location: Liberty City
Have you tried more inner joins? Inner joins always help.

I know SQL

_________________
I am currently under construction.
Thank you for your patience.


Image


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:47 
User avatar

Joined: 30th Mar, 2008
Posts: 32190
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.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:49 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
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.

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 11:59 
User avatar
Can't re-member

Joined: 27th Mar, 2008
Posts: 53296
Location: Liberty City
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

_________________
I am currently under construction.
Thank you for your patience.


Image


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 12:02 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
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

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 12:04 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 47913
Grim... wrote:
That said - ID columns, motherfucker!


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

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 12:06 
User avatar
Can't re-member

Joined: 27th Mar, 2008
Posts: 53296
Location: Liberty City
Have you tried truncating the table?

_________________
I am currently under construction.
Thank you for your patience.


Image


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 12:11 
User avatar
Comfortably Dumb

Joined: 30th Mar, 2008
Posts: 10753
Location: Sunny Stoke
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.

_________________
Consolemad | Under Logic
Curse, the day is long
Realise you don't belong


Top
 Profile  
 
 Post subject: Re: SQL Help
PostPosted: Wed Nov 02, 2016 12:16 
SupaMod
User avatar
Est. 1978

Joined: 27th Mar, 2008
Posts: 66918
Location: Your Mum
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!

_________________
Grim... wrote:
I wish Craster had left some girls for the rest of us.


Top
 Profile  
 
Display posts from previous:  Sort by  
Reply to topic  [ 110 posts ]  Go to page 1, 2, 3  Next

All times are UTC [ DST ]


Who is online

Users browsing this forum: Mr Chonks and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search within this thread:
You are using the 'Ted' forum. Bill doesn't really exist any more. Bogus!
Want to help out with the hosting / advertising costs? That's very nice of you.
Are you on a mobile phone? Try http://beex.co.uk/m/
RIP, Owen.

Powered by a very Grim... version of phpBB © 2000, 2002, 2005, 2007 phpBB Group.