• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Selecting Latest Record using SQL

Saint Nick

Saint Nick

Lifer
Given a sample of my data:

Code: 
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?

Code: 
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
 
Last edited:
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.
 
Last edited:
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 )
 
sathyan said:
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 )
Click to expand...

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.
 
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.
 
Last edited:
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
 
Last edited:
Thanks for the reply KLin. I came up with pretty much the exact response you did. Mine looks a little different

Code: 
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!
 
Saint Nick said:
Thanks for the reply KLin. I came up with pretty much the exact response you did. Mine looks a little different

Code: 
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!
Click to expand...

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.
 
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
)
 
KLin said:
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.
Click to expand...
Yes, you are correct. I added a WHERE clause to equal date as well.
 
Guys! Guys! why all the complications? Analytical functions to the rescue! He said he was using oracle. Whohoo!

Code: 
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.
 
ForumMaster said:
Guys! Guys! why all the complications? Analytical functions to the rescue! He said he was using oracle. Whohoo!

Code: 
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.
Click to expand...
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).
 
Saint Nick said:
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).
Click to expand...

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. 😛
 
Last edited:
KLin said:
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. 😛
Click to expand...

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.
 
KLin said:
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. 😛
Click to expand...

The row_number function can be placed in the actual where clause and doesn't require a separate sub query.
 
KentState said:
The row_number function can be placed in the actual where clause and doesn't require a separate sub query.
Click to expand...

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.
 
ForumMaster said:
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.
Click to expand...

You are correct. I was actually thinking about putting the query in a CTE and using a where clause to do the filter.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top