Avatar of Julio Jose
Julio Jose
Flag for Malaysia asked on

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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Julio Jose

8/22/2022 - Mon
Ryan Chong

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

Vitor Montalvão

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

Dale Fye

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Julio Jose

ASKER
Hi Ryan,

The second work for me.

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

Please help
Ryan Chong

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

Julio Jose

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ryan Chong

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

Julio Jose

ASKER
Hi Ryan,

it's close now the higher PD go to last row descending order but I need ascending order
ASKER CERTIFIED SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Julio Jose

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck