Order by Maximum Condition Match

Order by Maximum condition match

SELECT *
  FROM (SELECT (CASE WHEN cond1 THEN 1 ELSE 0 END +
                CASE WHEN cond2 THEN 1 ELSE 0 END +
                CASE WHEN cond2 THEN 1 ELSE 0 END +
                ...
                CASE WHEN cond10 THEN 1 ELSE 0 END
               ) AS numMatches,
               other_columns...
          FROM mytable
       ) xxx
 WHERE numMatches > 0
 ORDER BY numMatches DESC

How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?

You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields:

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

Get records with max value for each group of grouped SQL results

There's a super-simple way to do this in mysql:

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

This works because in mysql you're allowed to not aggregate non-group-by columns, in which case mysql just returns the first row. The solution is to first order the data such that for each group the row you want is first, then group by the columns you want the value for.

You avoid complicated subqueries that try to find the max() etc, and also the problems of returning multiple rows when there are more than one with the same maximum value (as the other answers would do)

Note: This is a mysql-only solution. All other databases I know will throw an SQL syntax error with the message "non aggregated columns are not listed in the group by clause" or similar. Because this solution uses undocumented behavior, the more cautious may want to include a test to assert that it remains working should a future version of MySQL change this behavior.

Version 5.7 update:

Since version 5.7, the sql-mode setting includes ONLY_FULL_GROUP_BY by default, so to make this work you must not have this option (edit the option file for the server to remove this setting).

Performance of max() vs ORDER BY DESC + LIMIT 1

There does not seem to be an index on sensor.station_id, which is important here.

There is an actual difference between max() and ORDER BY DESC + LIMIT 1. Many people seem to miss that. NULL values sort first in descending sort order. So ORDER BY timestamp DESC LIMIT 1 returns a NULL value if one exists, while the aggregate function max() ignores NULL values and returns the latest not-null timestamp. ORDER BY timestamp DESC NULLS LAST LIMIT 1 would be equivalent

For your case, since your column d.timestamp is defined NOT NULL (as your update revealed), there is no effective difference. An index with DESC NULLS LAST and the same clause in the ORDER BY for the LIMIT query should still serve you best. I suggest these indexes (my query below builds on the 2nd one):

sensor(station_id, id)
data(sensor_id, timestamp DESC NULLS LAST)

You can drop the other indexes sensor_ind_timestamp and sensor_ind_timestamp_desc unless they are in use otherwise (unlikely, but possible).

Much more importantly, there is another difficulty: The filter on the first table sensors returns few, but still (possibly) multiple rows. Postgres expects to find 2 rows (rows=2) in your added EXPLAIN output.

The perfect technique would be an index-skip-scan (a.k.a. loose index scan) for the second table data - which is not currently implemented (up to at least Postgres 15). There are various workarounds. See:

• Optimize GROUP BY query to retrieve latest row per user

The best should be:

SELECT d.timestamp
FROM   sensors s
CROSS  JOIN LATERAL  (
   SELECT timestamp
   FROM   data
   WHERE  sensor_id = s.id
   ORDER  BY timestamp DESC NULLS LAST
   LIMIT  1
   ) d
WHERE  s.station_id = 4
ORDER  BY d.timestamp DESC NULLS LAST
LIMIT  1;

The choice between max() and ORDER BY / LIMIT hardly matters in comparison. You might as well:

SELECT max(d.timestamp) AS timestamp
FROM   sensors s
CROSS  JOIN LATERAL (
   SELECT timestamp
   FROM   data
   WHERE  sensor_id = s.id
   ORDER  BY timestamp DESC NULLS LAST
   LIMIT  1
   ) d
WHERE  s.station_id = 4;

Or:

SELECT max(d.timestamp) AS timestamp
FROM   sensors s
CROSS  JOIN LATERAL  (
   SELECT max(timestamp) AS timestamp
   FROM   data
   WHERE  sensor_id = s.id
   ) d
WHERE  s.station_id = 4;

Or even with a correlated subquery, shortest of all:

SELECT max((SELECT max(timestamp) FROM data WHERE sensor_id = s.id)) AS timestamp
FROM   sensors s
WHERE  station_id = 4;

Note the double parentheses!

The additional advantage of LIMIT in a LATERAL join is that you can retrieve arbitrary columns of the selected row, not just the latest timestamp (one column).

Related:

• Why do NULL values come first when ordering DESC in a PostgreSQL query?
• What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
• Select first row in each GROUP BY group?
• Optimize groupwise maximum query

Can I do a max(count(*)) in SQL?

Use:

  SELECT m.yr, 
         COUNT(*) AS num_movies
    FROM MOVIE m
    JOIN CASTING c ON c.movieid = m.id
    JOIN ACTOR a ON a.id = c.actorid
                AND a.name = 'John Travolta'
GROUP BY m.yr
ORDER BY num_movies DESC, m.yr DESC

Ordering by num_movies DESC will put the highest values at the top of the resultset. If numerous years have the same count, the m.yr will place the most recent year at the top... until the next num_movies value changes.

