T-Sql: Selecting Column Based on Max(Other Column)

T-SQL: Selecting Column Based on MAX(Other Column)

Using a self join:

This will return all the values with subkey values that match, in case there are multiples.

SELECT a.value
  FROM TABLE a
  JOIN (SELECT MAX(t.subkey) AS max_subkey
          FROM TABLE t
         WHERE t.key = 1) b ON b.max_subkey = a.subkey
 WHERE a.key = 1

Using RANK & CTE (SQL Server 2005+):

This will return all the values with subkey values that match, in case there are multiples.

WITH summary AS (
  SELECT t.*,
         RANK() OVER(ORDER BY t.subkey DESC) AS rank
    FROM TABLE t
   WHERE t.key = 1)
SELECT s.value
  FROM summary s
 WHERE s.rank = 1

Using ROW_NUMBER & CTE (SQL Server 2005+):

This will return one row, even if there are more than one with the same subkey value...

WITH summary AS (
  SELECT t.*,
         ROW_NUMBER() OVER(ORDER BY t.subkey DESC) AS rank
    FROM TABLE t
   WHERE t.key = 1)
SELECT s.value
  FROM summary s
 WHERE s.rank = 1

Using TOP:

This will return one row, even if there are more than one with the same subkey value...

  SELECT TOP 1
         t.value
    FROM TABLE t
   WHERE t.key = 1
ORDER BY t.subkey DESC

SQL Select column with maximum value in another column

There are many ways to do this, here are some of them:

rextester for all of these: http://rextester.com/DTWB67044

max() over() version:

with cte as (
  select *, MaxValue = max([Value]) over (partition by [Group])
  from t
)
select Name
from cte
where [Value] = MaxValue;

inner join version:

select t.Name
from t
  inner join (
    select MaxValue=max(value), [Group]
    from t
    group by [Group]
    ) as m
      on t.[Group] = m.[Group]
     and t.[Value] = m.MaxValue;

cross apply() version:

select t.Name
from t
  cross apply (
    select top 1
        [Value]
      from t as i
      where i.[Group] = t.[Group]
      order by i.[Value] desc
     ) as x
  where t.[Value] = x.[Value];

---

If you will only be returning one value per group, these work as well:

common table expression with row_number() version:

with cte as (
select *, rn = row_number() over (partition by [Group] order by [Value] desc)
from t
)
select Name
from cte
where rn = 1;

top with ties version:

select top 1 with ties 
  t.Name
from t
order by row_number() over (partition by [Group] order by [Value] 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

SQL Query to get column values that correspond with MAX value of another column?

I would try something like this:

SELECT
   s.video_id
   ,s.video_category
   ,s.video_url
   ,s.video_date
   ,s.video_title
   ,short_description
FROM videos s
   JOIN (SELECT MAX(video_id) AS id FROM videos GROUP BY video_category) max
      ON s.video_id = max.id

which is quite faster that your own solution

SQL - Selecting a column value based on max value in another column and combination of values in another column - Teradata

You can use an approach which is similar to the aggregation you tried applying a dirty ol' trick, piggybacking.

You combine both columns into a single string, apply MAX and then strip off the date part again, e.g. for ACCT1 combining both PLAN_DATE & BASE_AMOUNT into a single string will result in this:

'20170101        100'
'20170111         30'
'20170113         50'
'20170118        180'
'20170122         80' -- this will be returned by MAX

After applying max you extract both columns again using SUBSTRING:

   CAST(SUBSTR('2017-01-22         80', 1, 10) AS DATE)
   CAST(SUBSTR('2017-01-22         80', 11) AS INT)

Of course you must create a string which is still sorting the correct way, e.g. yyyymmdd for a date and fixed width including leading spaces for numeric.

Now it's some Cut&Paste&Modify:

SELECT ACCOUNT_NUMBER,
   To_Date(Substr(RK, 1,8), 'yyyymmdd') AS MAX_RK_PLAN_DATE,
   Cast(Substring(RK From 9) AS INT) AS REQUIRED_RK_AMOUNT,
   To_Date(Substr(RC, 1,8), 'yyyymmdd') AS MAX_RC_PLAN_DATE,
   Cast(Substring(RC From 9) AS INT) AS REQUIRED_RC_AMOUNT
FROM 
 ( 
   SELECT ACCOUNT_NUMBER, 
      Max(CASE WHEN PLAN_code IN ('R','K') THEN To_Char(PLAN_DATE, 'yyyymmdd') || BASE_AMOUNT END) AS RK,
      Max(CASE WHEN PLAN_code IN ('R','C') THEN To_Char(PLAN_DATE, 'yyyymmdd') || BASE_AMOUNT END) AS RC
   FROM ACCNT_PLN_INFO
   WHERE  biz_date = DATE '2017-05-31'
   GROUP BY 1
 ) AS dt

select by max value of another column

SELECT ProductID,PriceNew 
FROM
( 
     SELECT *, ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY ChangeDate DESC) RN FROM Table1
) X 
WHERE RN = 1

Return another column value based on Max value from Column

You're looking for the most recent row per group which you can do using row_number

select t.personid, t.coursename, t.termname, t.Passingscore
from (
  select *, 
    Row_Number() 
      over (partition by personID, courseName, termName order by [date] desc) rn
  from t
)t
where rn=1;

Select max value in one column plus other columns in same table

Don't think "aggregation". Think "filtering". Then:

select u.*
from users_table u
where u.last_login = (select max(u2.last_login)
                      from users_table u2
                      where u2.user = u.uer
                     );

If you want to guarantee one row per user, you can use row_number():

select u.*
from (select u.*
             row_number() over (partition by user order by last_login desc) as seqnum
      from users_table u
     ) u
where seqnum = 1;

Select column value where other column is max of group

For SQL Server (and other products with windowed functions):

SELECT *
FROM
(
   SELECT
     *,
     ROW_NUMBER() OVER (PARTITION BY ID ORDER BY value desc) as rn
   FROM
     UnnamedTable
) t
WHERE
   t.rn = 1

---

Related Topics