mysql에서 이름으로 그룹화하기 전에 날짜 및 시간으로 정렬

mysql에서 이름으로 그룹화하기 전에 날짜 및 시간으로 정렬

---

나는 다음과 같은 테이블이 있습니다.

name    date         time
tom | 2011-07-04 | 01:09:52
tom | 2011-07-04 | 01:09:52
mad | 2011-07-04 | 02:10:53
mad | 2009-06-03 | 00:01:01

나는 가장 오래된 이름을 먼저 원합니다.

SELECT * 
ORDER BY date ASC, time ASC 
GROUP BY name

(-> 작동하지 않습니다!)

이제 그것은 나에게 먼저 화가 나고 (이전 데이트가 있음) Tom

하지만 GROUP BY name ORDER BY date ASC, time ASC분류하기 전에 그룹화되기 때문에 새로운 화가 먼저 나에게 제공됩니다!

다시 : 문제는 GROUP BY가 ORDER BY 전에 있어야하기 때문에 그룹화하기 전에 날짜와 시간별로 정렬 할 수 없다는 것입니다!

---

다른 방법 :

SELECT * 
FROM (
    SELECT * 
    ORDER BY date ASC, time ASC 
) AS sub
GROUP BY name

일치하는 첫 번째 결과에 대한 GROUP BY 그룹. 첫 번째 일치 히트가 원하는 히트 일 경우 모든 것이 예상대로 작동합니다.

이 방법은 하위 쿼리가 다른 조건을 사용하는 것보다 논리적으로 이해하기 때문에 선호합니다.

---

나는 이것이 당신이 찾고있는 것이라고 생각합니다.

SELECT name, min(date)
FROM myTable
GROUP BY name
ORDER BY min(date)

당분간 STR_TO_DATE를 통해 mysql 날짜를 만들어야합니다.

STR_TO_DATE(date + ' ' + time, '%Y-%m-%d %h:%i:%s')

그래서 :

SELECT name, min(STR_TO_DATE(date + ' ' + time, '%Y-%m-%d %h:%i:%s'))
FROM myTable
GROUP BY name
ORDER BY min(STR_TO_DATE(date + ' ' + time, '%Y-%m-%d %h:%i:%s'))

---

user1908688의 답변에 대해 언급 할 수 없으므로 여기에 MariaDB 사용자를위한 힌트가 있습니다.

SELECT *
FROM (
     SELECT *
     ORDER BY date ASC, time ASC
     LIMIT 18446744073709551615
     ) AS sub
GROUP BY sub.name

https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/

---

이것은 나를 위해 일했습니다.

SELECT *
FROM your_table
WHERE id IN (
    SELECT MAX(id)
    FROM your_table
    GROUP BY name
);

---

하위 선택 사용 :

select name, date, time
from mytable main
where date + time = (select min(date + time) from mytable where name = main.mytable)
order by date + time;

---

나는 단 하나 있었다이 질문에 다른 변화했다 DATETIME필드와 필요 limit후 group by나 distinct에 기초 내림차순 정렬 한 후 datetime필드를하지만이 나를 도왔다 것입니다 :

select distinct (column) from
(select column from database.table
order by date_column DESC) as hist limit 10

이 경우 분할 필드가있는 경우 concat을 기준으로 정렬 할 수 있으면 다음과 같은 작업을 수행 할 수 있습니다.

select name,date,time from
(select name from table order by concat(date,' ',time) ASC)
as sorted

그런 다음 제한하려면 끝에 제한 문을 추가하면됩니다.

select name,date,time from
(select name from table order by concat(date,' ',time) ASC)
as sorted limit 10

---

Another way to solve this would be with a LEFT JOIN, which could be more efficient. I'll first start with an example that considers only the date field, as probably it is more common to store date + time in one datetime column, and I also want to keep the query simple so it's easier to understand.

So, with this particular example, if you want to show the oldest record based on the date column, and assuming that your table name is called people you can use the following query:

SELECT p.* FROM people p
LEFT JOIN people p2 ON p.name = p2.name AND p.date > p2.date
WHERE p2.date is NULL
GROUP BY p.name

What the LEFT JOIN does, is when the p.date column is at its minimum value, there will be no p2.date with a smaller value on the left join and therefore the corresponding p2.date will be NULL. So, by adding WHERE p2.date is NULL, we make sure to show only the records with the oldest date.

And similarly, if you want to show the newest record instead, you can just change the comparison operator in the LEFT JOIN:

SELECT p.* FROM people p
LEFT JOIN people p2 ON p.name = p2.name AND p.date < p2.date
WHERE p2.date is NULL
GROUP BY p.name

Now, for this particular example where date+time are separate columns, you would need to add them in some way if you want to query based on the datetime of two columns combined, for example:

SELECT p.* FROM people p
LEFT JOIN people p2 ON p.name = p2.name AND p.date + INTERVAL TIME_TO_SEC(p.time) SECOND > p2.date + INTERVAL TIME_TO_SEC(p2.time) SECOND
WHERE p2.date is NULL
GROUP BY p.name

You can read more about this (and also see some other ways to accomplish this) on the The Rows Holding the Group-wise Maximum of a Certain Column page.

---

In Oracle, This work for me

SELECT name, min(date), min(time)
    FROM table_name
GROUP BY name

---

This is not the exact answer, but this might be helpful for the people looking to solve some problem with the approach of ordering row before group by in mysql.

I came to this thread, when I wanted to find the latest row(which is order by date desc but get the only one result for a particular column type, which is group by column name).

One other approach to solve such problem is to make use of aggregation.

So, we can let the query run as usual, which sorted asc and introduce new field as max(doc) as latest_doc, which will give the latest date, with grouped by the same column.

Suppose, you want to find the data of a particular column now and max aggregation cannot be done. In general, to finding the data of a particular column, you can make use of GROUP_CONCAT aggregator, with some unique separator which can't be present in that column, like GROUP_CONCAT(string SEPARATOR ' ') as new_column, and while you're accessing it, you can split/explode the new_column field.

Again, this might not sound to everyone. I did it, and liked it as well because I had written few functions and I couldn't run subqueries. I am working on codeigniter framework for php.

Not sure of the complexity as well, may be someone can put some light on that.

Regards :)

ReferenceURL : https://stackoverflow.com/questions/6572110/order-by-date-and-time-before-group-by-name-in-mysql