Can I use a MAX(COUNT(*)) ?

---

No, you can not layer aggregate functions on top of one another in the same SELECT clause. The inner aggregate would have to be performed in a subquery. IE:

SELECT MAX(y.num)
  FROM (SELECT COUNT(*) AS num
          FROM TABLE x) y

Is there a SQL technique for ordering by matching multiple criteria?

If I understood correctly, it looks like you can use expressions in your ORDER BY, in a way similar to the accepted answer given to the following Stack Overflow post:

• Using MySql, can I sort a column but have 0 come last?

Therefore, your query might look like this:

SELECT    imageID
FROM      ...
JOIN      ...
WHERE     designID = 100          
ORDER BY  garmentID = 1 DESC,
          colorID = 5 DESC,
          sizeID = 10 DESC;

Note that garmentID, colorID, and sizeID are not used as filters in the WHERE clause. The values are only used in the ORDER BY expressions.

Test case:

CREATE TABLE designs (designID int, garmentID int, colorID int, sizeID int);

INSERT INTO designs VALUES (100, 1, 1, 1);
INSERT INTO designs VALUES (100, 1, 2, 2);
INSERT INTO designs VALUES (100, 1, 5, 3);
INSERT INTO designs VALUES (100, 1, 5, 10);
INSERT INTO designs VALUES (100, 1, 5, 15);
INSERT INTO designs VALUES (100, 1, 8, 20);
INSERT INTO designs VALUES (100, 2, 5, 10);
INSERT INTO designs VALUES (100, 2, 6, 15);
INSERT INTO designs VALUES (101, 1, 1, 1);
INSERT INTO designs VALUES (101, 2, 1, 1);

Result:

SELECT    * 
FROM      designs 
WHERE     designID = 100 
ORDER BY  garmentID = 1 DESC, 
          colorID = 5 DESC, 
          sizeID = 10 DESC;

+----------+-----------+---------+--------+
| designID | garmentID | colorID | sizeID |
+----------+-----------+---------+--------+
|      100 |         1 |       5 |     10 |
|      100 |         1 |       5 |      3 |
|      100 |         1 |       5 |     15 |
|      100 |         1 |       1 |      1 |
|      100 |         1 |       2 |      2 |
|      100 |         1 |       8 |     20 |
|      100 |         2 |       5 |     10 |
|      100 |         2 |       6 |     15 |
+----------+-----------+---------+--------+
8 rows in set (0.02 sec)

Note how the row that matches the specified garmentID, colorID and sizeID is first. Failing that, the rows that match garmentID and colorID are next. Then the rows that only match garmentID follow. Then the rest, which only match the designID filter of the WHERE clause.

I believe it is worth doing this in SQL. As @Toby noted in the other answer, in general you don't need to worry about performance when sorting such a small number of rows, assuming you will always be filtering by designID... As for your other question, I don't know if there is a name for such a query - I tend to call it "ordering by an expression".

SQL select only rows with max value on a column

At first glance...

All you need is a GROUP BY clause with the MAX aggregate function:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

It's never that simple, is it?

I just noticed you need the content column as well.

This is a very common question in SQL: find the whole data for the row with some max value in a column per some group identifier. I heard that a lot during my career. Actually, it was one the questions I answered in my current job's technical interview.

It is, actually, so common that Stack Overflow community has created a single tag just to deal with questions like that: greatest-n-per-group.

Basically, you have two approaches to solve that problem:

Joining with simple group-identifier, max-value-in-group Sub-query

In this approach, you first find the group-identifier, max-value-in-group (already solved above) in a sub-query. Then you join your table to the sub-query with equality on both group-identifier and max-value-in-group:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Left Joining with self, tweaking join conditions and filters

In this approach, you left join the table with itself. Equality goes in the group-identifier. Then, 2 smart moves:

1. The second join condition is having left side value less than right value
2. When you do step 1, the row(s) that actually have the max value will have NULL in the right side (it's a LEFT JOIN, remember?). Then, we filter the joined result, showing only the rows where the right side is NULL.

So you end up with:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Conclusion

Both approaches bring the exact same result.

If you have two rows with max-value-in-group for group-identifier, both rows will be in the result in both approaches.

Both approaches are SQL ANSI compatible, thus, will work with your favorite RDBMS, regardless of its "flavor".

Both approaches are also performance friendly, however your mileage may vary (RDBMS, DB Structure, Indexes, etc.). So when you pick one approach over the other, benchmark. And make sure you pick the one which make most of sense to you.

Left join with conditions and limit to max of matching conditions

You can use outer apply. Your snippet is a bit hard to follow, but I think you want:

FROM OrderDetail od OUTER APPLY
     (SELECDT TOP (1) eh.*
      FROM Effort_Header eh
      WHERE od.MktgEffort = eh.MktgEffort AND
            od.OrderDate >= eh.Effort_Start_Date AND 
            od.OrderDate <= eh.Effort_End_Date
      ORDER BY eh.effort_year DESC
     ) eh

---

Related Topics