Get a maximum - 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)

Get a maximum

Author

Topic

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-08-14 : 08:52:17

Table name: Tbl_latLang
Sno name latitude langtitude time
1 kamal 22.23423 44.24242 10:30 AM
2 Dinesh 32.23232 32.32321 11:30 AM
3 kamal 45.32424 45.23232 11:45 AM
4 Dinesh 22.22323 22.32323 2:30 PM

I need a query to select the name, lat and lang values from the Tbl_latlang table where time is maximum.

Result will be

kamal 45.32424 45.23232 -----> 11:45 AM

Dinesh 22.22323 22.32323 ----> 2:30 PM

kamal..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-14 : 08:55:59

[code]
select *
from
(
select *, row_no = row_number() over (partition by name order by time desc)
from Tbl_latLang
) d
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 08:57:48

[code]SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY time DESC) AS RowNo,*
FROM Tbl_latLang
)t
WHERE t.RowNo=1[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 08:58:40

ah ..you posted...didnt refresh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-14 : 09:00:20

KH
[spoiler]Time is always against us[/spoiler]

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-08-14 : 09:05:54

I can't understood your query..
Kindly explain.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-14 : 09:12:58

did you try to run it and take a look at the result ?

row_number() returns a running number per record.
partition by name will re-start the running number for each name
order by defines the ordering of the running number

KH
[spoiler]Time is always against us[/spoiler]

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-08-14 : 09:16:32

I run that query, but I got the below error.

"Msg 195, Level 15, State 10, Line 4
'row_number' is not a recognized function name."

Kamal

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-14 : 09:20:09

are you using SQL Server 2005 or with compatibility level is not set at 90 ?

KH
[spoiler]Time is always against us[/spoiler]

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-08-14 : 09:31:51

Yes, I am using Sql server 2005

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 09:35:02

Run this

EXEC sp_dbcmptlevel 'your_db',90

Madhivanan

Failing to plan is Planning to fail

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-08-14 : 09:37:30

I run the above command but i got the below error.

Msg 15010, Level 16, State 1, Procedure sp_dbcmptlevel, Line 54
The database 'your_db' does not exist. Use sp_helpdb to show available databases.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 09:40:04

...'your_db' as in put the name of YOUR DB in there instead

Em

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-08-14 : 09:42:07

Sorry. I gave my DB name instead of Your_DB, but I got the below error message,

Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

Valid values of database compatibility level are 60, 65, 70, or 80.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 09:46:01

then you are using SQL 2000, not 2005

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 09:48:00

select * from Tbl_latLang as t
where time=(select max(time) from Tbl_latLang where name=t.name)

Madhivanan

Failing to plan is Planning to fail

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-08-14 : 09:50:36

Thanks Madhivanan, exactly correct what i want..