SQL Query

[Ali Rashid]

Hi Guys! I am using the following Statements: 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 which returns me the unique value in the table. The only problem is, how do I also get the itemID without the SQL Server listing all the values in the Table. My Original Question is pasted below for reference. Thanks once again to all the guys who had helped me earlier. Ali. ----------------------------------------------------------------------------------------------- ORIGINAL QUESTION: ================= I tried 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.

[Blue_Boy]

did you check my answer in your original posted message? Hope it helps.

---

I Love SQL

[Ali Rashid]

Hey Blue Boy, Thanks for the reply. I did and it returns the min and max value properly. But I am not getting the ID for the item. Thanks for the help.

[Ali Rashid]

Hey Blue Boy, Sorry, wrote it the wrong way, it returns the id if I add it but it did not return the unique values I was looking for. The query given by Syed returns the values I want but it does not return the ID for the product. Thanks.

[Blue_Boy]

tell me the columns of the table. Does your table have columns like this: ID itemPrice ----------------------- 1 | 10 2 | 10 3 | 11 4 | 12 5 | 12 6 | 13 7 | 14 8 | 14

---

I Love SQL

[Ali Rashid]

Hey Blue Boy! :-D Thanks for the help mate. Yes the columns and the values that you are displaying are correct. But from the resultset, what I need is ID: 3 and value 11 as Min(itemPrice), and ID: 6 and value 13 as Max(itemPrice). What I need is the lowest unique or distinct (whatever you would like to call it) and the maximum value which does not have similar values (e.g 11 and 13 have only one entry where as others have more than one). The other query that I mentioned in the last post returns me the values 11 and 13 but it is not returning the ID's (3 and 6) respectively. I hope I make some sense now. Sorry if I failed to explain it properly earlier. Thanks and kind regards, Ali

[mushfiqcs]

-- Solution 1 -- Say There are products in a table name products -- with a column named proPrice. -- Now insert your data 10, 10, 11, 12, 12, 13, 14, 14 -- Now run the scripts -- Keep practicing. Take care mushfiqcs@yahoo.com select proPrice, count(proPrice) from products group by proPrice Having count(proPrice)<2 S M Mushfiqur Rahman

[mushfiqcs]

-- Solution 1 -- Say There are products in a table name products -- with a column named proPrice. -- Now insert your data 10, 10, 11, 12, 12, 13, 14, 14 -- Now run the scripts -- Keep practicing. Take care mushfiqcs@yahoo.com select proPrice, count(proPrice) from products group by proPrice Having count(proPrice)<2 S M Mushfiqur Rahman

[Blue_Boy]

I guess you are looking for this select id,itemprice from Table1 where (select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1 and id in( (select top 1 id from Table1 where (select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1 order by id asc ) , (select top 1 id from Table1 where (select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1 order by id desc))

---

I Love SQL

[Syed Mehroz Alam]

Since we are sure that our Min/Max prices will be unique so we can write a subquery for retrieving the IDs, here's an addition to my original query.

Select MinUniquePrice, ( Select Top(1) ID from #MyTable T where T.ItemPrice=MinUniquePrice ) as MinPriceID,
MaxUniquePrice, ( Select Top(1) ID from #MyTable T where T.ItemPrice=MaxUniquePrice ) as MaxPriceID
From
(
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
) As MyTableWithMinMaxItemPrices

But Blue_boy's approach was better. I really liked it. :-D Regards, Mehroz

My Articles

modified on Wednesday, April 2, 2008 3:55 PM

[Blue_Boy]

thanks dude ;)

---

I Love SQL