with CustomerRank as ( select a.province ,c.name ,dense_rank() over(partition by a.province order by c.birth_date desc) as AgeRank from customer c join address a on c.cust_id = a.cust_id ) select * from CustomerRank a where a.AgeRank = 1; Example fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=016888fbaf40093edfdf7ad22412c215 The idea is to create a list on which you can then pick the answer you need. In this case, generating a ranking of birth_date per province (AgeRank) then picking all the rows where AgeRank is 1 (i.e. the youngest as we've order by birth_date desc).

Assuming you want one row per day and group name: WITH incomplete_table(date, group_name, value) AS ( VALUES ('2022-01-01'::date, 'a', 1) , ('2022-01-15'::date, 'a', 0.5) , ('2022-01-31'::date, 'a', 0.2) , ('2022-01-02'::date, 'b', 0.1) , ('2022-01-10'::date, 'b', 0.15) , ('2022-01-20'::date, 'b', 0.15) ) SELECT d.date, g.group_name, i.value FROM ( SELECT date::date FROM generate_series (timestamp '2022-01-01' , timestamp '2022-01-31' , interval '1 day') date ) d CROSS JOIN (SELECT DISTINCT group_name FROM incomplete_table) g -- ① LEFT JOIN LATERAL ( SELECT i.group_name, i.value FROM incomplete_table i WHERE i.group_name = g.group_name AND i.date = timestamp '2022-01-01' -- ? ② ORDER BY i.date DESC LIMIT 1 ) i ON true ORDER BY g.group_name, d.date DESC; db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=6a683b65cd480b1c9c934626ed098b77 ① If available, use a table "groups" providing distinct group names. Faster. Else, if the table is big, consider an emulated index-skip scan. See: https://stackoverflow.com/a/66894500/939860 ② The search for the latest value is not limited to the given time range unless you spell that out in the LATERAL subquery. About LATERAL: https://stackoverflow.com/a/28557803/939860 You still get value IS NULL where no earlier value is found. If incomplete_table is big, an index on (group_name, date) will help performance (a lot). Possibly even a "covering" index, adding column value. See: https://dba.stackexchange.com/questions/81456/slow-index-scans-in-large-table/81554#81554 Very similar case with more explanation: https://dba.stackexchange.com/questions/266009/return-previous-running-total-when-value-is-null-in-a-time-series/266013#266013

Add this index: INDEX(obj_type, obj_id, id) This looks like a http://mysql.rjweb.org/doc.php/groupwise_max query; it may have a better formulation. If you need to discuss further, please provide SHOW CREATE TABLE post.

MIN(enrolled) is kludgy. It works only because of string choices and comparison. BUG! MIN(Date), MIN(enrolled) cannot be trusted to get data from the same row. You have a "groupwise-max" problem; see the tag. You need INDEXes; FKs are optional. ID on table_3 is unnecessary; the PK can be PRIMARY KEY(student_id, date) Do use DATETIME, not VARCHAR. Table_2 does not need ID; PRIMARY KEY(student_id) is really what you need. Since you seem to be building students (table_2) and income_history (table_3) from table_1, these two steps might work: INSERT INTO students (...) SELECT ... FROM table_1 WHERE enrolled = 'enrolled'; INSERT INTO income_history (...) SELECT ... FROM table_1; (If there are more options for enrolled or a student can be "enrolled" twice or ..., then the above won't work.)

What code do you have so far? I assume you are using GROUP BY case_id, client_id. To discover whether any departure is NULL (across a group) is with COUNT(*) = COUNT(departure). This is because COUNT(*) counts all rows; the other counts only rows where departure IS NOT NULL. So, this is how you might make the departure expression: IF(COUNT(departure) = COUNT(*), MAX(departure), NULL) More Your proposed solution has the "only_full_group_by" problem since it asks for multiple things. Change ID to GROUP_CONCAT(ID). Also, it would be a slight optimization to change the ORDER BY to match the GROUP BY.

This can easily be done using PostgreSQL's (proprietary) distinct on () operator: select distinct on (id) * from the_table order by id, value_1 desc nulls last;

min( tc2.added_on ) and min( tc2.id ) will not necessary align with each other. Sounds like you need a "groupwise-max". INDEX(User_id, added_on) INDEX(status_enter, added_on) Using OFFSET for pagination is problematic, especially with poor indexes.

I infer that your data looks like this: Person Table ╔══════════╦═══════╦════════╗ ║ PersonID ║ Name ║ Gender ║ ╠══════════╬═══════╬════════╣ ║ 1 ║ John ║ M ║ ║ 2 ║ Vicky ║ F ║ ║ 3 ║ Bob ║ M ║ ╚══════════╩═══════╩════════╝ Job Table ╔══════════╦═════════════╦════════════╗ ║ PersonID ║ JobName ║ HireDate ║ ╠══════════╬═════════════╬════════════╣ ║ 1 ║ Electrician ║ 2010-01-01 ║ ║ 1 ║ Mechanic ║ 2012-05-08 ║ ║ 2 ║ Scientific ║ 2012-11-11 ║ ╚══════════╩═════════════╩════════════╝ The first task is to find the first job (by hire date) for each person. One neat way to do that is by using a correlated subquery: SELECT j.* FROM dbo.Job AS j WHERE j.HireDate = ( SELECT MIN(j2.HireDate) FROM dbo.Job AS j2 WHERE j2.PersonID = j.PersonID ); Notice the correlation WHERE j2.PersonID = j.PersonID between the inner and outer queries there. The output of that query is: ╔══════════╦═════════════╦════════════╗ ║ PersonID ║ JobName ║ HireDate ║ ╠══════════╬═════════════╬════════════╣ ║ 1 ║ Electrician ║ 2010-01-01 ║ ║ 2 ║ Scientific ║ 2012-11-11 ║ ╚══════════╩═════════════╩════════════╝ The execution plan (given a clustered PRIMARY KEY on PersonID, HireDate) is: The interesting thing about that plan is the Job table is only scanned once, despite there being two references to it in the original query. The plan uses an optimization that I call Segment Top. Essentially the execution engine takes advantage of the index order to detect the start of a new group (segment) and take just the first row from each group (top). Now that we have that result, all we need do is join it back to the Person table: SELECT p.PersonName, p.Gender, j.JobName FROM dbo.Person AS p LEFT JOIN ( -- Previous query SELECT j.* FROM dbo.Job AS j WHERE j.HireDate = ( SELECT MIN(j2.HireDate) FROM dbo.Job AS j2 WHERE j2.PersonID = j.PersonID ) ) AS j ON j.PersonID = p.PersonID OPTION (MERGE JOIN); The execution plan is: The OPTION (MERGE JOIN) is not required; I just added it to show the plan you are likely to get when the tables contain a larger number of rows than in this small example. Table definitions and sample data: CREATE TABLE dbo.Person ( PersonID integer NOT NULL, PersonName varchar(30) NOT NULL, Gender char(1) NOT NULL, PRIMARY KEY (PersonID) ); CREATE TABLE dbo.Job ( PersonID integer NOT NULL, JobName varchar(30) NOT NULL, HireDate datetime NOT NULL, PRIMARY KEY (PersonID, HireDate) ); INSERT dbo.Person (PersonID, PersonName, Gender) SELECT 1, 'John', 'M' UNION ALL SELECT 2, 'Vicky', 'F' UNION ALL SELECT 3, 'Bob', 'M'; INSERT dbo.Job (PersonID, JobName, HireDate) SELECT 1, 'Mechanic', '20120508' UNION ALL SELECT 1, 'Electrician', '20100101' UNION ALL SELECT 2, 'Scientific', '20121111';

That's what GROUP BY is used for. Get one row (per group). In this case, it will show all distinct user_id values and for the rest of the columns, you can (have to) use aggregate functions like MIN(), MAX(), AVG(), SUM() as you will have more than one values per group and only one can be shown. SELECT user_id , MIN(comment) AS comment -- it will show the first in alphabetical order -- you could also use MAX() FROM tableX GROUP BY user_id ; MySQL allows also the following unorthodox solution, that will return one (more or less random) comment per user: SELECT user_id , comment FROM tableX GROUP BY user_id ; This last query will not work but raise an error if the (stricter) ONLY_FULL_GROUP_BY mode is enabled. In the recently released 5.7 version, this mode is the default and a new function, ANY_VALUE(), is provided. For more details, see the MySQL Handling of GROUP BY page. The query can be written now: SELECT user_id , ANY_VALUE(comment) AS comment FROM tableX GROUP BY user_id ; Note that with either the "unorthodox" version or using the recent ANY_VALUE() function, if we add more columns in the SELECT list, their values is not guaranteed to be from the same row, just from a row in the same group. The way they are selected is not exactly random, depends on the execution plan and the indexes used.

EXPLAIN ANALYZE suggested the cost of the whole query with lateral joins would be about a quarter of the original. EXPLAIN (estimating the cost) suggests 40,089.36 vs. 189,883.92 unicorn points. But EXPLAIN ANALYZE (measuring actual execution times) disagrees and shows 2,502.031 ms vs. 1,835.193 ms, so around 1/3 slower. There can be many reasons why the estimation is off target. Most prominently cost settings and statistics. See: Keep PostgreSQL from sometimes choosing a bad query plan That said, the query can probably be much faster (by orders of magnitude). We only got limited information, but I suppose you want something like this: SELECT a.* , rs.created_at AS latestsnapshottime -- maybe use COALESCE? , rp.created_at AS latestresponsetime FROM appointments a LEFT JOIN LATERAL ( SELECT rs.created_at FROM reportsnapshots rs WHERE rs.appointmentid = a.id ORDER BY rs.created_at DESC -- NULLS LAST ? LIMIT 1 ) rs ON true LEFT JOIN LATERAL ( SELECT rp.created_at FROM responses rp WHERE rp.appointmentid = a.id ORDER BY rp.created_at DESC -- NULLS LAST ? LIMIT 1 ) rp ON true WHERE a.organizationid = 16 AND a.locationid = '51' AND a.cancelled = FALSE AND a.filteredin = TRUE AND a.start BETWEEN '2021-05-04T00:00:00-06:00' AND '2021-05-04T23:59:59-06:00' AND a.locationid IN (61,60,140,53,138,130,133,131,55,51,100) -- GROUP BY a.id, rs.created_at, rp.created_at -- not needed, I guess ORDER BY a.start, a.id LIMIT 100; DISTINCT ON is a great tool, but for different situations. See: Select first row in each GROUP BY group? Retrieving a single row from each LATERAL subquery like I suggest can use an index and is super fast. Ideally, you have theses indexes: reportsnapshots (appointmentid, created_at DESC NULLS LAST) responses (appointmentid, created_at DESC NULLS LAST) Further reading: Optimize GROUP BY query to retrieve latest row per user If created_at is defined NOT NULL, a simpler index on (appointmentid, created_at) is just as good (and preferable). See: Sort by column ASC, but NULL values first? Why does ORDER BY NULLS LAST affect the query plan on a primary key? Plus an index on the outer table. The one in use now (appointments_organizationId_status_start_idx) doesn't seem too bad. But I suspect more potential, depending on undisclosed information. You probably don't have to aggregate in the outer query, since both subqueries return a single row each (even your original). Alternatively, use plain correlated subqueries with max() for your simple case. Probably even faster, yet: SELECT a.* , (SELECT max(rs.created_at) FROM reportsnapshots rs WHERE rs.appointmentid = a.id) AS latestsnapshottime , (SELECT max(rp.created_at) FROM responses rp WHERE rp.appointmentid = a.id) AS latestresponsetime FROM appointments a WHERE ... LIMIT 100; See: What is the difference between LATERAL JOIN and a subquery in PostgreSQL? Aside 1: BETWEEN is typically no good for timestamps. See: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_BETWEEN_.28especially_with_timestamps.29 How to add a day/night indicator to a timestamp column? Aside 2: In your first query plan I see Sort Method: external merge Disk: 3,856kB and Sort Method: external merge Disk: 17,752kB, which indicates a lack of work_mem. The same problem does not surface in the 2nd plan, nor will it for my queries. But look into your server configuration. Related: Optimize simple query using ORDER BY date and text

See if its what you want -- this main query retrieve all users under the subquery domain SELECT a.UserID , substring_index(a.UserID, '@', -1) , MAX(a.lastupdate) FROM MyTable a WHERE substring_index(a.UserID, '@', -1) IN ( -- this subquery search all domain inactive in last 12 months SELECT substring_index(a.UserID, '@', -1) AS domain FROM MyTable a GROUP BY substring_index(a.UserID, '@', -1) HAVING MAX(a.lastupdate) < '2020-03-31' ) ORDER BY substring_index(a.UserID, '@', -1), a.lastupdate

As a classic Greatest N per group problem, this is easily solvable using the ROW_NUMBER() function: SELECT pizza_name, beverage FROM ( select pizza_name, beverage, ROW_NUMBER() OVER ( PARTITION BY pizza_name ORDER BY COUNT(beverage) DESC ) as pizza_beverage_ranking from pizza_production JOIN pizza_restrictions pr on pizza_production.topping_name = pr.topping_name JOIN beverage_pairing_notes on restriction = pairing GROUP BY pizza_name, beverage ) AS derived WHERE pizza_beverage_ranking = 1 ; As specified in the ORDER BY clause of the ROW_NUMBER() function, the rankings are assigned in the descending order of COUNT(beverage). Beyond that, however, the order is arbitrary, and when two or more rows have the same weight, which one gets lower or higher ranking is going to be up to the server. You can add more criteria after COUNT(beverage) DESC if you want the order to be more specific and, consequently, the results more predictable. Apart from sorting, there's also the PARTITION BY clause, which additionally specifies that the enumeration be done separately for every group of rows sharing the same pizza_name value. Once the row numbers are assigned, you can filter on them, as you can see done in the outer SELECT's WHERE clause. As a side note, please consider always to qualify columns with table names or aliases (like pizza_production.pizza_name instead of pizza_name, for example) where the query involves more than one table. While the server can certainly resolve unqualified columns if there are no conflicts, it is easier for a human to read and understand a query if the columns are unambiguously qualified, particularly when the reader is not very familiar with the schema. This is as true for queries that you offer for study to an unknown audience, as it is for queries you have once written yourself and forgotten but have to go back to for some reason or other.

Assuming that there is no self-messages: SELECT t1.* FROM messages t1 JOIN ( SELECT LEAST(from_user_id, to_user_id) user1, GREATEST(from_user_id, to_user_id) user2, MAX(created_at) created_at FROM messages t2 GROUP BY user1, user2 ) t3 ON t1.from_user_id IN (t3.user1, t3.user2) AND tm1.to_user_id IN (t3.user1, t3.user2) AND t1.created_at = t3.created_at ; Join users table if needed. If id (1) < id (2) => created_at (1) < created_at (2) then you may replace all occurences of created_at with id.

You can use a window function like ROW_NUMBER() to generate a unique ID for each record within a grouping (PARTITION) of each value of category like so: SELECT SalesDateYear, model, Sold, category FROM ( select YEAR(Sales.saleDate) SalesDateYear , Vehicles.model , count(Vehicles.model) 'Sold' , Vehicles.category , ROW_NUMBER() OVER (PARTITION BY Vehicles.category ORDER BY COUNT(Vehicles.model) DESC) SortId from Vehicles JOIN vehicleSold on Vehicles.vin = vehicleSold.vin JOIN Sales on Sales.saleID = vehicleSold.saleID group by YEAR(Sales.saleDate) , Vehicles.model , Vehicles.category ) Results WHERE SortId = 1 (I normally like to use a CTE instead of subquery for these kinds of things, but I don't think your version of MySQL has CTEs, so the above uses a subquery.)

On MySQL 8.0 one solution is using ROW_NUMBER function in this way: Notice I've used Grp instead of Group, because it's a reserved word. SELECT Person, Grp, Age FROM (SELECT Person, Grp, Age, ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY Age DESC) Rn FROM Ppl) a WHERE Rn = 1 ORDER BY Age DESC; Person | Grp | Age :----- | --: | --: Lalo | 3 | 50 Shawn | 1 | 42 Laura | 2 | 39 There is no way to get Julia instead of Lalo, unless you add another column to the ORDER BY clause. db<>fiddle here

Your query does not exactly do a count of cities, but rather the count of users per listed city. To get that after de-duplicating users: SELECT city, count(*) AS users FROM ( SELECT DISTINCT ON (user_id) city FROM cities ) sub GROUP BY city; This picks one row per user_id arbitrarily like you specified. So we need no ORDER BY in the inner SELECT. We need nothing but the city from the inner query for the bare count. Detailed explanation for DISTINCT ON: Select first row in each GROUP BY group? Not deterministic for arbitrary pick The above is typically fastest for few rows per user_id, while implementing stated requirements. But the result is not deterministic while we pick rows arbitrarily. Can return different numbers for repeated executions as Postgres is free to pick any row for one user. (The total sum over all cities is stable, though, being the count of users.) The result is normally stable, but any change to the table can trigger a different result. Like autovacuum doing its job in the background, or any unrelated write operation on the table. To get deterministic results you need to add a deterministic ORDER BY to the inner query, so that DISTINCT ON always picks the same row. Like: SELECT city, count(*) AS users FROM ( SELECT DISTINCT ON (user_id) city FROM cities ORDER BY user_id, city -- making the pick determinisitic ) sub GROUP BY 1; Which is equivalent to: SELECT city, count(*) AS users FROM ( SELECT min(city) FROM cities GROUP BY user_id ) sub GROUP BY 1;

Use a window function: SELECT ROW_NUMBER() OVER (PARTITION BY repid ORDER BY date DESC) = 1 AS greatest, ... This requires MySQL 8.0.02 or MariaDB 10.2.0 to use window functions.

If I understand correctly your objective is this: After ordering by position_id, get the row with the highest position_id for each of 100 percentiles. DISTINCT ON does it: SELECT DISTINCT ON (ntile) NTILE(100) OVER (ORDER BY position_id) AS ntile, * FROM position WHERE activity_id = 1 ORDER BY ntile, position_id DESC; db<>fiddle here Since the PK is on (position_id, activity_id), position_id is unique for each activity_id and we need no tiebreaker. Can be a single SELECT without subquery because DISTINCT ON is applied after window functions. See: Select first row in each GROUP BY group? Best way to get result count before LIMIT was applied

This can be cast as greatest-n-per-group problem in disguise. So DISTINCT ON is my core feature after unnesting and before aggregating back: To produce one row like in the original table: WITH cte AS ( SELECT *, CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 WHEN 'Low' THEN 3 END AS prio FROM tbl ) SELECT array_agg(id ORDER BY prio) FILTER (WHERE state = 'p') AS productive -- ORDER BY prio? , array_agg(id ORDER BY prio) FILTER (WHERE state = 'u') AS unproductive , array_agg(id ORDER BY prio) FILTER (WHERE state = 'n') AS neutral FROM ( SELECT DISTINCT ON (id) * FROM ( SELECT unnest(neutral) AS id, prio, 'n' AS state FROM cte UNION ALL SELECT unnest(productive) , prio, 'p' FROM cte UNION ALL SELECT unnest(unproductive) , prio, 'u' FROM cte ) sub1 ORDER BY id, prio, state ) sub2; Or with pivoted result: WITH cte AS ( SELECT *, CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 WHEN 'Low' THEN 3 END AS prio FROM tbl ) SELECT state, array_agg(id ORDER BY prio) -- ORDER BY prio? FROM ( SELECT DISTINCT ON (id) * FROM ( SELECT unnest(neutral) AS id, prio, 'neutral' AS state FROM cte UNION ALL SELECT unnest(productive) , prio, 'productive' FROM cte UNION ALL SELECT unnest(unproductive) , prio, 'unproductive' FROM cte ) sub1 ORDER BY id, prio, state ) sub2 GROUP BY 1; db<>fiddle here Further reading: Select first row in each GROUP BY group? You did not mention performance in this question. Consider the answer to your previous question for this: Remap array columns to single column with value

Simply adding DISTINCT ON (PersonID) will do the job in PostgreSQL: SELECT DISTINCT ON (PersonID) AgentID, PersonID, Count(*) AS BustCount FROM InvolvedIn i WHERE AgentID IS NOT NULL AND isCulprit GROUP BY AgentID, PersonID HAVING COUNT(*) > 1 ORDER BY PersonID, BustCount DESC, AgentID; I added AgentID as (optional) third ORDER BY expression and tiebreaker to get a deterministic result in case of multiple agents with the same amount of busts. Adapt to your requirements. Detailed explanation: Select first row in each GROUP BY group? We can do all of it in a single SELECT without subquery, because DISTINCT ON is applied after aggregation. About the sequence of events in an SELECT query: Best way to get result count before LIMIT was applied Oh, and WHERE isCulprit = TRUE is just a more noisy way of saying WHERE isCulprit.