We help IT Professionals succeed at work.

sql query display the latest row

216 Views
Last Modified: 2017-04-01
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

CERTIFIED EXPERT

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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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

Author

Commented:
Hi Ryan,

The second work for me.

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

Please help
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions