sql query display the latest row

Currrently I use query 

select NAME,ADDR,ID,E_DT,P_DT,PD from C_VW ORDER BY PD DESC;

Open in new window


to get the attached output, the output contain many entry based on the "ID"

I wish to get the same output but with uniq entry for each "NAME" and the latest row
Julio JoseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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;

Open 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;

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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;

Open in new window

0
Dale FyeCommented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
Julio JoseAuthor Commented:
Hi Ryan,

The second work for me.

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

Please help
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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;

Open in new window

0
Julio JoseAuthor Commented:
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
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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;

Open in new window

0
Julio JoseAuthor Commented:
Hi Ryan,

it's close now the higher PD go to last row descending order but I need ascending order
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
do you mean this?

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;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Julio JoseAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.