CODEWITHSUNDEEP
sqlt-sqldatedatetimejoin
sanchezis
sanchezis

Reputation: 463

SQL Get most recent date from table to be included in a VIEW with inner join

sample table

I have this issue and I cant find out a correct solution. The following image shows a table where I have different records going in. The keys for the records are RID and NAME, and I would like to create a query that returns only most recent dates from both keys (marked in grey in the image).

I would appreciate this comunity help in trying to make it work, I have already tried joining with it self and try to get the Date1 > Date2 without success.

I solve this by using this query: 

SELECT  *
 FROM     <table> as o
 inner join
 (
 select  RID, NAME, max(CREATED) as CREATED from <table> group by  RID, NAME
 ) as t on t.NAME=o.NAME and t.RID=o.RID and o.CREATED=t.CREATED
 order by ID

I would appreciate if you can find a better solution to it so I can also get the ID in the query?

Upvotes: 0

Views: 271

Answers (3)

Tomas Bartalos
Tomas Bartalos

Reputation: 1276

If you don't want to realy on max(ID) being in the same row as max(CREATED) and also want a better performance, you can use ROW_NUMBER analytical function.

That way you can return all columns of the row.

SELECT  ID, RID, NAME, CREATED,
ROW_NUMBER () OVER (
    PARTITION BY RID, NAME
    ORDER BY CREATED DESC
)
FROM <table> 
WHERE row_number = 1;

We first rank all rows of the group and take the first row from each of the groups. Since the order by is DESC, the first row is the most recent one.

Upvotes: 0

Kuba Wyrostek
Kuba Wyrostek

Reputation: 6221

Since maximum ID is related to maximum CREATED you can use aggregates to find maximum CREATED and ID for each distinct pair of RID, NAME:

select RID, NAME, max(ID), max(CREATED) from <your-table-name> group by RID, NAME

Upvotes: 2

sanchezis
sanchezis

Reputation: 463

I solve this by using this query: 

SELECT  *  FROM     <table> as o  inner join  (  select  RID, NAME,
max(CREATED) as CREATED from <table> group by  RID, NAME  ) as t on
t.NAME=o.NAME and t.RID=o.RID and o.CREATED=t.CREATED  order by ID

I would appreciate if you can find a better solution to it so I can also get the ID in the query?

Upvotes: 0

Related Questions