How to Select Only Row with Max Sequence Without Using a Subquery

how to select only row with max sequence without using a subquery?

Assuming SQL-Server ( >= 2005) or Oracle (10g?):

WITH CTE AS
( 
   SELECT
       ROW_NUMBER() OVER (PARTITION BY ID  ORDER BY Seq DESC) AS RN
       , ID, Age
   FROM 
       Persons
)
SELECT ID, Age 
FROM CTE
WHERE RN = 1

ROW_NUMBER returns the sequential number of a row within a partition of a result set.

Edit: works also in Oracle as you can see here: http://sqlfiddle.com/#!4/b7e79/2/0

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

getting the row corresponding to the max of other row

I made an sqlfiddle where I listed 4 different ways to achieve what you want. Note, that I added another row to your example. So you have two rows with the maximum date. See the difference between the queries? Manoj's way will give you just one row, although 2 rows match the criteria. You can click on "View execution plan" to see the difference how SQL Server handles these queries.

The 4 different ways (written in standard SQL, they should work with every RDBMS):

select A 
from TAB 
where 
B = (select max(B) from TAB);

select top 1 * from tab order by b desc;

select 
*
from
tab t1
left join tab t2 on t1.b < t2.b
where t2.b is null;

select
*
from
tab t1
inner join (
  select max(b) as b from tab
  ) t2 on t1.b = t2.b;

and here two more ways especially for SQL Server thanks to a_horse_with_no_name:

select *
from (
  select a, 
         b,
         rank() over (order by b desc) as rnk  
  from tab
) t
where rnk = 1;

select *
from (
  select a,
         b, 
         max(b) over () as max_b
  from tab
) t
where b = max_b;

See them working here.

get the max sequence number of a specific column grouping

We can try to use aggregate function subquery with JOIN to make it.

SELECT f.*
FROM E_FixedAssetLedger f
INNER JOIN (
    SELECT Fal_AssetCode,Max(Fal_SeqNo) Fal_SeqNo
    FROM E_FixedAssetLedger
    GROUP BY Fal_AssetCode
) fa ON f.Fal_AssetCode = fa.Fal_AssetCode
AND f.Fal_SeqNo = fa.Fal_SeqNo

If your RDBMS support window function we can try to use ROW_NUMBER window function to get Max(SeqNo) each AssetCode

SELECT *
FROM (
 SELECT *,
    ROW_NUMBER() OVER(PARTITION BY AssetCode ORDER BY  SeqNo DESC) rn
 FROM E_FixedAssetLedger
) t1
WHERE rn = 1

Fetch the rows which have the Max value for a column for each distinct value of another column

This will retrieve all rows for which the my_date column value is equal to the maximum value of my_date for that userid. This may retrieve multiple rows for the userid where the maximum date is on multiple rows.

select userid,
       my_date,
       ...
from
(
select userid,
       my_date,
       ...
       max(my_date) over (partition by userid) max_my_date
from   users
)
where my_date = max_my_date

"Analytic functions rock"

Edit: With regard to the first comment ...

"using analytic queries and a self-join defeats the purpose of analytic queries"

There is no self-join in this code. There is instead a predicate placed on the result of the inline view that contains the analytic function -- a very different matter, and completely standard practice.

"The default window in Oracle is from the first row in the partition to the current one"

The windowing clause is only applicable in the presence of the order by clause. With no order by clause, no windowing clause is applied by default and none can be explicitly specified.

The code works.

sql select everything with maximum date (that that is smaller than a specific date) without subqueries

You can find the maximum date without sub-queries.

SELECT t.*,
       max("DATE") OVER(PARTITION BY id ORDER BY "DATE" DESC) as max_date
FROM   "TABLE" t
WHERE  "DATE" < DATE '2021-07-16'

You need a sub-query to filter to only show the row(s) with the maximum date:

