Click here to Skip to main content
15,038,345 members
Please Sign up or sign in to vote.
3.00/5 (3 votes)
See more:
i dont't know how to select row with max column value group by another column. I have T-SQL
SQL
CREATE PROC GET_USER AS 
BEGIN
SELECT  T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
        MAX(T.[Second Start Date]) AS [Max Second Start Date],
         T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM ( 
	SELECT A.USER_ID ,A.FULL_NAME ,A.COST_CENTER ,B.START_DATE ,D.START_DATE AS [Second Start Date] ,
	  D.PC_GRADE,C.TYPE_PERSON_NAME,E.TRANSACTION_NAME,F.DEPARTMENT_NAME ,G.BU_NAME,H.BRANCH_NAME,J.POSITION_NAME
          FROM      USERS A
                    INNER JOIN USER_PERSON B ON A.USER_ID = B.USER_ID
                    INNER JOIN TYPE_PERSON C ON C.TYPE_PERSON_ID = B.TYPE_PERSON_ID
                    INNER JOIN USER_TRANSACTION D ON D.USER_ID = A.USER_ID
                    INNER JOIN TRANSACTIONS E ON E.TRANSACTION_ID = D.TRANSACTION_ID
                    INNER JOIN DEPARTMENT F ON F.DEPARTMENT_ID = D.DEPARTMENT_ID
                    INNER JOIN BUS_UNIT G ON G.BU_ID = D.BU_ID
                    INNER JOIN BRANCH H ON H.BRANCH_ID = D.BRANCH_ID
                    INNER JOIN POSITION J ON J.POSITION_ID = D.POSITION_ID
          WHERE     A.FLAG = 'TRUE'
        ) AS T
GROUP BY  T.USER_ID ,
       T.PC_GRADE, T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
END

But data is loop
HTML
U0001	7/9/2014	5/10/2015	2B	Android	1234567890	Chuyên gia	Lên chức	HR	Agro Feed	Trại 1 CP VN HN	Acc Staff
U0001	9/9/2014	5/10/2015	2B	Android	1234567890	Thực tập	Lên chức	HR	Agro Feed	Trại 1 CP VN HN	Acc Staff
U0001	7/9/2014	4/2/2015	3B	Android	1234567890	Chuyên gia	Thử việc	HR	Agro Feed	Trại 1 CP VN HN	HRIS Staff
U0001	9/9/2014	4/2/2015	3B	Android	1234567890	Thực tập	Thử việc	HR	Agro Feed	Trại 1 CP VN HN	HRIS Staff
U0001	7/9/2014	3/26/2015	4B	Android	1234567890	Chuyên gia	Thử việc	HR	Agro Feed	Trại 1 CP VN HN	HRIS Staff
U0001	9/9/2014	3/26/2015	4B	Android	1234567890	Thực tập	Thử việc	HR	Agro Feed	Trại 1 CP VN HN	HRIS Staff
U0001	7/9/2014	1/27/2015	5B	Android	1234567890	Chuyên gia	Thử việc	HR	Agro Feed	Trại 1 CP VN HN	Acc Staff
U0001	9/9/2014	1/27/2015	5B	Android	1234567890	Thực tập	Thử việc	HR	Agro Feed	Trại 1 CP VN HN	Acc Staff
U0001	7/9/2014	1/10/2015	6B	Android	1234567890	Chuyên gia	Thử việc	HR	Agro Feed	Trại 1 CP VN HN	HRIS Staff
U0001	9/9/2014	1/10/2015	6B	Android	1234567890	Thực tập	Thử việc	HR	Agro Feed	Trại 1 CP VN HN	HRIS Staff
U0002	8/3/2014	1/13/2015	4C	IOS	1237894560	Chuyên gia	Thử việc	HR	Agro Feed	Trại 1 CP VN HN	Acc Staff
U0003	1/1/2014	5/5/2015	3A	Windows	1239998880	Thực tập	Thử việc	ACCOUNT	Agro Farm	Trại 2 CP VN Huế	HRIS Staff


I want to the result as
HTML
U0001	9/9/2014	5/10/2015	2B	Android	1234567890	Thực tập	Lên chức	HR	Agro Feed	Trại 1 CP VN HN	Acc Staff
U0002	8/3/2014	1/13/2015	4C	IOS	1237894560	Chuyên gia	Thử việc	HR	Agro Feed	Trại 1 CP VN HN	Acc Staff
U0003	1/1/2014	5/5/2015	3A	Windows	1239998880	Thực tập	Thử việc	ACCOUNT	Agro Farm	Trại 2 CP VN Huế	HRIS Staff
Posted
Updated 18-May-15 23:04pm
v2
Comments
OriginalGriff 19-May-15 4:40am
   
That's a lump of code - but it doesn't tell us what is wrong, and neither does "But data is loop".
So try showing an example of your table data, and what output you want together with the output you get - it may help us to understand what you are trying to achieve.
Use the "Improve question" widget to edit your question and provide better information.
Herman<T>.Instance 19-May-15 6:56am
   
Are you using MS SQL or another Database system?
Mathi Mani 21-May-15 15:38pm
   
You are getting one row each for U0002 and U0003. Is it because these IDs have only one record for each of them in the table?
Try splitting this query. That might help.
First get the ids of interest into some temp table. Then get the details for each ID from this temp table.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900