As first sort, and then sgruppirovany sample SQL?

Need to find one last action users, and get first

SELECT
*
FROM
(
SELECT
act.*,
users.name
users.lname,
users.ava_file
FROM
act
 INNER JOIN users WHERE act.date > "1517427936" AND act.date < "1517428356" AND act.creator = users.id
 ORDER BY
act.date
DESC
) a
GROUP BY
 a.creator


SELECT
b.*,
users.name
users.lname,
users.ava_file
FROM
(
SELECT
a.*
FROM
(
SELECT
act.*
FROM
act
WHERE
 act.date > "1517427936" AND act.date < "1517428356"
 ORDER BY
act.id
DESC
 ) a
GROUP BY
a.creator
) b
INNER JOIN users b WHERE.creator = users.id


which queries should I use? the second day racking my head over the puzzle.
June 8th 19 at 17:12
3 answers
June 8th 19 at 17:14
grade in calculations as Well, it's just a way o

table draw
Grade in calculations not to Train when using window functions. - Benedict_Shiel commented on June 8th 19 at 17:17
CREATE TABLE `act` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `date` int(10) unsigned NOT NULL,
 `creator` int(10) unsigned NOT NULL,
 `value` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

CREATE TABLE `users` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `invited_by_id` int(11) unsigned DEFAULT NULL,
 `creator_id` int(11) unsigned DEFAULT NULL,
 `name` varchar(128) NOT NULL,
 `lname` varchar(128) NOT NULL,
 `email` varchar(128) NOT NULL,
 the `phone` varchar(128) NOT NULL,
 `ava_file` varchar(100) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8
- zoie_Schoen commented on June 8th 19 at 17:20
June 8th 19 at 17:16
Something like this?
CREATE TEMPORARY TABLE `act_tmp`

SELECT
`act`.*
FROM
`act`
LEFT JOIN(
SELECT
`creator`,
 MAX(`date`) AS `date`
FROM
`act`
WHERE
 `act`.`date` > "1517427936" AND `act`.`date` < "1517428356"
 GROUP BY
`creator`
) AS `act_b`
ON
 `act`.`creator` = `act_b`.`creator` AND `act`.`date` = `act_b`.`date`
WHERE
 `act_b`.`creator` IS NOT NULL
GROUP BY
`act`.`creator`;

SELECT
`act_tmp`.*,
`users`.`name`,
`users`.`lname`,
`users`.`ava_file`
FROM
`users`
LEFT JOIN `act_tmp` ON `users`.`id` = `act_tmp`.`creator`
WHERE
 `act_tmp`.`creator` IS NOT NULL
GROUP BY
 `users`.`id`
in my opinion, too hard it turned out - Benedict_Shiel commented on June 8th 19 at 17:19
it would be easier if grouping selected data from the row where max(col), but max() live their lives. Generation act_tmp is an attempt to solve this problem (instead, you can certainly put in a nested select, but the query will not be less). - zoie_Schoen commented on June 8th 19 at 17:22
and how many nested selectors be? - zoie_Schoen commented on June 8th 19 at 17:25
,
Two
SELECT
`act_tmp`.*,
`users`.`name`,
`users`.`lname`,
`users`.`ava_file`
FROM
`users`
LEFT JOIN(
 SELECT `act`.*
FROM
`act`
 LEFT JOIN(
SELECT
`creator`,
 MAX(`date`) AS `date`
FROM
`act`
WHERE
 `act`.`date` > "1517427936" AND `act`.`date` < "1517428356"
 GROUP BY
`creator`
 ) AS `act_b`
ON
 `act`.`creator` = `act_b`.`creator` AND `act`.`date` = `act_b`.`date`
WHERE
 `act_b`.`creator` IS NOT NULL
 GROUP BY
`act`.`creator`
 ) AS `act_tmp`
ON
 `users`.`id` = `act_tmp`.`creator`
WHERE
 `act_tmp`.`creator` IS NOT NULL
GROUP BY
 `users`.`id`
- casimer.Marquardt80 commented on June 8th 19 at 17:28
June 8th 19 at 17:18
What's the point of sorting, which will then be "perivitelline" group?

By the way, many dialects of SQL will not allow you to use the output fields that do not participate in the grouping without aggregation.
I mean
select
user_id,
act_date
from table
group by user_id

not a ride, but will need to aggregate act_date
select
user_id,
min(act_date) -- or max|sum|avg etc.
from table
group by user_id
max tried gives one row with the maximum date of all but not for everyone - Benedict_Shiel commented on June 8th 19 at 17:21
apparently malformed request
since max() takes the values within the groupings - zoie_Schoen commented on June 8th 19 at 17:24
in that case you need to find first all the actions of all users and then simply access to obtain just the date, but how to turn the group did not realize - zoie_Schoen commented on June 8th 19 at 17:27
even simplifying the query to
select *,max(date) FROM act WHERE date > "1517427936" and date < "1517428356" GROUP BY creator

gives me all the fields + max(date) but the maximum date may be true but the remaining data issues are not sorted - that is, get the first line - casimer.Marquardt80 commented on June 8th 19 at 17:30
,
select * and group by is a GREAT way to spoil yourself in the foot.

example

1 a 01-01-2017
1 b 02-02-2017

what will show in the second column
select * from ... group by column 1

and why? - zoie_Schoen commented on June 8th 19 at 17:33
the first entry poidee - casimer.Marquardt80 commented on June 8th 19 at 17:36
and what is so wrong with select *? - zoie_Schoen commented on June 8th 19 at 17:39
,
the first record poidee
why not the last? Of a sequence: ordering, the physical storage order of the records, the physical storage order of data in the index? -)))
what's wrong with select *?
lack of uniqueness - see above.

It is therefore normal practice to explicitly specify the aggregation of those fields that do not participate in the group. This ensures certainty and predictability of the result of the query. - delta89 commented on June 8th 19 at 17:42
,
select id,date,creator,max(date) as m FROM rooms_bids WHERE date > "1517427936" and date < "1517428356" GROUP BY creator

nothing is given, everything is stored as it is added over time, and increment - zoie_Schoen commented on June 8th 19 at 17:45
you need to aggregate fields are not participating in the group.

Otherwise it will be like pee my pants sometimes on gender, sometimes on foot... - zoie_Schoen commented on June 8th 19 at 17:48
, em and I neizmennaya aggregate id and value for example? (now there is no value of TC while id understand) - delta89 commented on June 8th 19 at 17:51
what's the problem?
or to group or aggregate - zoie_Schoen commented on June 8th 19 at 17:54
and it can be an example at least for this query?
select id,date,creator,max(date) as m FROM rooms_bids WHERE date > "1517427936" and date < "1517428356" GROUP BY creator
- zoie_Schoen commented on June 8th 19 at 17:57
,
select 
first_groupped_field,
second_gropped_field,
sum(other_filed),
min(other_field),
max(other_field)
from table
where ...
group by first_groupped_field, second_gropped_field
- delta89 commented on June 8th 19 at 18:00

Find more questions by tags SQL