Select Top of each rows - 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)

Select Top of each rows

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-21 : 11:43:26

[code]CREATE TABLE #Tempfun(
id1 varchar(10),
id2 varchar(10),
id3 varchar(10))
INSERT INTO #Tempfun(id1, id2, id3)
SELECT N'1 ', N'1 ', N'A ' UNION ALL
SELECT N'2 ', N'2 ', N'B ' UNION ALL
SELECT N'3 ', N'2 ', N'C ' UNION ALL
SELECT N'4 ', N'3 ', N'D ' UNION ALL
SELECT N'5 ', N'3 ', N'E ' UNION ALL
SELECT N'6 ', N'3 ', N'F ' UNION ALL
SELECT N'7 ', N'1 ', N'G ' UNION ALL
SELECT N'8 ', N'1 ', N'H ' UNION ALL
SELECT N'9 ', N'1 ', N'I '[/code]
hi, how to get the following result(group by id2)
result
9, 1, I
3, 2, c
6, 3, f

Hope can help...but advise to wait pros with confirmation...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-21 : 11:46:34

Not sure if I covered everything..but this seems to work

SELECT   Max(id1), 
         id2, 
         Max(id3) 
FROM     #tempfun 
GROUP BY id2

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-21 : 11:49:31

hmm...is it anyway to do it without using max or top?

Hope can help...but advise to wait pros with confirmation...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-21 : 11:53:34

Row Number?

SELECT id1, 
       id2, 
       id3 
FROM   (SELECT Row_number() 
                 OVER(PARTITION BY id2 ORDER BY id1 DESC) AS seq, 
               id1, 
               id2, 
               id3 
        FROM   #tempfun) t 
WHERE  t.seq = 1

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-21 : 11:55:09

oh ya....i forgotten 'DESC' this keyword...no wonder i stuck at high numbers...lol

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-21 : 11:55:22

thx alot vijayisonly ^^

Hope can help...but advise to wait pros with confirmation...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-21 : 11:56:17

welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-22 : 03:25:40

quote:
Originally posted by waterduck

oh ya....i forgotten 'DESC' this keyword...no wonder i stuck at high numbers...lol

Hope can help...but advise to wait pros with confirmation...

See how you can use row_number() function for various purposes
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources