SQL basics question - 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 2008 Forums

Transact-SQL (2008)

SQL basics question

Author

Topic

Alex2011
Starting Member

18 Posts

Posted - 2011-04-27 : 12:48:52

HI, I am a beginner of SQL and wanted to get some help.

select col1, max(col3) as maxcol3
from table
group by col1

col1 col2 col3
a a-3 20
a a-0 80
b b-9 100
b b-3 200

I want to add col2 and its value should be from the row where max(col3) exists. so the return for above would be

col1 col2 col3
a a-0 80
b b-3 200

how to change the SQL statements above to achieve this.

Appreciate any help.

Alex

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-27 : 12:58:48

;WITH a(col1, col2, col3, rn) AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col3 DESC) FROM table)
SELECT * FROM a WHERE rn=1

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-27 : 13:02:25

Something like this??

DOH!


Declare @t table (
	col01 varchar(10),
	col02 varchar(10),
	col03 int
)

Insert Into @t Values 
('a','a-3',20),
('a','a-0',80),
('b','b-9',100),
('b','b-3',200)


;with cte(col01, col02, col03, r) 
As (
	Select 
		col01,
		col02,
		col03,
		r = Row_Number() Over(Partition By col01 Order By col03 desc) 
	From @t
)

Select * 
From cte
Where r = 1

Corey

I Has Returned!!

Alex2011
Starting Member

18 Posts

Posted - 2011-04-27 : 13:27:26

Thanks for your replies.

will it wrok in SQL server 2000?

quote:

Originally posted by Seventhnight

Something like this??

DOH!


Declare @t table (
	col01 varchar(10),
	col02 varchar(10),
	col03 int
)

Insert Into @t Values 
('a','a-3',20),
('a','a-0',80),
('b','b-9',100),
('b','b-3',200)


;with cte(col01, col02, col03, r) 
As (
	Select 
		col01,
		col02,
		col03,
		r = Row_Number() Over(Partition By col01 Order By col03 desc) 
	From @t
)

Select * 
From cte
Where r = 1

Corey

I Has Returned!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-27 : 13:39:02

No, CTEs and ROW_NUMBER() was introduced in SQL 2005. Please post SQL 2000 questions in the 2000 forums.

SELECT a.* FROM table a
INNER JOIN (SELECT col1, MAX(col3) col3 FROM table GROUP BY col1) b
ON a.col1=b.col1 AND a.col3=b.col3

Alex2011
Starting Member

18 Posts

Posted - 2011-04-27 : 14:03:25

I just tested it - it's close but duplicates were produced because a.col1=b.col1 AND a.col3=b.col3 won't create an unique match.

quote:
Originally posted by robvolk

No, CTEs and ROW_NUMBER() was introduced in SQL 2005. Please post SQL 2000 questions in the 2000 forums.

SELECT a.* FROM table a
INNER JOIN (SELECT col1, MAX(col3) col3 FROM table GROUP BY col1) b
ON a.col1=b.col1 AND a.col3=b.col3

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-27 : 14:06:49

Which "duplicate" should be selected then? Are these the only columns in the table?

Alex2011
Starting Member

18 Posts

Posted - 2011-04-27 : 14:19:13

Thanks a lot, robvolk.

these are only columns but there are more rows like below.

col1 col2 col3
a a-3 20
a a-0 80
a a-4 80
b b-9 100
b b-2 200
b b-3 200

quote:
Originally posted by robvolk

Which "duplicate" should be selected then? Are these the only columns in the table?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-27 : 14:33:35

My question was, for the a-0 and a-4 lines, which value should be returned? Could I use MAX() or MIN() on col2?

Alex2011
Starting Member

18 Posts

Posted - 2011-04-27 : 14:46:10

I guess it doesn't matter which value should return for col2 as long as it's with max(col3)

thank again for your help - your sql should work fine.

quote:
Originally posted by robvolk

My question was, for the a-0 and a-4 lines, which value should be returned? Could I use MAX() or MIN() on col2?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-27 : 14:51:33

SELECT a.col1, max(a.col2) col2, a.col3 FROM table a
INNER JOIN (SELECT col1, MAX(col3) col3 FROM table GROUP BY col1) b
ON a.col1=b.col1 AND a.col3=b.col3
GROUP BY a.col1, a.col3

Alex2011
Starting Member

18 Posts

Posted - 2011-04-27 : 16:18:55

thanks again for your help.

quote:
Originally posted by robvolk

SELECT a.col1, max(a.col2) col2, a.col3 FROM table a
INNER JOIN (SELECT col1, MAX(col3) col3 FROM table GROUP BY col1) b
ON a.col1=b.col1 AND a.col3=b.col3
GROUP BY a.col1, a.col3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources