Selecting Latest Record using SQL

[Saint Nick]

Given a sample of my data:

PROJECT_ID        PROJECT_MANAGER        EFFDT

000000000000501        09705            6/12/2009
000000000000501        08381            11/17/2009
000000000000514        10472            10/20/2009
000000000000514        06423            6/19/2009
000000000000515        05299            6/23/2009
000000000000516        04817            6/25/2009
000000000000517        05299            6/26/2009
000000000000518        05299            6/26/2009

How would I write some SQL to grab only the latest EFFDT so my output would look like this?

PROJECT_ID        PROJECT_MANAGER        EFFDT

000000000000501        08381            11/17/2009
000000000000514        10472            10/20/2009
000000000000515        05299            6/23/2009
000000000000516        04817            6/25/2009
000000000000517        05299            6/26/2009
000000000000518        05299            6/26/2009

 

[Ka0t1x]

Which DBMS?

 

[Train]

Assuming SQL Server:

SELECT Top 6 * FROM tableName ORDER BY EFFDT DESC

though Select Top is decprecated IIRC, there's a limit rowcount function that is technically better, can't remember what it is called though.

The problem with select Top is it's not going to produce the exact results every time, i.e. if there is a 3 way tie for the last spot, there's no telling which of the 3 will take the last spot. There is a "With ties" option on Select Top if that is OK with you, but it might make your expected 6 rows jump up to any number depending.

 

[Crusty]

SELECT * FROM table ORDER BY EFFDT DESC LIMIT 6;

 

[sathyan]

select PROJECT_ID, PROJECT_MANAGER, EFFDT
from TABLE
WHERE EFFDT = (SELECT MAX(M.EFFDT) FROM TABLE M
WHERE TABLE.PROJECT_ID = M.PROJECT_ID
AND M.EFFDT <= SYSDATE )

 

[Train]

select PROJECT_ID, PROJECT_MANAGER, EFFDT
from TABLE
WHERE EFFDT = (SELECT MAX(M.EFFDT) FROM TABLE M
WHERE TABLE.PROJECT_ID = M.PROJECT_ID
AND M.EFFDT <= SYSDATE )

That would only select a single record,
It assumes they want records earlier than now
It uneccessarily joins the tables

Basically, you missed the OP's requirements by a mile.

 

[Saint Nick]

We are using Oracle. I'm sorry I didn't specify that in the OP.

Also, I can't just use LIMIT or TOP. There are 3600 rows in the table and I need each result.

