Teeworlds - general

[quakenet] <jxsl13> any sql experts here :D?

14:47

[quakenet] <jxsl13> https://i.imgur.com/93tyM2b.png

14:47

[quakenet] <pinkieval> hmm?

14:47

[quakenet] <jxsl13> I have git cTick = connected tick and the actual tick

14:47

[quakenet] <jxsl13> got*

14:48

[quakenet] <jxsl13> and I want all the rows with the minimum distance at one exact pTick

14:49

[quakenet] <jxsl13> like row 1, 5,9

group by ptick and sort ?

[quakenet] <minus> jxsl13: ORDER BY (cTick - pTick) ASC?

14:51

[quakenet] <jxsl13> dist is actually the distance

14:51

[quakenet] <minus> oh

14:51

[quakenet] <jxsl13> but sadly didnt work

14:51

[quakenet] <jxsl13> or :O

14:52

[quakenet] <jxsl13> wait

14:52

[quakenet] <minus> you want the rows with smallest dist for each distinct pTick?

14:52

[quakenet] <jxsl13> yeah

14:52

[quakenet] <pinkieval> SELECT DISTINCT TOP 1 dist FROM table ORDER BY dist

14:52

[quakenet] <pinkieval> er wait no

14:52

[quakenet] <jxsl13> prink stackoverflow :D?

14:53

[quakenet] <pinkieval> SELECT DISTINCT TOP 1 pTick FROM table ORDER BY dist

14:53

[quakenet] <jxsl13> pinkieval *

14:53

[quakenet] <jxsl13> sqlite*

14:53

[quakenet] <minus> paste a .sql script with DDL & DML somewhere

14:53

[quakenet] <minus> sqlite? good fucking luck

14:53

[quakenet] <jxsl13> x)

14:54

[quakenet] <jxsl13> https://pastebin.com/npcFhvmh

[SQL] select * from (select NickName, c.Tick as 'cTick', p....

14:54

[quakenet] <jxsl13> that's the stuff for now :O

14:55

[quakenet] <minus> that's not DDL+DML tho

14:55

[quakenet] <jxsl13> sqlite db -> https://files.johnbehm.de/share/isG4760l

14:56

[quakenet] <minus> still not exactly what i asked for

14:56

[quakenet] <jxsl13> table structure?

14:56

[quakenet] <minus> but it works

14:56

[quakenet] <jxsl13> is in the db ._.

14:57

[quakenet] <minus> i had intended to throw the data at postgres

14:57

[quakenet] <minus> because postgres is king

14:59

[quakenet] <pinkieval> minus: ever heard of some quote about a cannon and a fly?

14:59

[quakenet] <minus> nope

14:59

[quakenet] <minus> but i've heard about cannons and sparrows

15:00

[quakenet] <jxsl13> https://files.johnbehm.de/share/xtMHQWAn

15:00

[quakenet] <pinkieval> oh right, that one

15:00

[quakenet] <jxsl13> mysql dump, hopefully postgres takes it and there is nothing mysql preproitary stuff ._.

15:01

[quakenet] <minus> what exactly are you tring to find out, jxsl13?

15:01

[quakenet] <jxsl13> assiciate every tick/ClientID with a nickname

15:01

[quakenet] <minus> but i already said the sqlite db works too :<

15:01

[quakenet] <jxsl13> associate*

15:01

[quakenet] <minus> what for?

15:02

[quakenet] <jxsl13> to know the person moving the character :D

15:02

[quakenet] <jxsl13> as adding that information to every line would not be wise :c

15:02

[quakenet] <pinkieval> SELECT * FROM table GROUP BY pTick HAVING MIN(dist);

15:02

[quakenet] <minus> you want to join the player name onto each input from playerinput?

15:02

[quakenet] <jxsl13> yep

15:03

[quakenet] <minus> if you have influence on how the data is generated i strongly suggest you generate a unique ID on joining the game and use that to associate actions

15:03

[quakenet] <pinkieval> yeah

15:04

[quakenet] <jxsl13> ok :'C

15:07

[quakenet] <minus> because then you can just select from actions join players using (id)

15:07

[quakenet] <jxsl13> thanks for the tipp tho :D

15:07

[quakenet] <minus> bonus: it's much faster

15:07

[quakenet] <jxsl13> true that

Horrible joined the server. 2018-03-17 23:38:02Z