sql query display the latest row

try:

select NAME,ADDR,ID,E_DT,P_DT,PD from
(
select NAME,ADDR,ID,E_DT,P_DT,PD,
row_number() over (partition by NAME order by ID desc) idx
 from C_VW
) a
where idx = 1
ORDER BY PD DESC;

Select allOpen in new window

or

select NAME,ADDR,ID,E_DT,P_DT,PD from
(
select NAME,ADDR,ID,E_DT,P_DT,PD,
row_number() over (partition by NAME order by PD desc) idx
 from C_VW
) a
where idx = 1
ORDER BY PD DESC;

Select allOpen in new window

You attached nothing.
You also didn't provide any information to let us know what defines a last row. Assuming that is the record with higher PD, this can be a solution (adapt it if my assumption of PD is wrong):

select NAME,ADDR, ID,E_DT,P_DT, MAX(PD) as LastRow
from C_VW 
group by NAME, ADDR, ID, E_DT,P_DT
ORDER BY PD DESC;

Select allOpen in new window

or, if you want all of the data associated with the largest ID for each person then maybe something like:

SELECT [Name], Addr, ID, E_DT, P_DT
FROM C_VW
INNER JOIN (
SELECT [Name], Max(ID) as MaxID
FROM C_VW
GROUP BY [Name]
) as M on C_VW.[Name] = M.[Name] AND C_VW.ID = M.MaxID

Assuming of course that NAME is unique,  If not, then you might have to group by Name and Addr

Hi Ryan,

The second work for me.

I need to another add another logic like where E_D="RT - M"

Please help

you can try:

select NAME,ADDR,ID,E_DT,P_DT,PD from
(
select NAME,ADDR,ID,E_DT,P_DT,PD,
row_number() over (partition by NAME order by PD desc) idx
 from C_VW
where E_D= 'RT - M'
) a
where idx = 1
ORDER BY PD DESC;

Select allOpen in new window

Hi Ryan,

it work, I also need to sort by the latest  E_DT after sort by PD

E_DT
2017-03-29 00:37:45.000

it work, I also need to sort by the latest  E_DT after sort by PD

so this is a quick try:

select NAME,ADDR,ID,E_DT,P_DT,PD from
(
select NAME,ADDR,ID,E_DT,P_DT,PD,
row_number() over (partition by NAME order by PD desc, E_DT desc) idx
 from C_VW
where E_D= 'RT - M'
) a
where idx = 1
ORDER BY PD DESC, E_DT desc;

Select allOpen in new window

Hi Ryan,

it's close now the higher PD go to last row descending order but I need ascending order

I'm good with this query, thank you

however I have follow up question to join another table row to this table, can you take a look

https://www.experts-exchange.com/questions/29013297/MSSQL-join-different-row-from-other-table.html