Query To Find The Top 3 In Each Type
Mar 7, 2006
Hi
I need help in finding the query which will provide the following resultset from the below table..
Table :
create table product_stocks(product_id int , product_type varchar(20) , no_of_units int)
Data:
insert into product_stocks values(1,'A',30)
insert into product_stocks values(2,'A',70)
insert into product_stocks values(3,'A',60)
insert into product_stocks values(4,'A',40)
insert into product_stocks values(1,'B',90)
insert into product_stocks values(2,'B',60)
insert into product_stocks values(3,'B',70)
insert into product_stocks values(4,'B',40)
insert into product_stocks values(1,'C',40)
insert into product_stocks values(2,'C',50)
insert into product_stocks values(3,'C',80)
insert into product_stocks values(4,'C',90)
Result Set:
product_type product_id no_of_units
--------------- ------------- --------------
A 2 70
A 3 60
A 4 40
B 1 90
B 3 70
B 2 60
C 4 90
C 3 80
C 2 50
i.e The result set gives the top 3 products in each product_type based on the no_of_units.
thanks
View 14 Replies
ADVERTISEMENT
Jul 28, 2006
Hello --
I am having a UDT problem. When I run the Create Type command. I receive the "could not find type" error. I have seen other posts on here related to the default namespace in VB.NET. When I add the namespace I receive "Incorrect syntax near '.'." What is the format of the EXTERNAL NAME parameter. Thanks for any help. Code below...
Incorrect Syntax Error:
DROP ASSEMBLY CadSqlUdtsCREATE ASSEMBLY CadSqlUdtsAUTHORIZATION [dbo]FROM 'E:CAD.NETCADUDTsReleaseCadSqlUdts.dll'WITH PERMISSION_SET = SAFEGOCREATE TYPE dbo.ReportingAreaUDTEXTERNAL NAME CadSqlUdts.[CadSqlUdts.CadSqlUdts].ReportingAreaUDT;GO
Could Not Find Type Error:
DROP ASSEMBLY CadSqlUdtsCREATE ASSEMBLY CadSqlUdtsAUTHORIZATION [dbo]FROM 'E:CAD.NETCADUDTsReleaseCadSqlUdts.dll'WITH PERMISSION_SET = SAFEGOCREATE TYPE dbo.ReportingAreaUDTEXTERNAL NAME CadSqlUdts.ReportingAreaUDT;GO
What's up??
View 3 Replies
View Related
Mar 28, 2008
Happy Friday!
A while since I have posted a question, and this one is probably real easy.
I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1
If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.
anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
View 2 Replies
View Related
Jun 7, 2004
How Can I Find Fileds type (for example Bit or nvarchar or ntext ,...) in Sql Server;
with how sql parametr or query ?
View 2 Replies
View Related
May 30, 2001
We are evaluating users. We need to isolate certain users and certain groups. I can not figure out how to use a stored procedure or a script to filter a list of ALL users with the same 'Type'. Does this make sense? Any advice would be appreciated. I am on a deadline here and i could use any input u might offer. I can always TYPE them in using the EM - Security - Login, but I thought that there might be an easier way to do it.
Thanks! Billy
View 4 Replies
View Related
Jan 30, 2007
hi
is it possible to find a column's type inside stored procedure?? I am going to get the name of the table and then work with the columns and I just need the type of the column if I have had the name of it
regards
View 1 Replies
View Related
Jul 5, 2006
Hi,
I'm new to Integration services and .Net programming but am trying to
create a dll that I can access from Sql server 2005.
The dll read's an xml file and carries out some processing. I've run
the code as an console app and it works fine.
I have created the assembly in sqlserver thus:
create assembly PinCodeLoader from
'C:PinCodeLoaderPinCodeLoaderPinCodeLoaderinDebugPinCodeLoader.dll'
with permission_set = external_access
But when I try to reference the assembly from a stored proc
create procedure dbo.interface_processPinCodefile(@filename
nvarchar(1024))
as EXTERNAL name PinCodeLoader.PinCodeloader.Main
I get the following error:
Msg 6505, Level 16, State 1, Procedure interface_processPinCodefile,
Line 3
Could not find Type 'PinCodeloader' in assembly 'PinCodeLoader'.
I understand the context of the syntax should be
assembly_name.class_name.method_name. The first lines of the code in
the DLL are as follows
namespace PinCodeLoader
{
class PinCodeLoader
{
static void Main(string[] args)
{
Therefore assembly = PinCodeLoader, class_name = PinCodeLoader and
method_name = Main. Which should equal
EXTERNAL name PinCodeLoader.PinCodeloader.Main, I thought.
Has anybody come across this or can they offer any assistance?
Many thanks,
Paul
View 5 Replies
View Related
May 21, 2008
Hi
I have an java program which can connect to sql 2000 or 2005.I am using jdbc 2005 and can connect to sql 2000 or 2005.My problem is that the server is given at runtime by the user and i have to find somehow out to what type of server i am connecting to(2000/2005).
Can anybody help?
thanks
View 3 Replies
View Related
Jan 3, 2006
I am attempting to create a CLR Procedure. I was able to create the assembly, but I am unable to create a procedure on the assembly. This is the error I receive:
Msg 6505, Level 16, State 1, Procedure DINEServiceProc, Line 2
Could not find Type 'DINEServiceProc' in assembly 'DINEService'
Here is the VB code to create the class:
<code>
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class DINEServiceProc
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub ServiceProc(ByVal iMsg As String, ByVal iMsgType As String)
Dim conn As SqlConnection
'Create an in-process connection to the instance of SQL Server
conn = New SqlConnection("Context Connection=True")
Dim DINEService As New DINEService
Try
conn.Open()
DINEService.ProcessStartRequest(iMsg, iMsgType)
Catch sqe As SqlException
'Console.WriteLine(sqe.Message)
Return
Finally
conn.Close()
End Try
End Sub
End Class
</code>
And here is the code to create the assembly and the procedure:
<code>
USE [ServiceBrokerTest]
GO
/****** Object: SqlAssembly [DINEService] Script Date: 01/03/2006 10:38:00 ******/
CREATE ASSEMBLY [DINEServiceProc]
AUTHORIZATION [dbo]
FROM 'D:EHITServiceBrokerDINEServiceDINEServiceinDebugDINEService.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE PROCEDURE dbo.DINEServiceProc
(
@msg nvarchar(MAX),
@msgType nvarchar(MAX)
)
AS EXTERNAL NAME DINEServiceProc.DINEServiceProc.ServiceProc;
</code>
What am I doing wrong here?
View 11 Replies
View Related
Aug 13, 2007
Why would you get this error?
CLR Class
Code Snippet
Partial Public Class StoredProcedures
_
Public Shared Sub HelloWorldStoredProcedure()
SqlContext.Pipe.Send("Hello World")
End Sub
End Class
SQL Code To Test
Code Snippet
USE CMM
GO
-- Drop Pervious Version
DROP ASSEMBLY HelloWorld
GO
--Add Assebly
CREATE ASSEMBLY HelloWorld
FROM 'C:My DevelopmentSQL2005 CLRHelloWorldHelloWorldobjDebugHelloWorld.dll'
WITH PERMISSION_SET = SAFE;
GO
--Check that Assembly worked
SELECT * from CMM.sys.assemblies
--Build Procedure
CREATE PROCEDURE HelloWorldSP
AS
EXTERNAL NAME HelloWorld.StoredProcedures.HelloWorld
GO
--Test
EXEC HelloWorldSP
GO
SQL Error
Code Snippet
Msg 6505, Level 16, State 1, Procedure HelloWorldSP, Line 2
Could not find Type 'StoredProcedures' in assembly 'HelloWorld'.
What have I done wrong?
View 18 Replies
View Related
Nov 12, 2015
I am working with SQL 2012 Express and I have a table with all transactions of invoices and payments for each customer. I am looking to find the last transaction detail of either two particular types of transactions (but not both) for each customer. So far I have tried various combinations around
SELECT       MAX([sbt::dte]) OVER (PARTITION BY [sbt::code]) AS LastPayment, [sbt::folio], [sbt::typ], [sbt::net]Â
FROM dbo.tblSalbatTxn
WHEREÂ Â Â Â Â ([sbt::typ] = 13 OR [sbt::typ] = 17)Â
Then there are some cases where customers have made more than the one of the same transaction type on that same last day which I would then like to sum the net value for that day of that transaction type.
I have also worked around this but it filtered the transaction type after it found the last transaction for each customer irrespective of it's transaction type.
SELECT Â Â Â Â TOP (100) PERCENT a.[cl::code], a.[cl::alpha], b.[sbt::folio], b.[sbt::typ]
FROM Â Â Â Â Â Â dbo.tblSalAccounts AS a INNER JOIN
             dbo.tblSalbatTxn AS b ON a.[cl::code] = b.[sbt::code]
WHERE Â Â Â Â (b.[sbt::dte] =
               (SELECT     MAX([sbt::dte]) AS Expr1
                FROM       dbo.tblSalbatTxn
                WHERE     (b.[sbt::typ] = 11 OR
                             b.[sbt::typ] = 17) AND ([sbt::code] = b.[sbt::code])))Â
View 8 Replies
View Related
Apr 18, 2015
I have a dataset (DATA) like this-
Store Start End Type
XXXX 02-03-2015 10:04:00 02-03-2015 10:08:00 1
XXXX 02-03-2015 10:06:00 02-03-2015 10:10:00 2
XXXX 02-03-2015 10:09:30 02-03-2015 10:12:00 1
YYYY 03-03-2015 20:04:00 03-03-2015 20:12:00 1
YYYY 03-03-2015 20:06:00 03-03-2015 20:10:00 2
YYYY 03-03-2015 20:09:00 03-03-2015 20:16:00 1
YYYY 03-03-2015 20:15:00 03-03-2015 20:18:00 2
YYYY 03-03-2015 20:17:00 03-03-2015 20:22:00 2
YYYY 03-03-2015 20:21:00 03-03-2015 20:27:00 1
The output of this file (RESULT) is-
Store Start End Mins of Type 2 only
XXXX 02-03-2015 10:04:00 02-03-2015 10:12:00 00:01:30
YYYY 02-03-2015 20:04:00 02-03-2015 20:27:00 00:05:00
So for each Store (Store is unique in the table), I am rolling up the intervals with overlaps to create a single interval.
Now, for each store, I want to find the time period for purely type 2. So if there is an overlap, type 1 has the dominance. And I want the sum of time period of whatever is left for type 2.
I have written this code but not able to address the overlap issue:
alter table [DATA] add Outage float;
update [DATA]
set Outage = DATEDIFF(SECOND,[Start],[END])
alter table [RESULT] add [Outage_Type1 (%)] float,[Outage_Type2 (%)] float;
[Code] ....
View 11 Replies
View Related
Sep 28, 2006
Hello Everyone,A have a Managed Stored Procedure ([Microsoft.SqlServer.SqlProcedure]). In it I would like to call a UserDefinedFunction:public static SqlInt32 IsGetSqlInt32Null(SqlDataReader dr, Int32 index) { if(dr.GetSqlValue(index) == null) return SqlInt32.Null; else return dr.GetSqlInt32(index) }I than allways get the following ErrorMessage:Column, parameter, or variable #1: Cannot find data type SqlDatareader.Is it not possibel to pass the SqlDatareader to a SqlFunction, do the reading there and return the result.My original Problem is, that datareader.GetSqlInt32(3) throws an error in case there is Null in the DB. I thought SqlInt32 would allow Null.Would appreciate any kind of help! Thanks
View 1 Replies
View Related
Apr 28, 2015
SELECT
P.Publication
,P.Publication_type
,S.Subscriber_ID
,S.Update_Mode
FROM MSPublications P
INNER JOIN MSSubscriptions S
ON P.Publication_ID = S.Publication_ID
give me publication_type=0. So it is transactional replication but how do we know that is pull or push?
View 2 Replies
View Related
May 17, 2012
The followingÂ
SearchCondition[] sc = {new SearchCondition() {Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
Name = "TypeName"
,Values = new string[] {"Report"}Â Â Â Â Â Â Â Â Â
,Condition = ConditionEnum.Equals
,ConditionSpecified = true
}};
catalogItems = ReportService2010.FindItems("/"
,BooleanOperatorEnum.And
,new Property[] {new Property(){Name = "Recursive",Value="True"}}
,sc
);
Returns the following error
System.Web.Services.Protocols.SoapException: The TypeName field has a value that is not valid. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidElementException: The TypeName field has a value that is not valid.
at Microsoft.ReportingServices.WebServer.ReportingService2010Impl.FindItems(String Folder, BooleanOperatorEnum BooleanOperator, Property[] SearchOptions, SearchCondition[] SearchConditions, CatalogItem[]& Items)
  at Microsoft.ReportingServices.WebServer.ReportingService2010.FindItems(String Folder, BooleanOperatorEnum BooleanOperator, Property[] SearchOptions, SearchCondition[] SearchConditions, CatalogItem[]& Items)
The type appears to be correct. I've tried type of "Folder" and receive the same error.
View 5 Replies
View Related
Aug 16, 2007
Hi,
I installed the SharepointRS.msi add-in and configured the Reporting Services to the sharepoint integration mode. After the installation ,I am supposed to find a new section in the Application management of Central Administration Tool of sharepoint called Reporting services. But i am not able to find that.
View 1 Replies
View Related
Aug 11, 2014
I have a patient record and emergency contact information. I need to find duplicate phone numbers in emergency contact table based on relationship type (RelationType0 between emergency contact and patient. For example, if patient was a child and has mother listed twice with same number, I need to filter these records. The case would be true if there was a father listed, in any cases there should be one father or one mother listed for patient regardless. The link between patient and emergency contact is person_gu. If two siblings linked to same person_gu, there should be still one emergency contact listed.
Below is the schema structure:
Person_Info: PersonID, Person Info contains everyone (patient, vistor, Emergecy contact) First and last names
Patient_Info: PatientID, table contains patient ID and other information
Patient_PersonRelation: Person_ID, patientID, RelationType
Address: Contains address of all person and patient (key PersonID)
Phone: Contains phone # of everyone (key is personID)
The goal to find matching phone for same person based on relationship type (If siblings, then only list one record for parent because the matching phones are not duplicates).
View 9 Replies
View Related
Jul 23, 2005
I have a 1:1 relationship between tables and am finding that the parentis sometimes mising the child.How do I query for what is not there?TIA
View 4 Replies
View Related
Sep 19, 2006
Hello All,
We have an app that we do not have the source code for that is behaving badly. I'd like to find out what queries it is running in order to possibly fix the issue form the SQL server side of things. Anyone know what table/view I should select off of to find the queries that have been run recently?
Thanks in advance!
View 1 Replies
View Related
Jul 19, 2004
Hi
I am trying to find when a name has been entered more than once into 1 database table.
I'm currently doing something like this (can't remember exactly, not at work)
SELECT COUNT(*) AS Cnt, Name
FROM tblTable
GROUP BY Name
ORDER BY Cnt Desc
This brings back all the Names in the database and tells me which are duplicates but I want to just have the results of the duplicate values and not the single values.
Hope you can help.
Thanks
View 2 Replies
View Related
Jul 8, 2002
Hello,
We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
Regards,
Vnk.
View 3 Replies
View Related
Jul 8, 2002
Hello,
We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails in job, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
Regards,
Vnk.
View 3 Replies
View Related
Jan 12, 2005
I have a table with several foreign key relationships. I am scripting with PHP and was wondering if anyone knows how to query the database to show the relationships that a table has. I have been using MySQL, but for a number of projects have to use MS SQL.
Any help will be greatly appreciated.
View 2 Replies
View Related
Nov 13, 2013
If I run sample query below against an adventureworks database
where can I find query cost?
USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;
Does estimated subtree cost for in the actual execution plan (when right click SELECT operator in the execution plan) is considered query cost?
View 3 Replies
View Related
Sep 4, 2014
I have a warehouse table but I don't know which query will update warehouse inside of information ? Thus, how to write a query list all query have include this warehouse table name in there ?
View 1 Replies
View Related
Jun 20, 2007
can anyone explain how this works:
USE NORTHWIND
GO
SELECT freight
FROM orders E1
WHERE (N =
(SELECT COUNT(DISTINCT (E2.freight))
FROM orders E2
WHERE E2.freight >= E1.freight))
replace N by a number. To find that Nth value from the table.
Ashley Rhodes
View 2 Replies
View Related
May 5, 2008
Say the table has a primary key, and the latest value on it is 100, then all the records on that table are deleted... If I INSERT new record, the key will be 101... Can I find out what the key is going to be before inserting a new record to the table?
View 7 Replies
View Related
Sep 4, 2007
I have a query that can tell me the columns in a table. In this case "Contact". What I would like to add to my query is if the column is part of the primary key. Can that be done? how so?
Code Snippet
SELECT
c.name AS column_name,
c.column_id,
SCHEMA_NAME(t.schema_id) AS type_schema,
t.name AS type_name,
t.is_user_defined,
t.is_assembly_type,
c.max_length,
c.precision,
c.scale
FROM
sys.columns AS c
JOIN sys.types AS t ON
c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('Contact') ORDER BY c.column_id;
Thnx
Matt
View 4 Replies
View Related
Mar 17, 2008
Hello friends,
I am working on web application using sql server 2000, visual Studio 2005. In one of the table I have field 'NetAmount' purposely I had kept datatype as char. Now I want to update that field. for e.g. I have to add 100 to netAmount. How I can do it with sql query. Please let me know.
Thanks & RegardsGirish Nehte
View 1 Replies
View Related
May 1, 2006
I want to query XML datas.
I have one table T.this T has two columns,id int , datasXML xml.
the datasXML has some XML datas, there into one data is:
<root> <item val="true"></item> <others ... /></root>
Now,I want to query this table's datasXML column,and root/item value is true.
How to design this SQL string?
SELECT * FROM T WHERE datasXML = "<root>.." -- or other?
Please give me some samples.thanks!
Ray Lynn
View 2 Replies
View Related
Jul 20, 2005
Hi,I have a table with the follow fields :ID - IntDate - DatetimeI need to make a simple query to result the records between to dateswith a single ID.Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230I´m using the follow :ADOQuery1.Close;ADOQuery1.SQL.Clear;ADOQuery1.SQL.Add('Select * from Apro where data between Inicio andFinal');ADOQuery1.Parameters[0].Value:=Inicio;ADOQuery1.Parameters[2].Value:=Final;ADOQuery1.Open;When I open the query it doesn´t work cos it´s result a null setHow can I solve this?I´m using SQL Server 2000 and Delphi 6Thanks for atention.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 4 Replies
View Related
Aug 16, 2007
I've been struggling with this one for quite some time now and would appreciate some help:-
I'm trying to get a list of schools that only has male students. the closest I have so far:-
SELECT Schools.SchoolName, FORUM_MEMBERS.M_SEX , COUNT(Schools.SchoolName) AS no_of_students
FROM Schools
INNER JOIN FORUM_MEMBERS ON (Schools.SchoolID = FORUM_MEMBERS.SchoolID)
WHERE (FORUM_MEMBERS.UserType=4)
GROUP BY Schools.SchoolName, FORUM_MEMBERS.M_SEX
ORDER BY Schools.SchoolName, FORUM_MEMBERS.M_SEX;
The result is a list of schools:-
SchoolName, M_SEX, no_of_students
school 1, Male, 11
school 1, Female, 20
school 2, Male, 8
school 2, Female, 1
school 3, Male, 12
school 4, Male, 14
school 5, Male, 8
school 5, Female, 1
But what I really need is schools with only male students:-
SchoolName, M_SEX, no_of_students
school 3, Male, 12
school 4, Male, 14
Many Thanks
View 7 Replies
View Related