SQL Server

[Ali Rashid]

Hi Guys, I tries to look around but I could not find an answer for this question. I need to run an SQl Query which retuns me the highest Unique Value and the lowest Unique value from the same column. For example. Lets say the column name is itemPrice, and has the following values: 10, 10, 11, 12, 12, 13, 14, 14 respectively. How can I get the 11 and 13 as the result of my query. I have tried DISTINCT, MIN, MAX and HAVING but to no avail. I am using SQL Server 2000. If anyone can help, I would really be grateful. Thanking in Anticiption. Regards Ali

[Syed Mehroz Alam]

Look at my solution below. I have created a temp table to depict your scenario. The query in bold is the one you need. Explanation: I first selected unique values from the table using group by clause, then I just picked the min/max ones

Create table #MyTable
(
ItemPrice int
)

insert into #MyTable values(10)
insert into #MyTable values(10)
insert into #MyTable values(11)
insert into #MyTable values(12)
insert into #MyTable values(12)
insert into #MyTable values(13)
insert into #MyTable values(14)
insert into #MyTable values(14)

select * from #MyTable

Select min(ItemPrice) as MinUniquePrice, max(ItemPrice) as MaxUniquePrice
From
( select ItemPrice, Count(*) as ItemPriceCount
from #Mytable
group by ItemPrice
Having Count(*) = 1
) as MyTableWithUniqueItemPrices

drop table #MyTable

Happy querying, Mehroz

modified on Sunday, March 30, 2008 5:18 AM

[Blue_Boy]

select itemPrice from Table1 where (select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1

---

I Love SQL

[Ali Rashid]

Thanks Guys! That was really helpfull. Regards.