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]

Go to Top of Page

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
Go to Top of Page

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]

Go to Top of Page

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
Go to Top of Page

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]

Go to Top of Page

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]

Go to Top of Page
   

- Advertisement -