Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Coder
    Join Date
    May 2012
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    mysql order by(select)

    mysql order by(select)

    SELECT min(time), map, date FROM records GROUP BY map ORDER BY date desc
    Code:
    time	map	date
    
    10.00	map_1	15.10.2013
    20.00	map_1	20.10.2013
    30.00	map_2	17.10.2013
    40.00	map_2	12.10.2013

    Result:

    10.00 map1 20.10.2013
    30.00 map2 17.10.2013


    i need:

    10.00 map1 15.10.2013
    30.00 map2 17.10.2013

    something like...
    Code:
    SELECT min(time), map, date FROM records GROUP BY map ORDER BY (SELECT date FROM records WHERE time=min(time)) desc
    ?

  • #2
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,942
    Thanks
    2
    Thanked 169 Times in 164 Posts
    Is this what you're needing?

    mysql> select * from records;
    +-------+-------+------------+
    | time | map | date |
    +-------+-------+------------+
    | 10.00 | map_1 | 15.10.2013 |
    | 20.00 | map_1 | 20.10.2013 |
    | 30.00 | map_2 | 17.10.2013 |
    | 40.00 | map_2 | 12.10.2013 |
    +-------+-------+------------+
    4 rows in set (0.00 sec)

    mysql>
    mysql> SELECT min(time), map, date FROM records GROUP BY map ORDER BY time asc;
    +-----------+-------+------------+
    | min(time) | map | date |
    +-----------+-------+------------+
    | 10.00 | map_1 | 15.10.2013 |
    | 30.00 | map_2 | 17.10.2013 |
    +-----------+-------+------------+
    2 rows in set (0.00 sec)

    I get the same result if I order by date asc.

    mysql> SELECT min(time), map, date FROM records GROUP BY map ORDER BY date asc;
    +-----------+-------+------------+
    | min(time) | map | date |
    +-----------+-------+------------+
    | 10.00 | map_1 | 15.10.2013 |
    | 30.00 | map_2 | 17.10.2013 |
    +-----------+-------+------------+
    2 rows in set (0.00 sec)
    Last edited by FishMonger; 11-01-2013 at 09:06 PM.

  • #3
    New Coder
    Join Date
    May 2012
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i get only max or min date, something like this:

    Code:
    time	map	date
    
    10.00	map_1	17.10.2013
    20.00	map_1	12.10.2013
    30.00	map_1	20.10.2013
    40.00	map_1	15.10.2013
    50.00	map_1	14.10.2013

    i get:

    10.00 map_1 12.10.2013

    or:

    10.00 map_1 20.10.2013

    but i need:

    10.00 map_1 17.10.2013

  • #4
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,942
    Thanks
    2
    Thanked 169 Times in 164 Posts
    You must have a bug in the way you're parsing/processing the return results because mysql would not return those results based on the data you show.

    Please post your code.

  • #5
    New Coder
    Join Date
    May 2012
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i run SQL query in phpmyadmin.
    Try the last table and u get date only 20.10.2013(max) or 12.10.2013(min)

    like i said, need something like this:

    ORDER BY ( SELECT date FROM records WHERE time=min(time) ) desc

  • #6
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    When you use GROUP BY you need to use aggregate functions on ALL of the fields not being grouped.

    SELECT min(time), map, date FROM records GROUP BY map ORDER BY date desc
    needs a function applied to date or the date returned cannot be controlled. Only map is being frouped and so that's the only field that doesn't need a function.

    SELECT min(time), map, min(date) FROM records GROUP BY map ORDER BY date desc

    You can't use WHERE time=min(time) as WHERE runs before ORDER BY. You would need to use HAVING time=min(time)
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #7
    New Coder
    Join Date
    May 2012
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    SELECT min(time), map, min(date) FROM records GROUP BY map ORDER BY date desc

    I DONT NEED MINIMUM DATE!!!

    Anyone help? this date is so random :S
    Last edited by Radeom; 11-02-2013 at 01:24 AM.

  • #8
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,942
    Thanks
    2
    Thanked 169 Times in 164 Posts
    What data type did you use for the date field?

    Please post your code so we can see how you're retrieving and outputting the data.

    Also, please post the output of this mysql command and if possible run it directly in the mysql cli.

    Code:
    show create table records;

  • #9
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Radeom View Post
    SELECT min(time), map, min(date) FROM records GROUP BY map ORDER BY date desc

    I DONT NEED MINIMUM DATE!!!

    Anyone help? this date is so random :S
    So which one of the dates do you want - you need to use the correct aggregate function for the one you want otherwise it will give you whichever one it likes and you will have no control over it.

    If you don't want min(date) then the alternatives are

    avg(date)
    count(date)
    max(date)
    sum(date)
    group_concat(date)
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #10
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,942
    Thanks
    2
    Thanked 169 Times in 164 Posts
    Also, please post the output of this command so that I see if you're working with a small dataset like your post seems to indicate or if your working with a much larger dataset.

    Code:
    select count(*) from records;

  • #11
    New Coder
    Join Date
    May 2012
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
     `map` varchar(64) NOT NULL,
     `profile` varchar(64) NOT NULL,
     `time` decimal(65,5) NOT NULL,
     `date` datetime NOT NULL
    
    count(*) 3988

  • #12
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,942
    Thanks
    2
    Thanked 169 Times in 164 Posts
    Quote Originally Posted by Radeom View Post
    Code:
     `map` varchar(64) NOT NULL,
     `profile` varchar(64) NOT NULL,
     `time` decimal(65,5) NOT NULL,
     `date` datetime NOT NULL
    
    count(*) 3988
    Thank you for posting that info. Now please post your complete code that executes the select statement and retrieves and outputs the results.

  • #13
    New Coder
    Join Date
    May 2012
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Like i said run SQL query in phpmyadmin and u need all 3988 fields to give me correct query? what?

    map like:
    map1
    map2
    ...(about 300 different)

    profile:
    steam_
    (about 400 different)

    time:
    12.00000
    34.43433
    (about 4000 different)

    date:
    2013-11-02 18:35:29
    2013-11-02 18:34:28
    2012-06-25 23:32:16
    (about 4000 different)

  • #14
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,942
    Thanks
    2
    Thanked 169 Times in 164 Posts
    The reason I was asking for you to post your code was because the date format that you were saying you were getting is not the format that mysql would return by default. Which was telling me that you were modifying the return results in some way and I wanted to see exactly what you were doing. In addition to that, the records you said were being returned weren't among the sample records that you provided and without additional explanation, the only assumption I could make was that you had a bug in the php code that processed the result set which was altering the results.

    The more accurate you are in representing your data the easier it will be for us to work out the solution.

    I don't use phpmyadmin but when I have more free time I will work up a more complete sample database to test against.

  • #15
    New Coder
    Join Date
    May 2012
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    from game server:
    get_time("%Y%m%d%H%M%S")

    mysql(datetime) date format is same?

    And still, i dont have any php code yet i first try this query in database(phpadmin)
    Maybe anyone know how to make this **** in php?


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •