distinct and max - 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)

distinct and max

Author

Topic

gtrain
Starting Member

7 Posts

Posted - 2008-12-27 : 00:51:12

hi guys just wondering if i might be able get some help on this one. I should know, but my brainisn't firing on all cylinders in the "festive season".

Index uName Duration
2000 jill 35000
2000 bill 5000
2000 phil 3200
2001 jeff 30000
2001 gregg 13200
2002 steve 43200

I would like to get the "uName" based upon distinct(Index) and max(Duration)
so someting like the table below will be returned.

Index uName Duration
2000 jill 35000
2001 jeff 30000
2002 steve 43200

I have tried a coule of cases unsuccessfully.
quick thoughts?

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-27 : 01:39:02

select * from
( select *,row_number() over(partition by index order by index ) as 'rn' from urtable ) p
where rn = 1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-27 : 01:40:06

select t.index,uname,t.duration from urtable u inner join (select index,max(duration) as duration from urtable group by index) as t on t.index = u.index

gtrain
Starting Member

7 Posts

Posted - 2008-12-27 : 01:59:36

quote:
Originally posted by bklr

select t.index,uname,t.duration from urtable u inner join (select index,max(duration) as duration from urtable group by index) as t on t.index = u.index

Thanks guys

I just had to mod the above to

select t.index,uname,t.duration from urtable u inner join (select index,max(duration) as duration from urtable group by index) as t on (t.index = u.index and t.duration = u.duration)

Thanks again

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-27 : 02:03:26

ur welcome

I forgot to join on duration also

select t.index,uname,t.duration from urtable u inner join (select index,max(duration) as duration from urtable group by index) as t on (t.index = u.index and t.duration = u.duration) order by t.index
to get ur o/p as u required

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-27 : 12:08:30

quote:
Originally posted by Nageswar9

select * from
( select *,row_number() over(partition by index order by index ) as 'rn' from urtable ) p
where rn = 1

it should be


select * from 
( select *,row_number() over(partition by index order by Duration desc ) as 'rn' from urtable ) p
where rn = 1

gtrain
Starting Member

7 Posts

Posted - 2008-12-27 : 18:37:00

visakh16 is row_number() explicit to mssql2005, because i am getting an exception.

thanks for your input guys have a good 2009!

G

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-12-27 : 20:27:19

ROW_NUMBER is a new function is SQL 2005, bklr's solution should work for you.

Jim

gtrain
Starting Member

7 Posts

Posted - 2008-12-28 : 02:08:04

Yeh thought as much Jim. I just wanted to try both scenario's

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-28 : 22:58:38

SELECT b.* FROM
(SELECT Index as ind,MAX(Duration) as Dmax FROM urtable GROUP BY Index) a
CROSS APPLY(SELECT * FROM urtable WHERE Index = a.ind AND duration = a.dmax) b

Jai Krishna

jbp_j
Starting Member

24 Posts

Posted - 2008-12-28 : 23:28:44

hi,

Try this one also.

declare @tab table (Index1 int,uName varchar(50),Duration int)
insert into @tab
select 2000 ,'jill',35000 union all
select 2000,'bill',5000 union all
select 2000, 'phil', 3200 union all
select 2001,'jeff', 30000 union all
select 2001,'gregg',13200 union all
select 2002,'steve',43200

select * from ( select row_number() over (partition by index1 order by duration desc) as row,uname,index1 from @tab ) as d
where row = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 00:42:08

quote:
Originally posted by jbp_j

hi,

Try this one also.

declare @tab table (Index1 int,uName varchar(50),Duration int)
insert into @tab
select 2000 ,'jill',35000 union all
select 2000,'bill',5000 union all
select 2000, 'phil', 3200 union all
select 2001,'jeff', 30000 union all
select 2001,'gregg',13200 union all
select 2002,'steve',43200

select * from ( select row_number() over (partition by index1 order by duration desc) as row,uname,index1 from @tab ) as d
where row = 1

how do you think is this different from what i posted? dont repeat what others have suggested earlier.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 00:43:19

quote:
Originally posted by gtrain

visakh16 is row_number() explicit to mssql2005, because i am getting an exception.

thanks for your input guys have a good 2009!

G

yup row_number() is available only from sql 2005 onwards.For using it, you should be having compatibility level of 90 for your database.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2008-12-29 : 01:54:02

if your database is at compatibility level 80, but is running on a sql 2005 server, you could use some sql 2005 features (row_number among them), but you would not be able to use some others (pivot, for example).

if your database is running on a sql 2000 server, you will not be able to use row_number function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 04:15:25

quote:
Originally posted by sunitabeck

if your database is at compatibility level 80, but is running on a sql 2005 server, you could use some sql 2005 features (row_number among them), but you would not be able to use some others (pivot, for example).

if your database is running on a sql 2000 server, you will not be able to use row_number function.

Thats not true sunita. you can use new features of 2005 like ROW_NUMBER(),PIVOT,... only if compatibility level is 90. If you're using compatibility level 80, you will be able to use only those features which were present till sql 2000

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2008-12-29 : 08:25:03

quote:
Originally posted by visakh16

quote:
Originally posted by sunitabeck

if your database is at compatibility level 80, but is running on a sql 2005 server, you could use some sql 2005 features (row_number among them), but you would not be able to use some others (pivot, for example).

if your database is running on a sql 2000 server, you will not be able to use row_number function.

Thats not true sunita. you can use new features of 2005 like ROW_NUMBER(),PIVOT,... only if compatibility level is 90. If you're using compatibility level 80, you will be able to use only those features which were present till sql 2000

This has not been my experience, Visakh. At my last job, we had a SQL 2000 server that was upgraded to SQL 2005, but the database compatibility level for the main production database on it had to be left at 80 because the database vendor did not certify for level 90. I was using row_number function on that database.

In the following example, I am creating a database at level 90 and then downgrading it to level 80, and I am able to use row_number function on the downgraded database. The server version is SQL 2005 SP2.

-------
use master
go
create database TestDB
go
use TestDB
go
create table Table1 (col1 float)
go
insert into Table1 select Rand(cast(cast(newid() as varbinary) as int))
go 5
exec sp_dbcmptlevel 'TestDB', 80
go
exec sp_dbcmptlevel 'TestDB'
go
select row_number() over (order by col1) as row, col1 from Table1

-------
The current compatibility level is 80.
--------
row col1
1 0.0050403714622495
2 0.400580775666555
3 0.565804685986641
4 0.659780110054987
5 0.916506827178242

Sunita

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources