DECLARE @TABLE1 TABLE
(
	COL1		int,
	COL2 		int,
	COL3 		int,
	DESC_COL1 	varchar(50)
)

INSERT INTO @TABLE1
SELECT	10013,	1,	570,	'LOOK'			UNION ALL
SELECT	10013,	2,	900,	'ABC'			UNION ALL
SELECT	10013,	3,	570,	'ASDFASDF'		UNION ALL
SELECT	10013,	4,	570,	'Manual entry will be'	UNION ALL
SELECT	10013,	5,	570,	'journal posted'	UNION ALL
SELECT	10013,	6,	570,	'Need to look '		UNION ALL
SELECT	10013,	7,	200,	'The'    		UNION ALL
SELECT	10034,	1,	570,	'The batch '		UNION ALL
SELECT	10034,	2,	105,	'We have been '		UNION ALL
SELECT  10034,	6,	570,	'The DBA team '		UNION ALL
SELECT  10034,	7,	170,	'We are working' 		UNION ALL
SELECT  10057,	1,	8615,	'adfa'		UNION ALL
SELECT  10057,	2,	1615,	'adabxcx'		UNION ALL
SELECT  10057,	3,	4255,	'axcaxfasd'		UNION ALL
SELECT  10057,	6,	1015,	'ccbvncvb'		UNION ALL
SELECT  10057,	7,	1000,	null
;
select COL1, max(COL2) as COL2, max(COL3) as COL3 from @TABLE1
group by COL1--, COL3
order by COL1--, COL3

COL1  COL2    COL3  DESC_COL1 
10013	7      200  'The'
10034	7      170  'We are working'
10057	7      1000  null

SELECT t.*
FROm YourTable t
INNER JOIN (select COL1,MAX(COL2) AS MaxCol
           FROm YourTable
           GROUP BY COL1) tmp
ON t.COL1 = tmp.Col1
AND t.COL2=tmp.MaxCol

SELECT t.COL1,  
t.COL2,    
t.COL3,  
t.DESC_COL1 
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2 DESC) AS RowNo,*
FROM YourTable)t
WHERE t.RowNo=1

quote:

---

Originally posted by zion99

thnx visakh16 for your quick reply. this has solved my problem.

But if possible, could you please let me know if there is any way of using group by and non-agg columns together

---

quote:

---

Originally posted by zion99


But if possible, could you please let me know if there is any way of using group by and non-agg columns together

---