sql query display the latest row

Julio Jose
Julio Jose used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
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ãoIT Engineer
Distinguished Expert 2017

Commented:
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 FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Ryan,

The second work for me.

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

Please help
Ryan ChongSoftware Team Lead

Commented:
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

Author

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
Ryan ChongSoftware Team Lead

Commented:
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

Author

Commented:
Hi Ryan,

it's close now the higher PD go to last row descending order but I need ascending order
Software Team Lead
Commented:
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;

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial