How to determine max value in where clause

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)

How to determine max value in where clause

Author

Topic

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-02 : 12:07:32

So I have this seemingly simple problem which I've been pondering over.

I have a table with an identity column as the PK and two other varchar fields. I'm trying to select the two varchar fields with the largest ID (identity field) without actually selecting the ID itself. Can this be done?

I can write
SELECT TOP 1 ID, Name, Type
FROM TABLE
ORDER BY ID DESC

but that selects the ID which I don't want. Is there any way to specify max ID in the where clause? I'd love to be able to say something like:

SELECT Name, Type
FROM TABLE
WHERE ID is max()

I bet there's a really simple solution which I can't think of.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-02 : 12:13:41

dont select ID in that case

SELECT TOP 1 Name, Type
FROM TABLE
ORDER BY ID DESC

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-02 : 12:13:56

I'm sorry for wasting anyone's time on this, but as soon as I posted this I ended up figuring out the answer:

SELECT Name, Type
FROM TABLE
WHERE ID = (SELECT max(ID) FROM TABLE)

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-02 : 12:16:39

quote:
Originally posted by rohitkumar

dont select ID in that case

SELECT TOP 1 Name, Type
FROM TABLE
ORDER BY ID DESC

Thank you, your way works also.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-02 : 12:17:19

thats ok it just helps us increase our post count :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 12:19:05

quote:
Originally posted by Skorch

I'm sorry for wasting anyone's time on this, but as soon as I posted this I ended up figuring out the answer:

SELECT Name, Type
FROM TABLE
WHERE ID = (SELECT max(ID) FROM TABLE)

Although this way works as you've indicated, it is less efficient than the other one posted as your solution requires it to hit the table twice. This could become a performance problem if your table is big.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-02 : 12:37:36

quote:
Originally posted by tkizer

quote:
Originally posted by Skorch

I'm sorry for wasting anyone's time on this, but as soon as I posted this I ended up figuring out the answer:

SELECT Name, Type
FROM TABLE
WHERE ID = (SELECT max(ID) FROM TABLE)

Although this way works as you've indicated, it is less efficient than the other one posted as your solution requires it to hit the table twice. This could become a performance problem if your table is big.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

I agree with what you said.
But can you help me explain this please. I have a table with 50 million records.
I checked the plan for this :-

select top 1 * from tablename order by columnname desc

select top 1 * from tablename where columnname =(select max(columnname ) from tablename)

The query cost relative to batch for the 1st and 2nd query is 98.78 and 1.22 respectively.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 12:42:23

What indexes do you have on the table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-02 : 12:49:11

Am sorry. I should have mentioned this. The column I am using in the query doesn't have any associated indices. There are clustered and non clustered indices associated with other columns though.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-02 : 12:59:45

Well, in my most experience, Top always does scan rather than using index. Your 2nd query must evaluate Top twice and bookmark or keylookup as it has to fetch from clustered index since you don't have index for that column. Can you tell us difference in execution plan and exact steps?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-02 : 13:13:42

Its using indexes strangely. Didn't know how to post an image here.
Hope this helps.

select top 1 * from fct_bookings order by zrrbegdt desc

  |--Top(1)
       |--Sort(TOP 1, ORDER BY:([Fct_Bookings].[ZRRBEGDT] DESC))
            |--Clustered Index Scan(OBJECT:([Bookings].[dbo].[Fct_Bookings].[IX_Fct_Bookings]))

select top 1 * from fct_bookings where zrrbegdt=(select max(zrrbegdt) from fct_bookings)

  |--Top(1)
       |--Nested Loops(Inner Join, WHERE:([Expr1002]=[Fct_Bookings].[ZRRBEGDT]))
            |--Stream Aggregate(DEFINE:([Expr1002]=MAX([partialagg1006])))
            |    |--Stream Aggregate(DEFINE:([partialagg1006]=MAX([Fct_Bookings].[ZRRBEGDT])))
            |         |--Clustered Index Scan(OBJECT:([Bookings].[dbo].[Fct_Bookings].[IX_Fct_Bookings]))
            |--Clustered Index Scan(OBJECT:([Bookings].[dbo].[Fct_Bookings].[IX_Fct_Bookings]))

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-02 : 13:26:07

Its rather doing clustered scan rather than using index. Which performs better with SET STATISTICS TIME ON?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-02 : 13:32:52

I haven't tested that yet as it'll take long to execute. The table has 50 million rows.
I just wanted to know why the plan would say that the query cost relative to batch for the 1st and 2nd query is 98.78 and 1.22 respectively.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-02 : 13:40:33

You must have executed both queries in same batch. It adds up to 100%. It must have generated more costs while creating plan for Ist query while cost was less for 2nd query (Almost similar plan).

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-02 : 13:49:17

Yes, I did execute both in the same batch. But why would it say that query cost of the first is as high as the value I posted before when clearly the same query has just one scan compared to the other.
Also, Here are the timings of the 2 queries. The query with 2 scans took lesser time.

select top 1 * from fct_bookings order by zrrbegdt desc
   CPU time = 455781 ms,  elapsed time = 475995 ms.
select top 1 * from fct_bookings where zrrbegdt=(select max(zrrbegdt) from fct_bookings)
   CPU time = 47375 ms,  elapsed time =  210509 ms.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-02 : 14:11:32

Update your statistics, then run these two:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Now run the batch with statistics turned on.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources