|
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.
|
|
|
|
|
did you check my answer in your original posted message?
Hope it helps.
I Love SQL
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
tell me the columns of the table.
Does your table have columns like this:
ID itemPrice <br />
-----------------------<br />
1 | 10<br />
2 | 10<br />
3 | 11<br />
4 | 12<br />
5 | 12<br />
6 | 13<br />
7 | 14<br />
8 | 14
I Love SQL
|
|
|
|
|
Hey Blue Boy! 
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
|
|
|
|
|
I guess you are looking for this
select id,itemprice from Table1 <br />
where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1<br />
<br />
and id in( (select top 1 id from Table1 where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1 order by id asc ) <br />
, (select top 1 id from Table1 <br />
where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1 order by id desc))
I Love SQL
|
|
|
|
|
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.
Regards,
Mehroz
modified on Wednesday, April 2, 2008 3:55 PM
|
|
|
|
|
thanks dude 
I Love SQL
|
|
|
|
|
-- 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
|
|
|
|
|
-- 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
|
|
|
|
|
Hello.
What I'd like to ask is if there is a way to configure SQL Server 2005 to use only a certain amount of RAM. I have a machine with 4GB RAM, and there are a lot of apps running on it. It is when SQL Server starts executing a query over a relatively large database (e.g. 20 - 30 GB)that things go bad for all other apps - SQL Server uses almost all of the available RAM, leaving only a very limited amount for all other applications to use.
Any suggestions?
|
|
|
|
|
Those are fairly large databases and depending on your queries you could be hitting the processor heavily - as you are discovering.
The only solution I can think of is to remove all those extra applications.
Good practise dictates that your SQL Server is a machine that is primarily a dedicated server i.e you have no other applications on it (other than an email client, backup application etc)
If you are using databases of the size you mention I suggest you get another machine on which to run the other applications.
Some more tips:
Keep your databases and transaction logs on separate disks.
Make sure that your applications are not on either of these disks.
I know this doesn't answer you question directly...
You always pass failure on the way to success.
|
|
|
|
|
You cannot explicitly set the amount of RAM that the system should use (as far as my little knowledge serves me). You can however, set the priority of the SQL Server to low as compared to other applications (Press ALT+CTRL+DEL > Click task Manager > Applications (TAB) > Select Image Name sqlmangr.exe > Right click on it and select option: set priority). That would mean that you would have more processing preference for other applications. But remember, with the size of the databases you are talking about, you would still have issues with the processor getting hit hard. If you cannot redice the DB size, then increase the RAM, much better get dual processors and set one processor to solely work on SQL Server. This is what a Hardware guy can set for you.
Regards.
|
|
|
|
|
Open up Management Studio, connect to your server. In Object Explorer, right click on the Server Root and select "Properties". Under the "Memory" page, enter a value in the "Maximum Server Memory" textbox.
The change will take effect when SQL Server is under lesser load and can shed some memory or when you restart the service.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
I am using the Query Builder in Visual Studio to fill my Dataset but one of the field/column names is unknown until runtime; therefore I wish to specify this on the fly.
My current query is as follows:
SELECT Cards.Id, Cards.Card, Cards.FirstName, Cards.LastName, Cards.AccessLevel, Cards.StartDate, Cards.EndDate, Cards.fTitle, ?,<br />
Photo.Id AS Expr1, Photo.Photo,<br />
AccLevel.AccessLevel AS Expr2, AccLevel.Name AS AccessName<br />
<br />
FROM (Cards<br />
LEFT JOIN Photo ON<br />
Cards.Card = Photo.Id)<br />
LEFT JOIN AccLevel ON<br />
Cards.AccessLevel = AccLevel.AccessLevel
The question mark represents a variable field name. Below is the code which fills the dataset by calling the query above - although for now, I have passed it a fixed string which represents one of the fields in the ‘Cards’ table and should replace the question mark in the query:
cards_Photo_AccessLevelTableAdapter.Fill(MyDataSet.Cards_Photo_AccessLevel, "Cards.fDepartment");
Although my code builds ok, an exception is caused when the Dataset is filled as above.
Any help would be much appreciated!!
Regards,
Christian
|
|
|
|
|
hi
i want my sql server database save the data like
12:12:12 PM..Database must contain only time value nt default date value.
currently it saved data like 1/1/1900 12:12:12 PM and i want it save as 12:12:12 PM
plz give me solution to do this.
|
|
|
|
|
A datetime field stores a whole date and time. You can't store just a time.
There are functions in T-SQL for extracting time-specific information from a datetime value.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hey,
Unfortunately you cannot save the dateTime column without date. However you can extract them using the convert function in SQl Server.
e.g:
select convert(varchar,DateColumn,108) from MyDateTest99
The following table gives you the details of which digit sequence to use:
0 or 100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120 yyyy-mm-dd hh:mi:ss(24h)
21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM
Hope that this helps...
Regards,
Ali
|
|
|
|
|
Hello
I wish to bound 3 columns to a combobox in a win application. Column names are A, B and C. I can bind column's items but i cannot bind the columns themselves. When i was click the combobox, choose one of the columns(A, B, C) and click to button, datagridview must show me the selected column's items. What's this code? Thanks..
|
|
|
|
|
hi there,
i m trying to retrieve data from one table with 2 condition in 2 queries, here is an example
select id, count(Number) from myTable where Number < 10 group by id order by id
select id, count(Number) from myTable where Number >= 77 and Number <=100 group by id order by id
the result is coming what expected but i want to run only single query for single result
for example
the result is coming :
QUERY 1
ID----(Column Name)
10----66
14----3
19----44
30----22
QUERY 2
ID----(Column Name)
30----45
40----74
55----43
60----44
what i want :
ID----(Column Name)----(Column Name)
10------66--------------0
14------3---------------0
19------44--------------0
30------22--------------45
40------0---------------74
55------0---------------43
60------0---------------44
i used join but didnt got it work
any idea?
thanks
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87??6?N8?BcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKSQXUFYK[M`UKs*$GwU#(QDXBER@CBN%
Rs0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i?TV.C\y<p?jxsg-b$f4ia>
--------------------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
|
Are you using Sql Server 2005?
Regards,
Rob Philpott.
|
|
|
|
|
yupp!!!
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87??6?N8?BcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKSQXUFYK[M`UKs*$GwU#(QDXBER@CBN%
Rs0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i?TV.C\y<p?jxsg-b$f4ia>
--------------------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
|
Cool.
There may be a better way to do this, but here's what I'd do. In the old world you could create a view to represent each query and then join them together, but you can use Common Table Expressions in 2005 (http://msdn2.microsoft.com/en-us/library/ms190766.aspx[^]) which are similar.
So, create the two queries as you have them with the with keyword. Then to combine the two results set you want a full outer join with an isnull to check for null ids resulting from the join and to set nulls to zeros on the count.
Does that make sense?
Regards,
Rob Philpott.
|
|
|
|
|
yeah, i think i have to pick my old books, i will try and thanks 
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87??6?N8?BcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKSQXUFYK[M`UKs*$GwU#(QDXBER@CBN%
Rs0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i?TV.C\y<p?jxsg-b$f4ia>
--------------------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
