Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
727 views
in Technique[技术] by (71.8m points)

group by - Can MySQL use the row as the X in X -> Y (functional dependency)?

Consider the table:

row_id: unique, not null
user_id: id of the user
thing_id: id of something, (user_id, thing_id) is not unique, and (user_id_1, thing_id_1), (user_id_2, thing_id_1) can both present in the table
time_created: creation time of this row, not unique
col1, col2, col3: some other columns

There are no other constraint on the table other than the one on column row_id.

My objective is to list all the distinct thing_ids of a given user, ordered by time_created, with all columns returned, does the following query does what I want?

select row_id,
       user_id, thing_id,
       min(time_created) as time_created,
       col1, col2, col3
from the_table
where user_id = $some_user_id
group by thing_id
order by min(time_created) desc,
         min(id) desc
limit 10

To be more specific: the columns user_id, thing_id, min(time_created) in the select are deterministic, the question is, are columns row_id, col1-3 deterministic? The reason I'm expecting this is that, the select min(time_created) as time_created will choose some row in the group, will mysql derive the value of the other columns using this row? If not, is there other means to achieve the goal?

question from:https://stackoverflow.com/questions/65897165/can-mysql-use-the-row-as-the-x-in-x-y-functional-dependency

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
Waitting for answers

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...