SQL Most Recent Using Row_Number() Over Partition

SQL most recent using row_number() over partition

You should be able to move your query to a subquery and add where criteria:

SELECT user_id, page_name, recent_click
FROM (
  SELECT user_id,
         page_name,
         row_number() over (partition by session_id order by ts desc) as recent_click
  from clicks_data
) T
WHERE recent_click = 1

Getting the First and Last Row Using ROW_NUMBER and PARTITION BY

This can be done using window functions min and max.

select distinct name, 
min(timestamp) over(partition by name), max(timestamp) over(partition by name)
from tablename

Example

Edit: Based on the comments

select t.name,t.value,t1.earliest,t1.latest
from t 
join (select distinct name, 
      min(tm) over(partition by name) earliest, max(tm) over(partition by name) latest
      from t) t1 on t1.name = t.name and t1.latest = t.tm

Edit: Another approach is using the first_value window function, which would eliminate the need for a sub-query and join.

select distinct
name, 
first_value(value) over(partition by name order by timestamp desc) as latest_value,
min(tm) over(partition by name) earliest,
-- or first_value can be used 
-- first_value(timestamp) over(partition by name order by timestamp)
max(tm) over(partition by name) latest
-- or first_value can be used
-- first_value(timestamp) over(partition by name order by timestamp desc)
from t

Select last row using ROW_NUMBER function

Yes, you could add descending order and then filter using that column:

SELECT *
FROM (
  SELECT department_id, last_name, employee_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id DESC) l_emp_id 
   FROM employees
) 
WHERE l_emp_id = 1

SQL query row_number() over partition by query result return wrong for some case

You should move the condition for the Tenant from the outer query inside the subquery:

select * 
from (
  select fs1.schemaid, fs1.schemaName, ar1.roleId, ar1.roleName, ar1.tenant,
         row_number() over (
           partition by fs1.schemaName
           order by case when ar1.tenant = 'ALL' then 2 else 1 end, ar1.tenant
         ) rn
  from RolesTable ar1 full outer join FormSchema fs1
  on ar1.SchemaId= fs1.SchemaId
  where ar1.Tenant in (?, 'All')
) as t3
where rn = 1

Replace ? with 'A' or 'B'.

Also, it is not clear why you are doing a FULL join instead of a LEFT join.

If you want in the results unmatched rows from both tables, then may be you should use COALESCE():

where coalesce(ar1.Tenant, fs1.Tenant) in (?, 'All')

See the demo.

SQL Server - ROW_NUMBER() with PARTITION, how to get multiple records?

Use rank() and filtering:

SELECT *
FROM (SELECT ins.ID, ins.UnitElement_ID, ins.Date, ue.Code, 
             RANK() OVER (PARTITION BY ins.UnitElement_ID ORDER BY ins.Date DESC) AS lastAnomaly
      FROM Inspection ins JOIN
           UnitElement ue
           ON ins.UnitElement_ID = ue.ID JOIN
           InspectionedAnomaly ia
           ON ia.Inspection_ID = ins.ID
      WHERE ins.UnitElement_ID IN (3, 10) AND
            ins.Evaluation IS NOT NULL
     ) sa
WHERE lastAnomaly = 1;

Or if you prefer, you can use MAX():

             MAX(ins.DATE) OVER (PARTITION BY ins.UnitElement_ID) AS lastAnomalyDate
 . . .
 WHERE lastAnomalyDate = DATE

ROW_NUMBER() OVER (PARTITION BY) gives same row numbers

use this : ROW_NUMBER() OVER(ORDER BY code) it is continue sequence and you use partition by it manse it reset every code.

SELECT ROW_NUMBER() OVER (ORDER BY "ItemCode") AS "ID", "ItemCode", "CommitedQty", "JobId", "WarehouseID"
    FROM
        (SELECT "ItemCode", SUM("CommitedQty") AS "CommitedQty","JobId", "WarehouseID"
        FROM "Stock" 
        WHERE "BaseEntry" = 10352
        GROUP BY "ItemCode","JobId", "WarehouseID")

Get the record with the most recent date. SQL

Adjust your current query to sort row_number descending and add a partition on the month of your date. Then, add a where clause to pull rn = 1:

SELECT T.*
FROM (
  SELECT ID, value, date,
    ROW_NUMBER() OVER (PARTITION BY ID, month(Date) ORDER BY Date desc) AS rn
  FROM table
) t
where t.rn = 1

If you need to expand this to multiple years, you can inlcude a partition on the year as well:

SELECT T.*
FROM (
  SELECT ID, value, date,
    ROW_NUMBER() OVER (PARTITION BY ID, month(Date), year(Date) ORDER BY Date desc) AS rn
  FROM table
) t
where t.rn = 1

---

Related Topics