SELECT *
FROM   (
  SELECT t.*,
         max("DATE") OVER(PARTITION BY id ORDER BY "DATE" DESC) as max_date
  FROM   "TABLE" t
  WHERE  "DATE" < DATE '2021-07-16'
)
WHERE  "DATE" = max_date;

However, you are still only querying the table once using this technique even though it uses a sub-query.

Note DATE and TABLE are reserved words and cannot be used as unquoted identifiers; it would be better practice to use different names for those identifiers.

You could, equivalently use the RANK or DENSE_RANK analytic functions instead of MAX; ROW_NUMBER, however, does not give the same output as it will only return a single row and will not return all tied rows.

SELECT *
FROM   (
  SELECT t.*,
         RANK() OVER(PARTITION BY id ORDER BY "DATE" DESC) as rnk
  FROM   "TABLE" t
  WHERE  "DATE" < DATE '2021-07-16'
)
WHERE  rnk = 1;

But you still need a sub-query to filter the rows.

---

If you want to not use a sub-query then you can use:

SELECT id,
       MAX("DATE") AS "DATE",
       MAX(col1) KEEP (DENSE_RANK LAST ORDER BY "DATE", ROWNUM) AS col1,
       MAX(col2) KEEP (DENSE_RANK LAST ORDER BY "DATE", ROWNUM) AS col2,
       MAX(col3) KEEP (DENSE_RANK LAST ORDER BY "DATE", ROWNUM) AS col3
FROM   "TABLE"
GROUP BY id

However, that is not quite the same as it will only get a single row per id and will not return multiple rows tied for the greatest date per id.

MySQL - How to select rows with max value of a field

If you want to get ties, then you can do something like this:

select s.*
from scores s
where s.score = (select max(s2.score) from scores s2 where s2.level = s.level);

You could get one row per level by aggregating this:

select s.level, s.score, group_concat(s.user_id)
from scores s
where s.score = (select max(s2.score) from scores s2 where s2.level = s.level)
group by s.level, s.score;

This combines the users (if there is more than one) into a single field.

Optimizing Query. Want to pick the last record without using max in sub-query

It's likely slow because it's running a correlated subquery for every row of the outer query. There are two solutions that tend to run more efficiently.

One is to use a derived table, which uses a subquery, but it only executes the subquery once to prepare the derived table.

SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE 
FROM ITEMMASTER A
JOIN STOCKENTRY B ON A.ITEMID = B.ITEMID
JOIN (SELECT ITEMID, MAX(RECORDID) AS MAXRECORDID 
      FROM STOCKENTRY GROUP BY ITEMID) M
  ON (M.ITEMID, M.MAXRECORDID) = (B.ITEMID, B.RECORDID)
WHERE A.STOCKINHAND > 0 
  AND B.SALEPRICE > 0 
  AND B.INVOICEDATE IS NOT NULL 
ORDER BY A.ITEMNAME, B.INVOICEDATE;

The other solution is to use an exclusion join to find the row in B such that no other row exists with the same itemid and a greater recordid. With correct indexes (e.g. a compound index on (ITEMID, RECORDID), this should perform very well.

SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE 
FROM ITEMMASTER A
JOIN STOCKENTRY B ON A.ITEMID = B.ITEMID 
LEFT OUTER JOIN STOCKENTRY B2
  ON B.ITEMID = B2.ITEMID AND B.RECORDID < B2.RECORDID
WHERE B2.ITEMID IS NULL 
  AND A.STOCKINHAND > 0 
  AND B.SALEPRICE > 0 
  AND B.INVOICEDATE IS NOT NULL 
ORDER BY A.ITEMNAME, B.INVOICEDATE;

This type of problem comes up frequently on Stack Overflow. I've added the greatest-n-per-group tag to the question so you can see other cases.

---

Re @RPK's comment:

I don't use MySQL QB myself, and that app has changed so many times I can't advise on how to use it. But in the mysql monitor (command-line), I use a combination of EXPLAIN and PROFILING to give me stats.

However, you made a comment about not being to modify (or create?) indexes. That's going to hamstring your attempts to optimize.

---

Related Topics