CODEWITHSUNDEEP
mysql
Himal Acharya
Himal Acharya

Reputation: 1016

How to group together same user id but with order by time?

imei        date
10013700    6:00:00 AM
10015700    6:00:00 AM
10014200    6:00:01 AM
10014500    6:00:04 AM
10016300    6:00:04 AM
16014600    6:00:04 AM
10013700    6:00:05 AM
10015700    6:00:05 AM
10013000    6:00:06 AM
10014700    6:00:06 AM
10016300    6:00:06 AM
10016300    6:00:06 AM

This is table that consists of imei and time.. now i need to make table by date and group by imei...

imei        date
10013700    6:00:00 AM
10013700    6:00:05 AM
10015700    6:00:00 AM
10015700    6:00:05 AM
10014200    6:00:01 AM
10014500    6:00:04 AM
10016300    6:00:04 AM
10016300    6:00:06 AM
10016300    6:00:07 AM
16014600    6:00:04 AM
10013000    6:00:06 AM
10014700    6:00:06 AM

i have started from initial time and grouped by imei together with time and start with other imei number next to start time of previous imei in my data set along with imei,date there are other attributes.. 

con = sqlite3.connect("E://code/differenttaxi.sqlite")
df=pd.read_sql_query('SELECT * FROM taxitable WHERE date>="2013-12-16      06:00:00" AND date<"2013-12-17 00:00:00" JOIN (SELECT id,imei, i,lat,lon,speed,direction,error,engine,meter,min(date) first from taxitable group by 10013700)on imei=i ORDER BY first',con)


df.to_csv('E:\\code\\Query_Result.csv',index=False)
con.close()
print("completed!\n")
display(pd.read_csv('E:\\code\\Query_Result.csv'))

Error generated:

DatabaseError: Execution failed on sql near "JOIN": syntax error

Upvotes: 0

Views: 81

Answers (2)

Bohemian
Bohemian

Reputation: 425188

You need to find the first date for each user and order by that:

select t.user, t.date
from mytable t
join (select user u, min(date) first from mytable group by 1) x
  on user = u
order by first, user, date

Upvotes: 1

abrcek
abrcek

Reputation: 93

SELECT tbl.user, tbl.date FROM table_name tbl
LEFT JOIN (
  SELECT user, date FROM table_name GROUP BY user
) ord on tbl.user = ord.user
order by ord.date, tbl.date

Upvotes: 0

Related Questions