Advertisement
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQLite, organize

Comments in 'Plugin Development' started by AndrewBit, Jun 18, 2016.

  1. AndrewBit
    Offline

    AndrewBit Notable Member

    Joined:
    Jun 18, 2015
    Posts:
    435
    Minecraft User:
    AndrewBit4
    Anyone here know how to organize a SQLite database by, as example, kills?
    I mean, if each column have 3 values, "player", "kills" and "top", how to organize it like, the player with more kills get the top 1.
  2. AndrewBit
    Offline

    AndrewBit Notable Member

    Joined:
    Jun 18, 2015
    Posts:
    435
    Minecraft User:
    AndrewBit4
    Looks like nobody here is enoughly good to answer this :p
  3. AndrewBit
    Offline

    AndrewBit Notable Member

    Joined:
    Jun 18, 2015
    Posts:
    435
    Minecraft User:
    AndrewBit4
    Looks like SQLite isn't a good idea to do this, any better method?
  4. SOFe
    Offline

    SOFe Banned

    Joined:
    May 28, 2016
    Posts:
    386
    Minecraft User:
    Herobrine
    Common sense, but you need some natural creativity to come up with this.

    Code:
    CREATE TABLE tbl (
      player TEXT,
      kills INTEGER
    )
    
    Find player kills and rank in kills, given name:
    Code:
    SELECT kills, (SELECT COUNT(*) + 1 FROM tmp WHERE tmp.kills > kills) AS rank FROM tbl WHERE player = :s
    
    If subqueries are too confusing, do two queries (but less efficient):

    1. Find the player's kills.
    Code:
    SELECT kills FROM tbl WHERE player = :s
    
    where :s is the player name
    2. Find number of players with more kills than the player.
    Code:
    SELECT COUNT(*) AS count FROM tbl WHERE kills > :d
    
    where :d is the number of kills
    3. Add 1 to the `count` obtained.

    Hint: For optimization,
    Code:
    CREATE INDEX tbl_kills ON tbl (kills)
  5. AndrewBit
    Offline

    AndrewBit Notable Member

    Joined:
    Jun 18, 2015
    Posts:
    435
    Minecraft User:
    AndrewBit4
    And, how I can get the 50 top players in an array, as example? (Yup, I am a SQLite noob)
  6. Primus
    Offline

    Primus Notable Member

    Joined:
    Apr 7, 2015
    Posts:
    1,470
    Minecraft User:
    PrimusLV

Share This Page

Advertisement