SQL Query - 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

SQL Query

Author

Topic

nrajeshkumars
Starting Member

5 Posts

Posted - 2014-03-05 : 23:58:19

ItemID ItemName Price
1 Item1 120.00
2 Item2 234.00
3 Item3 250.00
4 Item4 300.00
5 Item5 300.00
6 Item6 290.00
7 Item7 170.00
8 Item8 90.00
9 Item9 170.00

the above is the existing table
and i need a query to retrieve max price with out using max function and sub query

N.Rajesh kumar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-06 : 00:00:52

homework ?

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

nrajeshkumars
Starting Member

5 Posts

Posted - 2014-03-06 : 00:04:56

YES..

quote:
Originally posted by khtan

homework ?

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

N.Rajesh kumar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-06 : 00:05:56

so can you use the TOP keyword ?

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

nrajeshkumars
Starting Member

5 Posts

Posted - 2014-03-06 : 00:09:59

we can use but what if we have multiple records with same price?
we should not do hard code.

N.Rajesh kumar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-06 : 01:23:13

use get the top price in derived table and JOIN to your main table


select *
from   yourtable t
       inner join
       (
           select top 1 price from yourtable order by price desc
       ) p on t.price = p.price

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-06 : 01:25:37

alternative method. use dense_rank() http://technet.microsoft.com/en-us/library/ms173825.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources