CODEWITHSUNDEEP
mysql
Alberto Acuña
Alberto Acuña

Reputation: 535

select distinct from table mysql

I cant filter in a good way my table, this is my table:

+----------+-----------+-------+------+
| PersonID | FirstName | Level | Time |
+----------+-----------+-------+------+
|    1     |   Beru    |   51  |  22  |
|    2     |   Pepe    |   42  |  13  |
|    3     |   Tom     |   30  |  15  |
|    4     |   Beru    |   51  |  32  |
|    5     |   Tom     |   28  |  15  |
|    ....  |    ...    |  .... |  ... |
+----------+-----------+-------+------+
    ........................... more players

I want to order it by Bigger Level and Time, but not repeating FirstName and only 5 first, its posible? thanks.

So my Output Should be some stuff like this:

- FirstName - Level - Time
- Beru      - 51    - 32
- Pepe      - 42    - 13
- Tom       - 30    - 15
-  .... more players...

I hope someone can help me. I know what to use, but i dont know how to use it.

SELECT o.FirstName, o.Level, o.Time
FROM myTable o
GROUP BY FirstName
ORDER BY o.Level*100+o.Time  DESC
LIMIT 0,10

thanks everyone, obviusly that mysql script doesnt work, i hope you know how to do it, :)

Upvotes: 1

Views: 62

Answers (2)

Kickstart
Kickstart

Reputation: 21513

This can be done with a fiddle using GROUP_CONCAT:-

SELECT FirstName, 
        SUBSTRING_INDEX(GROUP_CONCAT(Level ORDER BY Level DESC, Time DESC), ',', 1) AS Level,
        SUBSTRING_INDEX(GROUP_CONCAT(Time ORDER BY Level DESC, Time DESC), ',', 1) AS Time
FROM myTable
GROUP BY FirstName

To order that in the right order:-

SELECT FirstName, 
        SUBSTRING_INDEX(GROUP_CONCAT(Level ORDER BY Level DESC, Time DESC), ',', 1) AS Level,
        SUBSTRING_INDEX(GROUP_CONCAT(`Time` ORDER BY Level DESC, Time DESC), ',', 1) AS `Time`
FROM myTable
GROUP BY FirstName
ORDER BY Level*100+`Time`  DESC
LIMIT 10

SQL fiddle for it here:-

http://www.sqlfiddle.com/#!9/03457/1

Note that this ordering won't be that efficient (no way to use indexes).

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use variables in order to get the greatest-per-player record:

SELECT PersonID, FirstName, Level, Time
FROM (
  SELECT PersonID, FirstName, Level, Time,
         @rn := IF(@name = FirstName, @rn + 1,
                   IF(@name := FirstName, 1, 1)) AS rn                 
  FROM mytable
  CROSS JOIN (SELECT @rn := 0, @name := '') AS vars
  ORDER BY FirstName, Level DESC, Time DESC) AS t
WHERE rn = 1
ORDER BY FirstName LIMIT 0, 5

Demo here

Upvotes: 1

Related Questions