I have two tables sgroup and sitem. both sgroup and sitem have a created and modified property (which are of type DATETIME and can be NULL).
There can be many sitem entries for each sgroup. I'd like to create a query that will return to me the properties of the group as well as the MAX of all of the sitem(s) or sgroup datetimes.
It's always possible modified is null (though this can be changed). It's possible there are 0 sitems. +1 if I can sort the results by date.
Here's an example.
sgroup: || id || a || b || c || created || modified ||
|| 1 || xyz || def || qwe || 2024-01-03 00:31:55 || NULL ||
|| 2 || wer || ert || rty || 2024-01-04 00:12:12 || 2024-01-07 00:32:51 ||
sitem: || id || gid || d || e || created || modified ||
|| 1 || 1 || hio || kso || 2024-01-06 00:31:55 || NULL ||
|| 1 || 2 || kop || noa || 2024-01-07 00:12:55 || NULL||gid is a foreign key from sitem to sgroup. id is the primary key for both tables. As you notice Group 1 has 2 items, and Group 2 has no items.
I'd like to perform a query and return something like the following:
|| id || a || b || c || latest ||
|| 2 || wer || ert || rty || 2024-01-07 00:32:51 ||
|| 1 || xyz || def || qwe || 2024-01-07 00:12:55 ||
As you can see the second group's latest datetime which is its modified property. In the first group the latest date comes from the creation of sitem 2. I'd like to receive only these 2 entries describing the groups.
Join tables and sort by max date between the two tables and it's entries (there are two dates each)
41 Views Asked by Sharp Dev At
0
There are 0 best solutions below
Related Questions in MYSQL
- How to Retrieve Data from an MySQL Database and Display it in a GUI?
- How to change woocomerce or full wordpress currency with value from USD to AUD
- window.location.href redirects but is causing problems on the webpage
- Error: local variable 'bramka' referenced before assignment
- Products aren't displayed after fetching data from mysql db (node.js & express)
- status table for all entries (even in different dates) in database changing value when all checkboxes are checked
- Can't Fix Mariadb & Mysql ERROR 2002 (HY000): Can't connect to local server through socket '/tmp/mysql.sock' (2) On MacOs
- Express Mysql getting max ID from table not working cought in a promise
- failed to upload a table from sql file
- Update a MySQL row depending on the ID in Google Sheets Apps Script
- Use row values from another table to select them as columns and establish relations between them (pivot table)
- SQL: Generate combination table based on source and destination column from same table
- How to display the column names which have only unique non-null values in MySQL table?
- mysql query takes too long because of wrong indexes usage
- Multitable joining in Sql
Related Questions in DATETIME
- I wrote this time displaying FLUTTER app, How can I improve it?
- How to convert pandas series to integer for use in datetime.fromisocalendar
- JSON file of 7000 meetings in multiple timezones and Flatlist
- Intl.DateTimeFormat() - return weekday as number?
- I'd like to create a custom time zone converter, any pointers?
- How set weekday datetime?
- ValueError: setting an array element with a sequence. Trying to make a Skymap in Python
- Different X axes with Plotly's make_subplots
- Error when converting datetime from UTC to Brasilia datetime in Power BI
- Problem converting time series df from chr to date using as.POSIXct
- How to supress naive datetime warning in django
- How to find rows that fall within time range from a dataframe?
- Timedelta error - AttributeError: type object 'datetime.datetime' has no attribute 'timedelta'
- How to convert "Tue Feb 27 2024 16:35:30 GMT+0800" String to ZonedDateTime type
- react apex charts x axis labels and tool tip showing different times instead of actual PST time
Related Questions in JOIN
- Hibernate: JOIN inheritance question - why the need for two left joins
- PHP fetchAll on JOIN
- Polars asof join on next available date
- Merge effective dated records of an attribute with the main effective dated table (SQL)
- Repeat Value for Every Instance of Another Value in Excel without using Power Query
- Is it correct to add "UNNEST" in the "ON" condition of a (left) join?
- Is there a way in Laravel to use multiple connections simultaneously in a combined manner?
- How can I join data to my table that isn't available for everyone without losing results?
- Using max/min on columns with null values
- Qlik IntervalMatch to SQL
- Join two tables by columnname when columnames for joining stored in a table
- Combining two dataframes with different column name in time-series
- How to deal with complex oracle sql query in spring boot?
- Join data frames with multiple conditions
- PySpark: NULL values in Join 2nd dataframe should match
Related Questions in GROUP-BY
- I typed all the codes well, but only the group_by function doesn't work
- How can I change the groupby scope to find the first value that meets the conditions of a mask?
- How can i fix this error ? Attempt to get argmax of an empty sequence
- How can I change the groupby column to find the first row that meets the conditions of a mask if the initial groupby failed to find it?
- Fans By AgeGroup in Percentage SQLITE
- How can I create groups based on ascending streak of a column?
- GROUP BY with multiple nested queries T-SQL
- Problems encountered when using _shard_num when querying clickhouse shard sets
- How to replace cur_data() with a non-deprecated function?
- How to use a conditional to evaluate a paired variable in case statement
- How would I groupby and rank in Pandas while excluding one value of the group?
- Counting nested categories in pandas/python
- Group By clause returning too many rows
- Django: how to annotate average of average?
- How to get the unique combined groups in R?
Related Questions in SQL-ORDER-BY
- Order By Clause Overridden in Laravel Union Query Construction
- SQL Average of Last 100 records
- Pagination order by date ignores some data having same date
- MySQL is not using the primary key that's implicitly appended to this secondary index
- does the column we use in group by effect the sorting order of the table?
- MySQL: wrong sorting order in a query with ORDER BY + GROUP BY + GROUP_CONCAT() + COUNT(*) OVER() window function
- find groups by event timestamps that are out of order
- Understanding behaviour of order by in a query
- How to implement custom ordering with cursor pagination in Django Rest Framework
- Join tables and sort by max date between the two tables and it's entries (there are two dates each)
- Show value in a special position with MySQL
- Extension method not changing the list when sorting it
- select two balance team of two people
- Use order by with two columns
- Implement LESS THAN a certain id with ORDER BY
Trending Questions
- UIImageView Frame Doesn't Reflect Constraints
- Is it possible to use adb commands to click on a view by finding its ID?
- How to create a new web character symbol recognizable by html/javascript?
- Why isn't my CSS3 animation smooth in Google Chrome (but very smooth on other browsers)?
- Heap Gives Page Fault
- Connect ffmpeg to Visual Studio 2008
- Both Object- and ValueAnimator jumps when Duration is set above API LvL 24
- How to avoid default initialization of objects in std::vector?
- second argument of the command line arguments in a format other than char** argv or char* argv[]
- How to improve efficiency of algorithm which generates next lexicographic permutation?
- Navigating to the another actvity app getting crash in android
- How to read the particular message format in android and store in sqlite database?
- Resetting inventory status after order is cancelled
- Efficiently compute powers of X in SSE/AVX
- Insert into an external database using ajax and php : POST 500 (Internal Server Error)
Popular Questions
- How do I undo the most recent local commits in Git?
- How can I remove a specific item from an array in JavaScript?
- How do I delete a Git branch locally and remotely?
- Find all files containing a specific text (string) on Linux?
- How do I revert a Git repository to a previous commit?
- How do I create an HTML button that acts like a link?
- How do I check out a remote Git branch?
- How do I force "git pull" to overwrite local files?
- How do I list all files of a directory?
- How to check whether a string contains a substring in JavaScript?
- How do I redirect to another webpage?
- How can I iterate over rows in a Pandas DataFrame?
- How do I convert a String to an int in Java?
- Does Python have a string 'contains' substring method?
- How do I check if a string contains a specific word?