Is it possible to use where is Max like 2005?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » General » Is it possible to use where is Max like 2005?...

Is it possible to use where is Max like 2005?

Rate Topic

Display Mode

Topic Options

Author

Message

emre.celik00

Posted Tuesday, January 10, 2012 1:30 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 2:47 PM
Points: 3, Visits: 3

Hi,
Is there any way to find Max value ?
Select xyz from table1 where xyz is max.
I know that I can use select max(xyz) from table1 but I need first one.

Post #1233502

LutzM

Posted Tuesday, January 10, 2012 1:42 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 10:30 AM
Points: 6,712, Visits: 12,042

Something like this?

SELECT TOP 1  xyz  FROM table1 ORDER BY xyz DESC

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #1233510

emre.celik00

Posted Tuesday, January 10, 2012 1:52 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 2:47 PM
Points: 3, Visits: 3

Thanks for your reply.
I need to use with join.
Can I find Max value in Where clause?
or
like this
Left outer join table1 t on t.xyz= ........ and t.xyz is max or where t.xyz is max

Post #1233513

LutzM

Posted Tuesday, January 10, 2012 2:06 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 10:30 AM
Points: 6,712, Visits: 12,042

Please take the time to read and follow the advice given in the first article referenced in my signature and provide some ready to use sample data.

Furthermore I'd like to know if you're really still using SQL 2000 as the forum you posted in indicates. It'll make a big difference on the possible solutions.

Post #1233526

OzYbOi d(-_-)b

Posted Tuesday, January 10, 2012 2:09 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 1,062, Visits: 693

Thanks LutzM :)

Almost sounds to me like you are trying to apply a WHERE condition instead of a HAVING condition... If you are looking to filter based on the aggregate, you need to use HAVING

http://msdn.microsoft.com/en-us/library/ms180199.aspx

Post #1233528

emre.celik00

Posted Tuesday, January 10, 2012 2:25 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 2:47 PM
Points: 3, Visits: 3

Lutzm thanks for your suggestion. I will read.
And The factory which I'm working using Sql 2000 so I'm using Sql Server 2000 :)

@OzYbOi d(-_-)b
Thanks for your reply I will check.

Post #1233535

Sean Lange

Posted Tuesday, January 10, 2012 2:46 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 4,742, Visits: 4,918

emre.celik00 (1/10/2012)

Thanks for your reply.
I need to use with join.
Can I find Max value in Where clause?
or
like this
Left outer join table1 t on t.xyz= ........ and t.xyz is max or where t.xyz is max

Pretty sure that what Lutz posted is what you need. Just create your select statement with a join. Then get the top 1 order by xyz desc.

select top 1 xyz
from table1
join table2 on table1.key = table2.key
order by xyz desc

Bingo, you have the "max".

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Post #1233566

« Prev Topic | Next Topic »

Permissions