Ranking and finding the difference in dates in a table in SQL Problem Description: I want to find the number of days between two different dates in a column which I have ranked based on the most recent date. I was able to write the code that gave me the correct difference in dates between … Read more
Sql Query + fetch first 2 records of every month Problem Description: I have a large set of data with the date as the primary key and there will be records for most of the days. Is it possible to write an SQL query to fetch records for the first 2 days of every month? … Read more
How to select last created record in a group by clause in mysql? Problem Description: I am using mysql 8.0.23 I have three tables, chats, chat_users and chat_messages I want to select the chat_id, the last message (with maximum createdAt date for a particular group. Said in other words, the message order by created_at desc … Read more
Mysql count query results in empty set Problem Description: I am executing the following query and getting an empty result set back. What could be the problem? SELECT COUNT(*) FROM STATION_METRICS WHERE metric_label = ‘Pressure’ AND station_id = 1 AND metric_timestamp >= ‘2022-11-29’ AND metric_timestamp <= ‘2022-12-06’ ORDER BY metric_timestamp ASC LIMIT 18446744073709551615 OFFSET 50; … Read more
Calculate time between time in python and time in mysql database Problem Description: ` import datetime now = datetime.datetime.now () tgl = now.strftime("%Y-%m-%d") y = now.strftime (‘%H:%M:%S’) print(y) >>> 20:01:10 crsr.execute("SELECT * FROM tblsolar WHERE tanggal LIKE ‘%s’ AND id LIKE ‘1001’ ;" % (tgl)) res = crsr.fetchall() for i in res: z = i[9] … Read more
Count records from two tables with foreign keys Problem Description: Suppose I have these MySql tables: TABLE_PEOPLE id name 1 John 2 Albert 3 Joanna 4 Mike 5 Norton TABLE_COLOR id people_id colors 1 1 Green 2 1 Red 3 3 Yellow 4 3 Blue 5 2 Green 6 4 Red 7 5 Grey 8 … Read more
How can I create a REGEXP constraint on a column? Problem Description: I am trying to put a regexp constraint on a field "StaffAdress" such that it should only end with " street". This is my attempt, but it still doesn’t seem to be valid. I tried regexp ‘.*street$’ and regexp ‘street$’ but both did … Read more
How can I create a REGEXP constraint on a column? Problem Description: I am trying to put a regexp constraint on a field "StaffAdress" such that it should only end with " street". This is my attempt, but it still doesn’t seem to be valid. I tried regexp ‘.*street$’ and regexp ‘street$’ but both did … Read more
Retrieve data from MySQL in Java (After end of result set error) Problem Description: Can anyone tell me what is wrong with my code here, and what is the best approach to do the same thing? I’m trying to connect to a database and retrieve a list of unprocessed orders, recreate the order object which … Read more
Retrieve data from MySQL in Java (After end of result set error) Problem Description: Can anyone tell me what is wrong with my code here, and what is the best approach to do the same thing? I’m trying to connect to a database and retrieve a list of unprocessed orders, recreate the order object which … Read more