Sql Max(date)

[OlaPola]

Pls, i need help.....
Otherwise i have to write my CV very soon...

PK : USER_ID,FROM_DATE

Each user has 3 rows. I need all users but only with the last row where FROM_DATE is the last
This is the basic SQL I need. Now….. Where can we put MAX(FROM_DATE )?

SELECT   USER_ID,
                to_char(FROM_DATE,'ddmmyyyy'),
                NVL(SALE_CHANNEL_CODE,0),
                NVL(PROFILE_CODE,0),
                USER_NAME, 
                NVL(DIVISION_CODE,0), 
                NVL(REGION_CODE,0),
                NVL( DEPARTMENT_CODE,0),
                NVL(STORE_NUM, 0),
                NVL(SECTION_CODE,0),
                NVL(ROLE_CODE,0),
                STATUS
FROM  NAM_USERS_MANAGE
 WHERE  DIVISION_CODE IN (1,3) AND USER_ID IN  ( SELECT  USER_ID
                                                                   FROM  NAM_USERS )
 

I took one example from the Internet :

SELECT t.EmployeeId, t.ReviewDate, t.ReviewRating
FROM   tbl_stuff t
       INNER JOIN (
          SELECT EmployeeId, MAX(ReviewDate) AS MaxReviewDate
          FROM tbl_stuff GROUP BY EmployeeId
       ) t1 ON t.EmployeeId = t1.EmployeeId AND t.ReviewDate = t1.MaxReviewDate
WHERE (some filters on t)
 

But I've got "SQL Command was not properly ended"
So I took another one from the Internet:

SELECT P.*
FROM (SELECT USER_ID, MAX(FROM_DATE) AS FROM_DATE
      FROM NAM_USERS_MANAGE
      GROUP BY USER_ID) AS O
INNER JOIN
NAM_USERS_MANAGE AS P
ON P.USER_ID = O.USER_ID 
P.FROM_DATE = O.FROM_DATE;
 

And I've got "SQL Command was not properly ended" again.
So I thought maybe its related to SQL 2005 or something sp I took this one:

select EmployeeID, ReviewDate, ReviewRating
from
    (select EmployeeID, ReviewDate, ReviewRating, rank() over (partition by EmployeeID order by ReviewDate desc) as rank
     from tbl_Stuff
     where EmployeeID = X) a
where rank = 1
 

But it gives me all the data and I have no idea what is RANK...

[pbmods]

http://www.dummies.com/WileyCDA/Dumm...le/id-551.html

[pradeep kaltari]

Pls, i need help.....
Otherwise i have to write my CV very soon...

PK : USER_ID,FROM_DATE

Each user has 3 rows. I need all users but only with the last row where FROM_DATE is the last
This is the basic SQL I need. Now….. Where can we put MAX(FROM_DATE )?

SELECT USER_ID,
to_char(FROM_DATE,'ddmmyyyy'),
NVL(SALE_CHANNEL_CODE,0),
NVL(PROFILE_CODE,0),
USER_NAME,
NVL(DIVISION_CODE,0),
NVL(REGION_CODE,0),
NVL( DEPARTMENT_CODE,0),
NVL(STORE_NUM, 0),
NVL(SECTION_CODE,0),
NVL(ROLE_CODE,0),
STATUS
FROM NAM_USERS_MANAGE
WHERE DIVISION_CODE IN (1,3) AND USER_ID IN ( SELECT USER_ID
FROM NAM_USERS )

I took one example from the Internet :

SELECT t.EmployeeId, t.ReviewDate, t.ReviewRating
FROM tbl_stuff t
INNER JOIN (
SELECT EmployeeId, MAX(ReviewDate) AS MaxReviewDate
FROM tbl_stuff GROUP BY EmployeeId
) t1 ON t.EmployeeId = t1.EmployeeId AND t.ReviewDate = t1.MaxReviewDate
WHERE (some filters on t)

But I've got "SQL Command was not properly ended"


So I took another one from the Internet:
SELECT P.*
FROM (SELECT USER_ID, MAX(FROM_DATE) AS FROM_DATE
FROM NAM_USERS_MANAGE
GROUP BY USER_ID) AS O
INNER JOIN
NAM_USERS_MANAGE AS P
ON P.USER_ID = O.USER_ID
P.FROM_DATE = O.FROM_DATE;

And I've got "SQL Command was not properly ended" again.
So I thought maybe its related to SQL 2005 or something sp I took this one:
select EmployeeID, ReviewDate, ReviewRating
from
(select EmployeeID, ReviewDate, ReviewRating, rank() over (partition by EmployeeID order by ReviewDate desc) as rank
from tbl_Stuff
where EmployeeID = X) a
where rank = 1

But it gives me all the data and I have no idea what is RANK...

Try the following:

SELECT   USER_ID,
                to_char(FROM_DATE,'ddmmyyyy'),
                NVL(SALE_CHANNEL_CODE,0),
                NVL(PROFILE_CODE,0),
                USER_NAME, 
                NVL(DIVISION_CODE,0), 
                NVL(REGION_CODE,0),
                NVL( DEPARTMENT_CODE,0),
                NVL(STORE_NUM, 0),
                NVL(SECTION_CODE,0),
                NVL(ROLE_CODE,0),
                STATUS
FROM  NAM_USERS_MANAGE OUTER
 WHERE  DIVISION_CODE IN (1,3) AND USER_ID IN  ( SELECT  USER_ID
                                                                   FROM  NAM_USERS)
AND DATE_FROM = (SELECT MAX(DATE_FROM) FROM NAME_USERS_MANAGE INNER
WHERE OUTER.USER_ID=INNER.USER_ID )
GROUP BY USER_ID
 

I hope this solves your problem.