Query To Find Nth Value Explanation

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


ADVERTISEMENT

Learning SQL: Rather Difficult Query Needed: And If You Know This With Explanation What You're Doing If Possible :)

Jan 7, 2008

If you know the answer please explain what you're doing if possible, that'll help me :)I have the following tables:CREATE TABLE [dbo].[tblUserData](    [UserCode] [int] IDENTITY(1,1) NOT NULL,    [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    [DisplayName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,) ON [PRIMARY]CREATE TABLE [dbo].[tblFriends](    [UserCodeOwner] [int] NOT NULL,    [UserCodeFriend] [int] NOT NULL,    [createdate] [datetime] NOT NULL CONSTRAINT [DF_tblFriends_createdate]  DEFAULT (getdate())) ON [PRIMARY]in tblFriends relations are stored twice, so for a relation between user 5 and 6, there will be 2 rows: 5-6 and 6-5Now, I want to get the columns (UsercodeOwner,UsercodeFriend,createdate,username,displayname) for relations that were created in tblFriends in the last 10 days for the FRIENDS of a person with usercode 5.Example:tblUserdata5 peter Petertje6 john Johnny11 simon SimonSays15 monique MontjetblFriends5 6 'createdate 30 days ago'5 11 'createdate 5 days ago'6 5 'createdate 30 days ago'6 11 'createdate 3 days ago'6 15 'createdate 7 days ago'11 5 'createdate 5 days ago'11 6 'createdate 3 days ago'15 6 'createdate 7 days ago'The resultset for a query on usercode 5 would now be (usercode1, username1, displayname1,usercode2, username2, displayname2,createdate):6 john Johnny 11 simon SimonSays 'createdate 3 days ago'6 john Johnny 15 monique Montje 'createdate 7 days ago'As you can see each relation is only returned twice even though there are always two entriesWhat would be the SQL statement, if possible without temp table..Thanks!

View 21 Replies View Related

Just An Explanation Of What This Means Please

Jan 22, 2008

Hi,
I have a project that has been given to me and need help please. The complete class is as follows
Public Class CarAccessData
    Public Function Getcarinfo() As List(Of CarInfo)
        Dim AllCarInfo As New List(Of CarInfo)
        Dim SQL As String        SQL = "SELECT [SKU], [CarMake], [CarModel], [Carprice]"
        Dim MyConnection As SqlConnection        MyConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("AntConnectionString1").ConnectionString)
        Dim aCmd As SqlCommand        aCmd = MyConnection.CreateCommand
        aCmd.CommandText = SQL        aCmd.CommandType = CommandType.Text
        Dim aDataReader As SqlDataReader
        Try
            MyConnection.Open()
            aDataReader = aCmd.ExecuteReader
            While aDataReader.Read()
                AllCarInfo.Add(New CarInfo(aDataReader))            End While
        Catch ex As Exception            Throw ex        Finally
            aDataReader.Close()
            MyConnection.Close()        End Try
        Return AllCarInfo    End Function
End Class
 
The bit I dont quite understand is the following snippet from above
 
 
Dim aCmd As SqlCommand        aCmd = MyConnection.CreateCommand
        aCmd.CommandText = SQL        aCmd.CommandType = CommandType.Text
        Dim aDataReader As SqlDataReader
        Try
            MyConnection.Open()
            aDataReader = aCmd.ExecuteReader
            While aDataReader.Read()
                AllCarInfo.Add(New CarInfo(aDataReader))            End While
        Catch ex As Exception            Throw ex        Finally
 
Can anyone explain this in real ABC style step by step please, just so I can start to understand this, ( I am quite new to this)
many thanks,
Anteater

View 8 Replies View Related

Explanation Of Columns_updated()

Jun 1, 2004

the statement
if ((columns_updated() & 2 + 4 + 8)) > 0) is supposed to tell me if the 2nd, 3rd or 5th columns were updated. My question is, what desginates the column 2,3,5, when 2,4,8 are in the statement