I reworded the OP to make more sense. If there are ties, that is okay (it probably won't happen in our data anyways). While sathyan did miss the requirements...I'm thinking the query is on the right track. It might have to be a pretty nasty looking query with a few subqueries. I'm going to keep plugging away and will post the results if I can figure it out.

 

[KLin]

Sounds like you want all projectIDs and their latest effective date to me.

SELECT PROJECT_ID, PROJECT_MANAGER, EFFDT
FROM T1 INNER JOIN (SELECT PROJECT_ID, Max(EFFDT) As LatestDate GROUP BY PROJECT_ID) T2
ON T1.PROJECT_ID = T2.PROJECT_ID AND T1.EFFDT = T2.LatestDate

 

[Saint Nick]

Thanks for the reply KLin. I came up with pretty much the exact response you did. Mine looks a little different

SELECT      left.PROJECT_ID, left.PROJECT_MANAGER, left.EFFDT
FROM        PS_PROJECT_MGR left,
            (
                SELECT      PROJECT_ID, MAX(EFFDT)
                FROM        PS_PROJECT_MGR
                GROUP BY    PROJECT_ID
            ) right
WHERE       left.PROJECT_ID = right.PROJECT_ID
ORDER BY    1;

Glad there are lots of SQL guys on the board. I love SQL, but am not quite a master yet...thanks again everyone for helping me out!

 

[KLin]

Thanks for the reply KLin. I came up with pretty much the exact response you did. Mine looks a little different

SELECT      left.PROJECT_ID, left.PROJECT_MANAGER, left.EFFDT
FROM        PS_PROJECT_MGR left,
            (
                SELECT      PROJECT_ID, MAX(EFFDT)
                FROM        PS_PROJECT_MGR
                GROUP BY    PROJECT_ID
            ) right
WHERE       left.PROJECT_ID = right.PROJECT_ID
ORDER BY    1;

Glad there are lots of SQL guys on the board. I love SQL, but am not quite a master yet...thanks again everyone for helping me out!

is that query getting you a list of distinct project Ids and their latest effective date? Seems to me you should also include a match on the date field in the where clause as well.

 

[brandonbull]

Also a solution:

SELECT
t1.PROJECT_ID,
t1.PROJECT_MANAGER,
t1.EFFDT
FROM PS_PROJECT_MGR T1
where t1.effdt = (SELECT Max(t2.EFFDT) from PS_PROJECT_MGR t2 where T1.PROJECT_ID = t2.PROJECT_ID
)

 

[Saint Nick]

is that query getting you a list of distinct project Ids and their latest effective date? Seems to me you should also include a match on the date field in the where clause as well.

Yes, you are correct. I added a WHERE clause to equal date as well.

 

[ForumMaster]

Guys! Guys! why all the complications? Analytical functions to the rescue! He said he was using oracle. Whohoo!

select project_id,effdt,
  MAX(effdt) KEEP (DENSE_RANK LAST ORDER BY effdt desc) OVER (PARTITION BY project_id) "Highest" from table

This query will do a full table scan (you said you wanted everything, all 3600 rows).
It will rank each effdt in descending order for each project id.

Enjoy.

 

[Saint Nick]

Guys! Guys! why all the complications? Analytical functions to the rescue! He said he was using oracle. Whohoo!

select project_id,effdt,
  MAX(effdt) KEEP (DENSE_RANK LAST ORDER BY effdt desc) OVER (PARTITION BY project_id) "Highest" from table

This query will do a full table scan (you said you wanted everything, all 3600 rows).
It will rank each effdt in descending order for each project id.

Enjoy.

Wow...very cool. I am a SQL Server guy originally so Oracle is a new beast. This is sweet! SQL Server can't do that (I don't think).

 

[KLin]

Wow...very cool. I am a SQL Server guy originally so Oracle is a new beast. This is sweet! SQL Server can't do that (I don't think).

SELECT * FROM
(
select PROJECT_ID, PROJECT_MANAGER, EFFDT, ROW_NUMBER() OVER (PARTITION BY PROJECT_ID Order by EFFDT) As RowNum from Table1
) sub where rownum = 1


How it's done in SQL Server. And another way to accomplish the original request.

 

[ForumMaster]

SELECT * FROM
(
select PROJECT_ID, PROJECT_MANAGER, EFFDT, ROW_NUMBER() OVER (PARTITION BY PROJECT_ID Order by EFFDT) As RowNum from Table1
) sub where rownum = 1


How it's done in SQL Server. And another way to accomplish the original request.

similar effect, but it believe the optimizer will do a query over a view to filter the result set. plus, a little but less readable. there's also the one key difference between dense_rank and rank: rank will give and equal rank for ties while dense_rank doesn't. so that's why dense_rank (something i don't believe sql server has) is better. max is best in this case.

 

[KentState]

SELECT * FROM
(
select PROJECT_ID, PROJECT_MANAGER, EFFDT, ROW_NUMBER() OVER (PARTITION BY PROJECT_ID Order by EFFDT) As RowNum from Table1
) sub where rownum = 1


How it's done in SQL Server. And another way to accomplish the original request.

The row_number function can be placed in the actual where clause and doesn't require a separate sub query.

 

[ForumMaster]

The row_number function can be placed in the actual where clause and doesn't require a separate sub query.

i believe you're mistaken. while i don't know sql server (i am an oracle DBA),
in oracle, you cannot filter a predicate where the predicate is an analytical function/windows function. nor will it work with the having clause.

you must use a subquery to filer the resultset.

 

[KentState]

i believe you're mistaken. while i don't know sql server (i am an oracle DBA),
in oracle, you cannot filter a predicate where the predicate is an analytical function/windows function. nor will it work with the having clause.

you must use a subquery to filer the resultset.

You are correct. I was actually thinking about putting the query in a CTE and using a where clause to do the filter.