MySql - 加入删除列中的duplicity
i am newbie in MySql. I don't know name of function (if its exist) so it's hard to find something. I have 2 tables.
software id | name --------- 1 | xxxx 2 | yyyy
software_version id | software_id | date ---------------------------------- 1 | 2 | 2013-10-9 10:20 2 | 1 | 2013-10-9 10:21 3 | 1 | 2013-10-9 10:22 4 | 2 | 2013-10-9 10:23
Im looking for SQL command, that join this tables, order it by date and show only unique record. For these tables will looks like:
id | name | date --------------------------- 4 | yyyy | 2013-10-9 10:23 3 | xxxx | 2013-10-9 10:22
I found something about DISRINCT but it's unuseable for this i think (i found that it looks for duplicity of whole record). Maybe something with GROUP but I really don't know. How I said in MySql I can do only basic selects, inserts etc.
Even I dont know every type of JOIN so it could be some INNER JOIN or LEFT JOIN. There is so much types of JOIN but i dont see diference.
Thanks for every help!
我是MySql的新手。 我不知道函数的名称（如果它存在），所以很难找到一些东西。 我有2个表。 p>
software id | name --------- 1 | xxxx 2 | yyyy pre>software_version id | software_id | date ---------------------------------- 1 | 2 | 2013-10-9 10:20 2 | 1 | 2013-10-9 10:21 3 | 1 | 2013-10-9 10:22 4 | 2 | 2013-10-9 10:23 pre>我正在寻找SQL命令，它连接这些表，按日期排序并仅显示唯一记录。 对于这些表格将如下所示： p>
id | 名字| date --------------------------- 4 | yyyy | 2013-10-9 10:23 3 | xxxx | 2013-10-9 10:22 pre>我发现了一些关于DISRINCT的内容，但我认为这是不可用的（我发现它寻找完整记录的两面性）。 可能与GROUP有关但我真的不知道。 我在MySql中怎么说我只能做基本选择，插入等。 p>
即使我不知道每种类型的JOIN，所以它可能是一些INNER JOIN或LEFT JOIN。 有很多类型的JOIN，但我没有看到差异。 p>
感谢您的帮助！ p> div>
You can use joins and for the most recent row for each software you can use selef join on the maxima of second table
select v.id,s.name,v.date from software s
join software_version v on (s.id=v.software_id)
join (select software_id ,max(date) date
from software_version group by software_id) v1
on(v.software_id =v1.software_id and v.date=v1.date)
order by v.date desc