how to use group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to use group by

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-03-07 : 20:11:53

Hello Everyone,

I have to list Field1, Field2 and Field3 values with group by Field1 and Field2 value is latest date.

how do i query to get below result.

declare @tbl1 table
(Field1 int,
Field2 datetime,
Field3 varchar(20)
)

INSERT INTO @tbl1
SELECT 101,'6/21/2010 17:38' ,'Abc'

INSERT INTO @tbl1
SELECT 101,'6/21/2010 17:58' ,'Xyz'

INSERT INTO @tbl1
SELECT 101,'6/21/2010 18:01' ,'Mnop'

INSERT INTO @tbl1
SELECT 102,'6/23/2010 20:10' ,'Xyz'

INSERT INTO @tbl1
SELECT 102,'1/18/2011 13:10' ,'Abc'

INSERT INTO @tbl1
SELECT 103,'9/20/2010 11:05' ,'Abc'

INSERT INTO @tbl1
SELECT 103,'10/23/2010 01:22' ,'Abc'

select * from @tbl1

Output looks like this:

101 2010-06-21 18:01:00.000 Mnop
102 2011-01-18 13:10:00.000 Abc
103 2010-10-23 01:22:00.000 Abc

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-03-07 : 22:51:39

Try this:


SELECT T.Field1, T.Field2, T.Field3
  FROM (SELECT T.Field1, T.Field2, T.Field3,
               ROW_NUMBER() OVER(PARTITION BY T.Field1 ORDER BY T.Field2 DESC) AS row_num
          FROM @tbl1 AS T) AS T
 WHERE T.row_num = 1

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-03-07 : 22:57:40

perfect thanks malpashaa

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-03-07 : 23:10:45

Welcome

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-03-08 : 12:14:00

I would like to one more criteria where Field3 like '%X%' but don't get record.

SELECT T.Field1, T.Field2, T.Field3
FROM (SELECT T.Field1, T.Field2, T.Field3,
ROW_NUMBER() OVER(PARTITION BY T.Field1 ORDER BY T.Field2 DESC) AS row_num
FROM @tbl1 AS T) AS T
WHERE T.row_num = 1 AND T.Field3 like '%X%'

Sachin.Nand

2937 Posts

Posted - 2011-03-08 : 12:17:19

Put the where condition for '%X%' in the inner query.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources