Post reply

Is it possible to use where is Max like 2005?

  • January 10, 2012 at 1:30 pm

    #143085

    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.

  • January 10, 2012 at 1:42 pm

    #1431395

    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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 10, 2012 at 1:52 pm

    #1431397

    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

  • January 10, 2012 at 2:06 pm

    #1431405

    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.


    Lutz
    A pessimist is an optimist with experience.

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

  • January 10, 2012 at 2:09 pm

    #1431407

    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

  • January 10, 2012 at 2:25 pm

    #1431411

    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.

  • January 10, 2012 at 2:46 pm

    #1431439

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply