|
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
|
|
|
|
|
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
|
|
|
|
|
Thanks Guys!
That was really helpfull.
Regards.
|
|
|
|
|
select itemPrice from Table1<br />
where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1
I Love SQL
|
|
|
|
|
Hi - I'm having trouble running a sql script with "GO" using DbCommand...
sql scripts looks like this:
<br />
declare EmpCur cursor for (<br />
select * from employee where ...<br />
)<br />
GO << This go leads to error when run by DbCommand (Although NO error when run on ASE isql -- against Sybase)<br />
... more processing ...<br />
GO<br />
Now, the code is like this:
DataSet oDataSet = new DataSet(strName);<br />
DbProviderFactory oDbProviderFactory = null;<br />
DbDataAdapter oAdapter = null;<br />
DbCommand oCmd = null;<br />
DbConnection oConn = null;<br />
<br />
oDbProviderFactory = CDataUtil.GetConnectionFactory(strProvider);<br />
oConn = oDbProviderFactory.CreateConnection();<br />
oConn.ConnectionString = strConnectionString;<br />
<br />
oCmd = oDbProviderFactory.CreateCommand();<br />
oCmd.CommandText = m_Cmd.RenderCommand(null); <br />
oCmd.Connection = oConn;<br />
<br />
oAdapter = oDbProviderFactory.CreateDataAdapter();<br />
oAdapter.SelectCommand = oCmd;<br />
<br />
oConn.Open();<br />
oAdapter.Fill(oDataSet, strName);
Error message is like this:
2008-03-28 17:53:20,875 [1] ERROR XXXComponent - SQL command failed.<br />
Message: ERROR [42000] [MERANT][ODBC Sybase driver][SQL Server]Incorrect<br />
syntax near 'go'.
devy
|
|
|
|
|
GO is not a SQL statement. Rather, it is, by convention, a separator which tells the script processor that this is the end of a batch: it should send what it's read so far to the database, wait for a response, then proceed with the next set of statements up to the next GO (or end of file).
You should split your script at GO statements.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Mike Dimmick wrote:
You should split your script at GO statements.
Thanks - multiple sql scripts? And multiple Cmd.Execute?.. hum
my scripts looks like this, not sure if I can really "split"...
<br />
declare EmpCur cursor for (select * from Emp where ...}<br />
go<br />
... processing ...<br />
fetch EmpCur into @empId ...<br />
while (@@sqlstatus=0)<br />
begin<br />
fetch EmpCur into @empId ...<br />
... some processing ...<br />
insert into #tmp (empId, salary) (@empId, @salary)<br />
end<br />
<br />
select * from #tmp<br />
go
As you can see, declare cursor and the rest of the script must be separated by GO.
devy
|
|
|
|
|
You don't need the GO statement after declaring the cursor. By doing this, your first statement gets compiled into its own batch and is executed independently from the rest of the code. You don't want to do this. The one GO statement at the end is sufficient.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
o I tested the sql script on ASE isql (Sybase version of SQL's "Query Analyzer ") - it inisists "Declare cursor" be in a separate batch (therefore the GO that comes after it).
With Cmd.Execute you don't need to GO?
Thanks!!
devy
|
|
|
|
|
Hi - I've tried, got the following error after removing GO after DECLARE CURSOR statement:
Message: ERROR [HY000] [MERANT][ODBC Sybase driver][SQL Server]DECLARE CURSOR must be the only statement in a query batch.
If I *need* cursor, what are my options? Can we have GO in stored proc? (wrap sql statement by stored proc?)
devy
|
|
|
|
|
Hi every body!
I have a data base to store a large number of records and I want an Unique ID for each of them, so I used an UniqueIdentifier field, but the problem is when I delete a record, as you know, the identifier is not reordered automatically, so what should I do?
by the way, as I told I have many records to save. How can I prevent data from overflowing?
thanks for any help.
|
|
|
|
|
Is it necessary for the IDs to be contiguous? They'll still be unique, even if a record is deleted.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
infact no!
but because there are many deletion operations in data base and there are a large number of records, so the ID value grows too fast to the maximum value it can accept.
|
|
|
|
|
You probably shouldn't be using an IDENTITY column then.
Have a look at this article[^] about created custom auto-generated sequences.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi
I wrote one stored procedure it’s returning two parameters. How to get the two output parameters retuned by SP .
|
|
|
|
|
Do a Google search for the OUTPUT keyword.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
If using a .NET language, set the parameter object's Direction property to ParameterDirection.Output (or, if you're passing values in through this parameter as well, ParameterDirection.InputOutput). The parameter will then be set to this value once all resultsets have been read to the end.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Hi,
I have a database correupted when altering a field in db.
while it was altering i've killed the enterprise manager, then database returned to suspect mode we turned the db to emergency mode then we execute,
dBCC CHECKALLOC (yourDBname, REPAIR_ALLOW_DATA_LOSS)
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
we get error that couldn't fix some corrupted pages
Anyone can help me, please
Thanks
Ahmet GULBAY
|
|
|
|
|
i have been trying to find out what is the difference between INNER JOIN and WHERE CLAUSE.
Any one with help please
Back off i am coding
|
|
|
|
|
Joins - clickety
Where - clickety
You always pass failure on the way to success.
|
|
|
|
|
No real difference. You can express your inner joins in the WHERE clause if you wish (I believe in the early versions of SQL you had to), but your code will be easier to understand if you separate join conditions from the other conditions.
|
|
|
|
|
Is there anything special I should consider in a security model for an ASP.NET web site with a dedicated database, possibly on a shared server? I want to use Windows Authentication, but not the individual users.
|
|
|
|
|
1) Setup a Windows Group and grant that group login priviledges to your SQL Server instance.
2) Configure SQL Server to grant that group the needed priviledges for your database.
3) Create a Windows login and make that user a member of the windows group in step #1
4) Configure your IIS AppPool identity to be the Windows login you created in step #3
If you are working in a windows domain the group and user should be created at the domain level so that you can eventually separate the web server and database server.
|
|
|
|
|
I have an identity column of type int in my table and I need to reseed it when it reaches certain value.
I'm thinking of a trigger on insert that would check last generated identity value and reseed it if needed.
At the moment I believe I should use SCOPE_IDENTITY() and DBCC CHECKIDENT (table, reseed, initial_value) in CASE statement. If this is the case I need some help in putting things together otherwise I need a better solution.
Any help will be very appreciated.
Thanks in advance.
modified 19-Nov-18 21:01pm.
|
|
|
|
|
Without knowing why you need to reseed the value, it's difficult to make recommendations.
However, a case statement wouldn't work because CHECKIDENT doesn't return a value. But if you used logic similar to the following it should do what you're asking:
<br />
DECLARE @ident INT<br />
<br />
SET @ident = SCOPE_IDENTITY()<br />
<br />
IF @ident >= {max value here} BEGIN<br />
DBCC CHECKIDENT({table name here}, reseed, {new seed value here})<br />
END<br />
<br />
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