View 1 Replies View Related

Need Some Explanation On Transactions

Apr 28, 2005

I am having some problems with transactions, although it seems that the problem exists within the vb code making the db call it may be that sql is the source of the problem.

1.
There is a number of stored procs that contain transactions most of which are inserts followed by a select statement to retrieve the most recently added ID. So to start with is a select the best way or is the @@Identity. I have read the @@Identity is global, so for a external server that is running a number of databases I stayed away from it. Did I take the right actions or is there a better way?

2.
Is having sql transactions within a call from vb.net ok? I know that the sqlClient class doesn't support nested transactions, but does that include transactions within sql?

3.
if a stored proc is called, from vb contained in a sqlClient transaction, are ALL the calls from within the sproc able to be rolled back?

If there is a reliable way to obtain the Identity without containing it in a transaction, and having the internal sql transactions in the problem, then I am home free, so I am hoping this is the case.

Thanks for any help

View 11 Replies View Related

Simple Explanation Please

Jul 23, 2005

PREDICATESUsed as a clause.A. What does PREDICATES mean?B. What does it mean when used in a where clause?I checked BOL (Glossary) but get no explanation there.ThanksJay

View 5 Replies View Related

SQL Queries - Explanation Of Od

Oct 31, 2007

I am wondering what are od1 and od2 used for in SQL Queries? Are they used for joining... can anyone explain their significance in the queries below... (especially the commands in red)


USE Northwind
SELECT OrderID, CustomerID
FROM Orders o
WHERE 20 < (SELECT Quantity
FROM [Order Details] od
WHERE o.OrderID = od.OrderID AND od.ProductID = 23)USE Northwind
SELECT DISTINCT ProductName, Quantity
FROM [Order Details] od1 JOIN Products p
ON od1.ProductID = p.ProductID
WHERE Quantity = (SELECT MAX(Quantity)
FROM [Order Details] od2
WHERE od1.ProductID = od2.ProductID)

View 3 Replies View Related

Basic Syntax Explanation

Jul 23, 2005

In T sql for sql server, what is the technical difference between thecomparisons "is" and "="for example:set @test = nullprint @test is null -> trueprint @test = null -> false

View 3 Replies View Related

Very Chalanging Question - Explanation

May 31, 2006

Ok, here is a asample table representing the problem more clearlyA | B | C | D-----------------a1 b1 c1 d1a1 b2 c2 d2a3 b3 c1 d3a4 b4 c4 d3a5 b5 c5 d5a6 b6 c6 d3Tha duplications are:row 1+2 in param Arow 1+3 in param Crow 3+4+6 in param Donly row 5 is unique in all parameters.conclusion: row 1+2+3+4+6 are the same usergoal: to find all duplicated rows & to delete them all accept oneinstance to leave.Note:Finding that row 1similar to 2 in A & deleting it will loose databecause we won't know that row 1 is ALSO similar to 3 on C & later onfinding that 3 is similar to 4 & 6 on D & so onThe simple time consuming (about 2 weaks) query to acomplish the taskis:SELECT count(*),A.B,C,DFROM tblGROUP BY A,B,C,DHAVING count(*)>1I THANK YOU ALL

View 3 Replies View Related

Explanation Of SQL Server Database Files

Feb 22, 2000

When a database is created in SQL Server, 4 files are made:

(example)
DB_Data.DAT
DB_data.mdf
DB_Log.DAT
DB_log.ldf


What do each of these files contain? I can figure out that the main database is the DB_Data.DAT, but why is the transaction log a .DAT and why is there four files instead of two? etceterea.

Llyal

View 3 Replies View Related

Instr (vbscript) Function Explanation ???

Jan 15, 2001

I went to Microsoft to find some info about the function Instr. I need to
perform a search with a string similar to their example I found below. Can
anyone explain to me Microsoft's example?? I am little confused by the
parameters used and the explanation it gives back to me??

Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP" ' String to search in.
SearchChar = "P" ' Search for "P".
MyPos = Instr(4, SearchString, SearchChar, 1) ' A textual comparison
starting at position 4. Returns 6.
MyPos = Instr(1, SearchString, SearchChar, 0) ' A binary comparison
starting at position 1. Returns 9.
MyPos = Instr(SearchString, SearchChar) ' Comparison is binary by default
(last argument is omitted). Returns 9.
MyPos = Instr(1, SearchString, "W") ' A binary comparison starting at
position 1. Returns 0 ("W" is not found).

My problem is this:

I need to scan within SearchString for blanks/spaces characters. When I
find one, then place the values to the left and right of it in seperate
columns. For example, I would need to scan 'John Smith A' and then place
'John' in FirstName column, 'Smith' in LastName column, and 'A' in MidName
column.

I think this is how my code would read, but I am confused on how to place
the results into my table to the correct columns?

my search string would be SearchString = 'John Smith A'
my SearchChar would be SearchChar = ' ' (note I am searching for a
space/blank character)

So would then my code be like:

Dim SearchString, SearchChar, MyPos
SearchString = 'John Smith A'
SearchChar = ' '
MyPos = Instr(1, SearchString, SearchChar, 0)

How do I get whatever is returned from the Instr function to a column in a
table??

Any help would be great.

Rey

View 1 Replies View Related

Need An Explanation Of Msdtssrvr.ini.xml And Named Instances

Apr 30, 2008

We've read about a single installation of SSIS applies to all SQL instances on a server. We've also read how by default SQL Server stores SSIS packages in the MSDB database on the default instance and if you want to have packages stored on a named instance you must modify msdtssrvr.ini.xml. Here are our questions.

1 - If msdtssrvr is modified to reference a named instance, such as <ServerName>SQLTESTINST1<ServerName>, when I connect to Integration Services through Management Studio do I still specify the default instance name in the login window? That's the only way we have been able to connect and despite specifying the default instance name, the packages displayed are those of the named instance.

2 - If I am correct that you must always specify the default instance name when connecting to Integration Services, then in order to eliminate confusion regarding what SQL instance's SSIS packages I am viewing under the folder structure I can create SQL Server folders by modifying msdtssrvr.ini.xml. For example:


<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB-INST1</Name>
<ServerName>SQLTESTINST1</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB-INST2</Name>
<ServerName>SQLTESTINST2</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>


I then restart the SSIS service and can now see the following folder structure.

Running Packages
Stored Packages

+ File System
+ MSDB
+ MSDB-INST1
+ MSDB-INST2

If I expand MSDB-INST1 I will only see packages saved on the instance named INST1. The same is true for INST2. If I expand MSDB I will only see packages saved on the default instance.

Does this sound like I am doing things correctly?

Thanks, Dave

View 3 Replies View Related

Explanation Wanted About The Service Principal Name ( SPN )

Jun 26, 2007

Hello,



In the ErrorLog of my Sql Server , i found this line :

2007-06-26 05:35:18.37 Serveur The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.


Operating System XP Home SP2

SQL Server 2005 Express Edition with Advanced SP1 )



( idem for another workstation with XP Pro SP2 and same version of SQL Server 2005 Express



My problem is :

i want use the windows authentification but my computers are on Worhkgroup linked by a router ( no window server )

i have read that's possible to connect from a remote computer to a computer having a SQL Server 2005 Express through SPN

How can i do it ? ( activating NTLM ? but how ?)

I'm writing a C# program which must be executing on several computers with a SQL Server 2005 Express installed on a particular computer. These computers will belong to a domain of Windows Server 2003.

As i can't connect to this "normal" network, i am trying to simulate this network at home because i want to test this program and especially the possible locks problems.

I don't know whether i'm querying with the "correct" forum.

Sorry for my poor english.

I shall appreciate any help about this problem

Have a nice day

View 1 Replies View Related

Simple Explanation Of Mining Algorithms

Jun 9, 2007

Hello Everyone,



I am reading DataMining Tutorial and right now I am at the Mining Algorithms section. I cannot understand any of the algorithms. For example take the following text... what a bunch of mouthful bla bla bla it is ....


"The Microsoft Decision Trees algorithm supports both classification and regression and it works well for predictive modeling. Using the algorithm, you can predict both discrete and continuous attributes.
In building a model, the algorithm examines how each input attribute in the dataset affects the result of the predicted attribute, and then it uses the input attributes with the strongest relationship to create a series of splits, called nodes. As new nodes are added to the model, a tree structure begins to form. The top node of the tree describes the breakdown of the predicted attribute over the overall population. Each additional node is created based on the distribution of states of the predicted attribute as compared to the input attributes. If an input attribute is seen to cause the predicted attribute to favor one state over another, a new node is added to the model. The model continues to grow until none of the remaining attributes create a split that provides an improved prediction over the existing node. The model seeks to find a combination of attributes and their states that creates a disproportionate distribution of states in the predicted attribute, therefore allowing you to predict the outcome of the predicted attribute"



In the above text what is meant by discrete and continious attributes? what is regression? what is predicted attributes? what are input attributes? what is distribution of states?



Is there a source which explains these algorighms in a easier way ....

View 4 Replies View Related

Explanation Of Security Groups For 2005

Sep 21, 2005

I've been reconfiguring my Windows service accounts for the SQL Server service and the SQL Agent service to comply with the security best practices for SQL Server 2005.  Specifically, I created two new network accounts.  One account runs the SQL Server service, the other runs the SQL Agent service.

View 1 Replies View Related

Funny SQL Server Error Messages - Anyone Has An Explanation?

Apr 3, 2006

I am getting a new server online at a customer, and our system shows very funny erorrs under full load. No explanations could be found. Anyone has some?
Here we go:
Server failed to resume the transaction, desc: 360000054a. The transaction active in this session has been committed or aborted by another session.,
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
and
New request is not allowed to start because it should come with valid transaction descriptor.
System.Data.SqlClient.SqlException
I am a little lost on those. Simple fact is that I do not find any description of those. Anyone an idea what causes these?

View 2 Replies View Related

Checksum_agg And Row Size Error. Require Explanation.

Jul 23, 2005

Hi,I can see that by using the object ID rather that the object name, thefollowing SQL query works. Has anybody got any idea what is causing theerror?-- Works OKselect o.id,checksum_agg(binary_checksum(m.text))from sysobjects o,syscomments mwhere o.id = m.idand o.xtype in ('FN','IF','P','TF','TR','V')group by o.id-- Error-- Server: Msg 1540, Level 16, State 1, Line 1-- Cannot sort a row of size 8096, which is greater than the-- allowable maximum of 8094.select object_name(o.id),checksum_agg(binary_checksum(m.text))from sysobjects o,syscomments mwhere o.id = m.idand o.xtype in ('FN','IF','P','TF','TR','V')group by object_name(o.id)-- Error-- Server: Msg 1540, Level 16, State 1, Line 1-- Cannot sort a row of size 8096, which is greater than the-- allowable maximum of 8094.select o.name,checksum_agg(binary_checksum(m.text))from sysobjects o,syscomments mwhere o.id = m.idand o.xtype in ('FN','IF','P','TF','TR','V')group by o.name-- Workaroundselect getdate(),object_name(x.id),check_sumfrom (select m.id,checksum_agg(binary_checksum(m.text)) as check_sumfrom syscomments minner joinsysobjects oon m.id = o.idwhere o.xtype in ('FN','IF','P','TF','TR','V')group by m.id) as xRegardsLiam

View 1 Replies View Related

Linear Regression With Nested Explanation Variable

Jan 22, 2007

We are trying to create a model of linear regression with nested table. We used the create mining model sintax as follow :

create mining model rate_plan3002_nested2

( CUST_cycle LONG KEY,

VOICE_CHARGES double CONTINUOUS predict,

DUR_PARTNER_GRP_1 double regressor CONTINUOUS ,

nested_taarif_time_3002 table

( CUST_cycle long CONTINUOUS,

TARIFF_TIME text key,

TARIFF_VOICE_DUR_ALL double regressor CONTINUOUS

)

) using microsoft_linear_regression

INSERT INTO MINING STRUCTURE [rate_plan3002_nested2_Structure]

(CUST_cycle ,

VOICE_CHARGES ,

DUR_PARTNER_GRP_1 ,

[nested_taarif_time_3002](SKIP,TARIFF_TIME ,TARIFF_VOICE_DUR_ALL)

)

SHAPE {

OPENQUERY([Cell],

'SELECT CUST_cycle ,

VOICE_CHARGES ,

DUR_PARTNER_GRP_1

FROM dbo.panel_anality_3002

order by CUST_cycle ')}

APPEND

({OPENQUERY([Cell],

'select CUST_cycle,

TARIFF_TIME,

CYCLE_DATE

from dbo.nested_taarif_time_3002

order by CUST_cycle,TARIFF_TIME')

}

relate CUST_cycle to CUST_cycle

) as nested_taarif_time_3002



The results we got are a model with intercept only. if we don't use the nested variable (the red line) we get a rigth model . (we had more variable ....)

Is there a way to do this regression correctly?

Thanks,

Dror

View 7 Replies View Related

ReportBuilder / Report Model Explanation Needed

May 1, 2008

(I wish I was able to attach pictures....anyhow I'll try my best without them)

The customer's goal is to via ReportBuilder to select fields from multiple
many-to-many relationships. They are able to see the fields that exists
within the various many-to-many relationships but IS ONLY able to select
fields from ONE many-to-many relationship. Example using AdventureWorks:

DataSourceView (relationships automatically generated by wizard)
Contact ---< VendorContact >--- Vendor
Contact ---< StoreContact >--- Store
Contact ---< ContactCreditCard >--- CreditCard

So the goal in the ReportBuilder is to be able select fields from
Contact/Vendor/Store/ and CreditCard.
PROBLEM is that the ReportBuilder ONLY allows user to select fields from
Contact/Vendor OR Contact/Store OR Contact/CreditCard (exclusively)

The only workaround I've found is to reverse the relationships in the
DataSourceView to the following

Contact >--- VendorContact ---< Vendor
Contact >--- StoreContact ---< Store
Contact >--- ContactCreditCard ---< CreditCard

This works however we need an explanation as to why it only works this way?

Thanks in advance

View 1 Replies View Related

Simple Explanation To Concatenating Multiple Items Into One Column

Jan 17, 2008

Hi, I am a extreme beginer to sql server and i am i'm having big trouble trying to display my sql query properly. Bascially i want to put the results of a one to many query into one row per record. I have read articles and forums discussing 'concatenating the values' or creating a function??? but i dont follow what they mean and i am completely lost. Can anyone provide a really simple explanation on what i need to do to resolve my duplicate row issue? i urgently need to find a solution to this.
 Regards

View 2 Replies View Related

Query To Find What Is Not There

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

How Do I Find Out What Query Someone Ran?

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

Query To Find Duplicates

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

SQL Query Help, Need To Find Dependency In SP&#39;s.

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

SQL Query Help. Need To Find Dependency In SP&#39;s.

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

SQL Query To Find Relationships

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

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 View Related

Where To Find Query Cost

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

Find Table Name In All Query

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

Can I Use A Query To Find The Next Key In SQL Server?

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

A Query To Find Primary Key

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

Query To Find Schools With Only Boy/girls

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

Can I Find The Drive Space Using A SQL Query?

Jun 1, 2001

Hey - I know that I can find space information about SQL Server. Allocated space, free space, used space, data space, etc... BUT is there a way that I can query how much total/available space is on the actual drive? For example, let's say that I have SQL installed on the D drive of a Server; i also have another application on that drive. I know that I can query how much room SQL Server is using, but can i query how much total/available space is on the drive? Any help appreciated.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved