CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Anaiah
Anaiah

Reputation: 633

Getting the latest row in sql (getting one out of duplicates)

I have a database wherein I store data and once it is updated the updated data will be saved .. (have the same row data except the status and approvers) For better understanding here is my table 

    | RequestID   |  RequestDate  |  PartNumber  | StatusID |  FullName  |
    | 171367      |  2015-05-11   | PTA#522589   |   4      | Jfuentes   |
    | 171367      |  2015-05-11   | PTA#522589   |   5      | JReyes     |
    | 171367      |  2015-05-11   | PTA#522589   |   6      | JLabo      |
    | 171367      |  2015-05-11   | PTA#522589   |   7      | JGulbin    |
    | 171367      |  2015-05-11   | PTA#522589   |   8      | Egulbin    |

So everytime the the request is approved another row is added.

But then, I wanted to get only one out the 5 which has the latest status of the RequestID ..

I tried using Distinct but there are columns that I can't include and get this error :

The text, ntext, or image data type cannot be selected as DISTINCT.

Is there any other way to get the all the columns in the row I wanted to have ?

Here's my query :

SELECT r.[RequestID],r.[RequestDate],r.[PARNumber],r.[StatusID],p.[DeliveryDate] 
, r.[PurchaseComment],r.[UID],p.[DeliveryDate],r.[FullName],r.[Email]          
,r.[EntityName],r.[DepartmentName],r.[DepartmentID],r.[InboxUID]
,r.[ProgramID],r.[ProgramCode],r.[ProgramName],r.[CostCenterCode]           
,p.[PartDesc],p.[SupplierID],p.[AccountType],p.[CurrName]
,p.[PartQuantity],p.[PiecePrice], p.[PartNumber], p.[DeliveryDate], p.[UnitName] 
FROM [NOP_PR].[dbo].[Requests] r 
JOIN [NOP_PR].[dbo].[Parts] p on p.[RequestID] = r.[RequestID]
JOIN [NOP_PR].[dbo].[REQApproval] a on a.[RequestID] = r.[RequestID] 
JOIN [NOP_PR].[dbo].[Departments] d on d.[DepartmentID] = r.[DepartmentID]
WHERE  [EntityName]  like '%.PTA'

Is there any other way to get the all the columns in the row I wanted to have ? All I needed is to query all the Requests without duplicating it on my web ..

Any help would do.

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Assuming that "latest" status means the most recent, then you can use row_number():

select t.*
from (select t.*, row_number() over (partition by requestid
                                     order by requestdate desc, statusid desc) as seqnum
      from mytable t
     ) t
where seqnum = 1;

I am not sure, though, what your sample data has to do with your query. You only show data for one table, but your query mentions several others.

Upvotes: 1

Related Questions