Retrieve record - 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

General SQL Server Forums

New to SQL Server Programming

Retrieve record

Author

Topic

jggtz
Starting Member

32 Posts

Posted - 2008-12-01 : 15:18:46

MS SQL Server 2005

How can I retrieve quickly the record (and all its fields) that has the highest number value in one of the fields , from a huge transaction table.
This field is an unique index non clustered and not an identity

Thanks!

JG

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-01 : 15:21:23

select * from yourtable where yournumber=(select max(yournumber) from yourtable)

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 00:44:09

[code]SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY yourfield DESC) AS Seq,*
FROM YourTable
)t
WHERE Seq=1[/code]

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-02 : 10:33:51

OR:
SELECT top 1 *
FROM [Table1]
order by Field desc

I tested all three methods on 1 million rows and they were all 3 as fast as each other.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 10:37:09

What did the execution plan say about all three methods?

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-02 : 11:28:07

Running three queries in 1 batch
Method 1 (..where cl=(select max(cl)..) Query cost relative to batch 3%
Method 2 (...SELECT ROW_NUMBER()..) Query cost relative to batch 94%
Method 3 (SELECT top 1 * ...) Query cost relative to batch 3%

But the execution time for each was 1 ms.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 11:34:33

ROW_NUMBER is costly, because the INDEX is stored ascending and the suggestion to use ROW_NUMBER need the value descending and that's why ROW_NUMBER takes longer time.
Now some optimization may have taken place since they all takes "1 ms" each.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 11:51:28

quote:
Originally posted by Peso

ROW_NUMBER is costly, because the INDEX is stored ascending and the suggestion to use ROW_NUMBER need the value descending and that's why ROW_NUMBER takes longer time.
Now some optimization may have taken place since they all takes "1 ms" each.

E 12°55'05.63"
N 56°04'39.26"

does that mean for requirements like this its better to use techniques without use of ROW_NUMBER() ?

jggtz
Starting Member

32 Posts

Posted - 2008-12-02 : 11:53:30

Thanks
I learned a little more

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 11:58:34

quote:
Originally posted by visakh16

does that mean for requirements like this its better to use techniques without use of ROW_NUMBER() ?

Not in general, but you have to consider present indexes for windowed functions.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 12:02:34

quote:
Originally posted by Peso

quote:
Originally posted by visakh16

does that mean for requirements like this its better to use techniques without use of ROW_NUMBER() ?

Not in general, but you have to consider present indexes for windowed functions.

E 12°55'05.63"
N 56°04'39.26"

Yup...that makes sense

Thanks Peso for clarification

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources