CODEWITHSUNDEEP
sqlsql-server
hotseetotsee
hotseetotsee

Reputation: 71

MAX function does not return maximum value, returned all value instead

I have a table of User_Transfer that looked like below

ID    User_ID  User_No   Date_From    Date_To
1     00001    KJH789    2014-04-26   2014-04-29
2     00001    KJH789    2014-04-01   2014-04-04
3     00004    TGI567    2014-04-26   2014-04-29
4     00026    RTW763    2014-04-09   2014-04-12

and table User_Profile 

ID    User_ID    User_Name   Nationality_Code
1     00001      John        JAP
2     00004      Sarah       SG
3     00026      Timmy       SG

My query:

    SELECT X.USER_ID,
           X.USER_NO,
           X.USER_NAME,
           X.NATIONALITY_CODE,
           U.DATE_FROM,
           X.DATE_TO
   FROM
     (SELECT 
      ID,
      USER_ID,
      USER_NO,
     (SELECT EB.USER_NAME FROM USER_PROFILE EB WHERE EB.USER_ID = USER_TRANSFER.USER_ID) AS USER_NAME,
     (SELECT EB.NATIONALITY_CODE FROM USER_PROFILE EB WHERE EB.USER_ID = USER_TRANSFER.USER_ID) AS NATIONALITY_CODE
     MAX(DATE_TO) AS DATE_TO
     FROM USER_TRANSFER
     GROUP BY
        ID,
        USER_ID,
        USER_NO,
        EMPLOYEE_NO) X,
   USER_TRANSFER U
   WHERE
     U.ID = X.ID AND
     U.USER_ID = X.USER_ID
   ORDER BY X.USER_NO

My query above returned 4 records, instead of 3.
I tried to get the a result that looked like below:

User_ID    User_No  User_Name   Nationality_Code    Date_From    Date_To
  00001    KJH789      John        JAP               2014-04-26   2014-04-29
  00004    TGI567      Sarah       SG                2014-04-26   2014-04-29
  00026    RTW763      Timmy       SG                2014-04-09   2014-04-12

Upvotes: 0

Views: 77

Answers (4)

Aparna
Aparna

Reputation: 286

Try the below query 

   with temp as 
   (
    select t.user_id,
    t.user_no,
    p.user_name,
    p.nationality_code, 
   max(Date_to) as Date_to
   from user_transfer t inner join user_profile p 
   on t.user_id=p.user_id
   group by t.user_id,t.user_no,p.user_name,p.nationality_code 
 )
select 
  a.user_id,a.user_no,p.user_name,p.nationality_code,a.date_from,p.date_to 
  from user_transfer a join temp p 
  on a.user_id=p.user_id
  and a.date_to=p.date_to;

Upvotes: 0

andreas karimi
andreas karimi

Reputation: 192

try it :

http://www.sqlfiddle.com/#!9/508ae6/27

select tmp1.User_ID,tmp1.User_No,User_Profile.User_Name,User_Profile.Nationality_Code ,
tmp1.Date_From,tmp1.Date_To
from 
(
    select * from User_Transfer U
    order by Date_From desc
) as tmp1,User_Profile
 where
 tmp1.User_ID=User_Profile.User_ID
 group by User_ID

Upvotes: 0

Eid Morsy
Eid Morsy

Reputation: 966

Don't Put The ID Column in the Group By thats why you get all rows, so try the below query

 SELECT X.USER_ID,
       X.USER_NO,
       EB.USER_NAME,
       EB.NATIONALITY_CODE,
       x.DATE_FROM,
       X.DATE_TO
 FROM
 (SELECT USER_ID,USER_NO,Max(DATE_From) AS DATE_From, MAX(DATE_TO) AS DATE_TO
    FROM USER_TRANSFER
    GROUP BY USER_ID,USER_NO) X
 left join USER_ID EB on x.USER_ID=EB.USER_ID

 ORDER BY X.USER_NO

Upvotes: 1

Tomato32
Tomato32

Reputation: 2245

You can refer my solution:

SELECT     Uf.USER_ID,
           Uf.USER_NO,
           Upro.USER_NAME,
           Upro.NATIONALITY_CODE,
           Uf.DATE_FROM,
           T1.DATE_TO
   FROM User_Transfer as Uf
JOIN (Select USER_ID,
       USER_NO,
       MAX(Date_to) AS Date_To
       FROM User_Transfer 
       GROUP BY USER_ID, USER_NO) as T1 --get all user_id and user_no that have max date_to
ON T1.USER_ID = Uf.USER_ID AND T1.USER_NO = Uf.USER_NO 
                           AND T1.Date_To = Uf.Date_To
JOIN User_Profile AS Upro
ON Upro.USER_ID = Uf.USER_ID

Upvotes: 0

Related Questions