Select Distinct Record Only If Certain Column Not Null

Apr 5, 2007

Been trying to come up with a query to filter-down my sample set into
distinct records. For instance, lets say column1 is a sample set,
column2 is the parameter, and column3 is a name and column4 is a type
(lets also say there is a fifth column (id) that is an id). What I
need is one record per type per sample only if type is given, if not,
then return that record as well.

I've used a subquery to get as close to the desired query is as
possible:

select * from table1
where id in (select min(id) from table1
where column1="A"
group by column1, column2)

Here's an example of all data for sample "A":

1 2 3 4
----------
A 1 X P
A 1 Y P
A 1 Z P
A 2 W
A 3 W
A 4 T P
A 5 U P
A 6 V P
A 7 T
A 7 U
A 7 V

I want output :

1 2 3
-------
A 1 X P
A 2 W
A 3 W
A 4 T P
A 5 U P
A 6 V P
A 7 T
A 7 U
A 7 V

Except the above query will exclude the last two records because
column3 is not 'grouped by'.

Basically I need to reduce any 'range' of records per sample (column
a) where column4 is not null (ie = 'P'), to only one record and
keeping all others. Thanks in advance:

-B

View 6 Replies


ADVERTISEMENT

HOW TO USE DISTINCT IN SELECT STATEMENT TO FILTER OUT DUPLICATED RECORD??

Jan 5, 2001

I HAVE A SELECT STATEMENT WITH TEACHERS AND STUDENTS AND SOMETHING ELSE TOO.
FOR EACH TEACHER I ONLY NEED ONE(FIRST ONE) STUDENT.
HOW THE STATEMENT SHOULD BE?

SELECT DISTINCT .... TID, SID, SOMETHING ???????

View 3 Replies View Related

Distinct Record Equal To 2 Values From Same Column

Dec 6, 2013

Distinct name that match both subjects (math, science) from classname in level 2 only. Not sure where to even start. Example table below:

name subject level
bob math 2
hank math 1
joe science 2
bob science 2
joe math 2
ben science 2
carl science 1

View 2 Replies View Related

Select Null Value Date Fields Record

Sep 28, 1999

Hi:

I used this statement, select * from table1 where date1 = null, in SQL Query window and got a few records back. Now, I used the same statement in my VB 5 code and no record is found. How do I select all the records in table1 which do not have values in field date1? Thanks for the help.

-Nicole-

View 2 Replies View Related

How To Select Distinct On One Column

Apr 15, 2015

I have this

SELECT DISTINCT inta, name, PHN#, fROM nydta.adres
WHERE inta <> ' '

I want the distinct for inta because alot of the time phone is blank so those are coming thru i do want all columns, but distinct for inta.

View 1 Replies View Related

SELECT DISTINCT On One Column

Oct 19, 2006

I know this is a popular problem, but i haven't found an answer to my situation.
I have a table (myTable) with three Columns (Column1, Column2, Column3). Column1 entries are unique (with unique identifier), Column2 entries are not unique.
I want to do a SELECT DISTINCT on myTable so that Column2 is unique and i get all the other columns. I thought something like this would work

SELECT DISTINCT Column2 as Col2, newid() as Col1, Column3 as Col4
-- I am getting uniqueidentifier for each row as well

This doesn't seem to work however. I still get all the rows because Col1 is unique.
since i am using aliases, the usual syntax doesn't work either.

any help would be greatly appreciated!

View 2 Replies View Related

Select With Multiple Column And Distinct

Jun 22, 2008

Hi guys, I'm hoping there's someone out there with more sql knowledge and experience than me. I'll try to explain everything.

I'm trying to create a select statement but i'm not gettting the required results mainly because i think its a very complicated select.

Here is the scenario.

The table has 12 columns

ProductID Colour MD01 MD02 MD03 MD04 MD05 MD06 MD07 MD08 MD09 MD010


The ProductID is naturally the unique key.

There is always a colour value. But there is not always a value in the MD columns. For example one Product may have values in MD01 MD02 MD03 MD04 MD05 whilst another has values in all MD columns.

My problem is thatI am trying to create a results list based upon selecting distinct values from the colour and md columns

In otherwords i can't have more than only one instance of a word appearing in the recordset list

I'm really struggling with this because there are only 6 colours so if i set distinct purely on 6 colours i only get back 6 rows.

When I try to set disctinct across all the MD columns it seems to ignore it and lists results for example in the table

ProductID Colour MD01 MD02 MD03 MD04 MD05 MD06 MD07 MD08 MD09 MD010
1 red car bike
2 blue bike car train

my select lists results as
red
car
bike
blue
bike
car
train

and it is as if it only carries out the distinct command across the row not across all columns for all rows if you see what i mean?

I need to be able to list all data from all rows that have values in the MD columns and colour column but not list the values more than once and not list "empty" (NULL) columns. Does this make sense?

This is the select statement i wrote.

Select DISTINCT md00, md01, md02, md03, md04, md05, md06, md07, md08, md09, md10, colour FROM TEMPLATES WHERE md00 IS NOT NULL or md01 IS NOT NULL or md02 IS NOT NULL or md03 IS NOT NULL or md04 IS NOT NULL or md05 IS NOT NULL or md06 IS NOT NULL or md07 IS NOT NULL or md08 IS NOT NULL or md09 IS NOT NULL or md10 IS NOT NULL

But it returns empty columns and it returns every instance of the same word so in other words the distinct command doesn't seem to be working at all?

I don't know if this is because of my asp code I am trying to list results with the rescordset?

<%
While ((Repeat1__numRows <> 0) AND (NOT template_rs.EOF))
%>

<%=(template_rs.Fields.Item("md01").Value)%>
<%=(template_rs.Fields.Item("md02").Value)%>
<%=(template_rs.Fields.Item("md03").Value)%>
<%=(template_rs.Fields.Item("md04").Value)%>
<%=(template_rs.Fields.Item("md05").Value)%>
<%=(template_rs.Fields.Item("md06").Value)%>
<%=(template_rs.Fields.Item("md07").Value)%>
<%=(template_rs.Fields.Item("md08").Value)%>
<%=(template_rs.Fields.Item("md09").Value)%>
<%=(template_rs.Fields.Item("md10").Value)%>
<%=(template_rs.Fields.Item("colour").Value)%>

<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
template_rs.MoveNext()
Wend
%>


I have one more problem. How can I also in addition to being able to list all distinct results list only results for a specific letter

for example

Select DISTINCT md00, md01, md02, md03, md04, md05, md06, md07, md08, md09, md10, colour FROM TEMPLATES WHERE md00 IS NOT NULL or md01 IS NOT NULL or md02 IS NOT NULL or md03 IS NOT NULL or md04 IS NOT NULL or md05 IS NOT NULL or md06 IS NOT NULL or md07 IS NOT NULL or md08 IS NOT NULL or md09 IS NOT NULL or md10 IS NOT NULL WHERE FIRST LETTER ='A'?


I am so far out of my depth here guys I am hoping that someone who has real knowledge of SQL can help me with this statement. I've been pulling my hair out for days now and getting just more and more frustrated listing the same results :(

-BB

View 20 Replies View Related

Select Distinct Column While Joining Tables

Oct 29, 2014

I am attempting to run the following select statement joining multiple tables but in the end result I would like only Distinct/Unique values to be returned in the invlod.lodnum column.

[select pw.schbat, adrmst.adrnam, adrmst.adrln1, adrmst.adrcty, adrmst.adrstc, adrmst.adrpsz,
invlod.lodnum,
shipment.host_ext_id, shipment_line.ordnum, car_move.car_move_id
from aremst join locmst
on (aremst.arecod = locmst.arecod)
and (aremst.wh_id = locmst.wh_id)

[Code] .....

View 4 Replies View Related

SELECT DISTINCT And ORDER BY With Aliased Column

Feb 14, 2008



This query demonstrates a problem I have run across:


USE AdventureWorks

GO

-- This query works fine.

SELECT DISTINCT FirstName AS Name1 FROM Person.Contact ORDER BY FirstName

GO

-- This query also works fine.

SELECT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName

GO

-- This query returns error 145

SELECT DISTINCT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName

GO


The last query returns the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified".
It will work if I change ORDER BY to use "Name1" instead of "FirstName", but in the situation I have at hand, the query is generated by third-party software and I don't have the ability to change it.
Can anyone explain why what's going on here? Oddly, this same query will work if I run it against SQL Server 2000.

View 8 Replies View Related

Update Via SQLDataSource Fails For Record With A Null Value In One Column

Oct 16, 2006

Hi,I have an updatable DataGrid linked to a SQLDataSource in a web site developed using VS2005. Update works fine unless a value in the existing row is Null. Only one column in the database allows nulls.Putting a debug stop in the SqlDataSource1_Updating event, I checked the parameter value in the Immediate window and got the following result:?e.Command.Parameters(16){System.Data.SqlClient.SqlParameter}System.Data.SqlClient.SqlParameter: {System.Data.SqlClient.SqlParameter}DbType: Int32 {11}Direction: Input {1}IsNullable: FalseParameterName: "@original_FiresolveJobNo"Size: 0SourceColumn: ""SourceColumnNullMapping: FalseSourceVersion: Current {512}Value: NothingIs there a property I can set to generate a suitable Null check clause for the Update statement?Many Thanks,Keith.

View 3 Replies View Related

Record Not Updated Or Deleted When Any Column Consists The Null Value

Oct 23, 2007

Hi All  In My application when i want to work with sqldatasource for updation and delteion tasks it is working properly when all the columns consists the data. If any of the column consists the null values its not updating and deleting. Advices are needed.Thank uBaba 

View 4 Replies View Related

Select Distinct Column Data With Other Values From The Table

Dec 16, 2004

I have a table 'wRelated' with the following columns

[related_id] [int]
[channel_id] [int]
[mui] [varchar]
[price_group_id]
[type_id] [int]
[related_mui] [varchar] (100)
[date_started] [smalldatetime]
[date_ended] [smalldatetime]
[date_entered] [datetime]
[deleted] [tinyint],
[rank] [int]
data in column [mui] is repeated as the table has more than one entries for the same [mui],
The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui]
The recordset expected should be something like this.

[mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004'
null,rm789,'ar','12-1-2003',26-2-2004'
null,rm999,'xy','14-12-2002',12-2-2004'
m777,rm889,'pr','12-12-2004',12-12-2004'
null,rm785,'yy','1-10-2002',12-12-2004'
m888,rm345,'pr','2-8-2003',12-12-2004'
null,rm345,'tt','30-7-2002',12-12-2004'

I have tried Unions and temporary table inserts.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Select Rows Based On One Distinct Column

Apr 18, 2014

--------------------------------------------------
SalesOrder-ItemName-Price-Category
-------------------------------------------------
01-Camera-100-Electronic
01-Memory-4GB-10-Memory
01-Battery-5-Battery
02-Keyboad-10-Accessories
02-Mouse-5-Accessories
03-CPU-300-Hardware
03-Motherboad-400-Hardware

From above rows i would like to select rows based on one distinct column SalesOrder, i want output like below.

-----------------------------------
SalesOrder-ItemName-Price-Category
-----------------------------------
01-Camera-100-Electronic
02-Keyboad-10-Accessories
03-CPU-300-Hardware

CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, Category VARCHAR(100))

[Code] ......

View 2 Replies View Related

SELECT INTO A New Table All Columns Based On DISTINCT Value Of One Column

Oct 31, 2014

‘Trying to SELECT INTO a new table all columns of a table based on a DISTINCT value of one column so for example:

SELECT *
INTO new_table
FROM old_name
WHERE old_table.column IS DISTINCT’

View 4 Replies View Related

Trying To Add A NON-DISTINCT Field To A DISTINCT Record Set In A Query.

Mar 12, 2007

I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks

View 2 Replies View Related

Null Value In Column In Select Query

Mar 18, 2015

Suppose I have added a xyz bit column in mytable. From now onwards new values inserted in mytable will have 0 or 1 in xyz column but the values that were previously stored will have NULL value in xyz column.

Now I want to write a query in which I will not give xyz parameter always like it can be null or value.

select * from mytable
where class='something'
and xyz is null

or

select * from mytable
where class='something'
and xyz = 1

Now how to write same query for both cases.

View 9 Replies View Related

Howto Add More Column Into Select Record?

Aug 16, 2006

hi, good day,

i have try following to get the records of the existing folder using :


insert into my_table
exec xp_dirtree 'c: emp'


for example , it give the result

subdirectory depth
--------------------
another 1
TEST 1
txtfiles 1
anotherSub 2



my question is , can i have add another column maindirectory into this existing result ?
and my expected result would like to be as follow

main directory subdirectory depth
--------------------------------------

another another 1
TEST TEST 1
txtfiles txtfiles 1
TEST anotherSub 2


is it possible ? thanks for guidance

View 5 Replies View Related

How To: Mutiple Select Statements Where 2nd Uses Column Value For Each Record From The First

Jul 16, 2006

To clarify, I'm trying to create a hiearchial grid that will display projects and their sub projects.

The page the sproc is servicing is a search page where a user can display all projects by a customer or they can enter in a projectID and return info that way.  Thus you'll see If else in what I have listed below.  I highlighted in red the 2nd select statement I'm trying to get to run.  One problem is I'm getting an error:

Msg 156, Level 15, State 1, Procedure sp_GetProjects, Line 33

Incorrect syntax near the keyword 'ELSE'.

Think this has something to do with me trying to run 2 select statements in an IF.  Secondly, I'm not sure exactly what syntax I need to use to accomplish what I'm trying to do.  Again, the first select statement is creating a table with all projects that match the passed custID, the second select statement I'm trying to generate a 2nd table that will take the projectID for each row returned to the first table and find all rows with the same projectID's in the BillGroup table.

 I need these two tables returned to a dataset I'm populated with a sqldataadapter in .net so I can create a relationship between the two tables and then bind to a grid.

 

@custID nchar(25) = NULL,

@projectID bigint = NULL

IF @custID != 'null'

   SELECT p.projectID, p.Description FROM Projects p

    WHERE custID = @custID



     SELECT * FROM Projects p

     JOIN dbo.BillGroups bg ON p.projectID = bg.projectID



ELSE IF @projectID != -1

   SELECT projectID, Description FROM Projects



   WHERE projectID = @projectID

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here



END

View 3 Replies View Related

Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?

Jul 6, 2007

Hi, I have the following script segment which is failing:

CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))

INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache



When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."



Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.

The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.

Any ideas?

View 2 Replies View Related

How To Show Distinct Rows Of The Column Of The Dataset And Number Of Distinct Rows Of That Column

Mar 29, 2007

suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"

I want's some thing like

field1 rowcount
aaa 5
bbb 6

View 1 Replies View Related

Count By Distinct Value When All Parts Not Null

Mar 12, 2007

Hello,

I'm fairly new to SQL as so am looking for help with an ad hoc query. The data in focusing on is from table named APs:

FileNo Type PartNo Completed
6 Northbound 1 03/03/2007
6 Northbound 2 NULL
6 Other 1 NULL
6 Other 2 NULL
20 Proof 0 19/07/2006
20 TCP 0 21/07/2006
24 40-Day 1 16/01/2006
24 40-Day 2 16/03/2006
24 Other 0 NULL
44 Northbound 1 16/01/2006
44 Northbound 2 16/06/2006
44 Northbound 3 16/12/2006
44 Northbound 4 01/01/2007

I've tried variations on a SELECT statement like below but have been unable to find a way to count only those types per fileNo that have all partNo completed (and to count all types per fileNo with a partNo of 0 and a completed date as they have no parts):

SELECT [FileNo], COUNT(DISTINCT [Type]) AS CountOfAPs
FROM APs
WHERE (completed IS NOT NULL)
GROUP BY [File]

Total count should be: 4

View 9 Replies View Related

Correlated Subquery With Distinct Record

Jul 20, 2005

record_id Status Due_date549In Progress2004-06-02 00:00:00.000549Not Started2004-06-07 00:00:00.000549Not Started2004-06-08 00:00:00.000549Waiting 2004-05-31 00:00:00.000549Waiting 2004-06-04 00:00:00.000550Completed2004-05-05 00:00:00.000551Completed2004-05-06 00:00:00.000551Completed2004-05-07 00:00:00.000551Completed2004-05-10 00:00:00.000551Not Started1900-01-01 00:00:00.000552Not Started1900-01-01 00:00:00.000Hi I have this table with 3 columns.. What I want isDistinct(record_id),max(due_date) and Status.. I tried thisselect distinct(record_id),status,(due_date) from table1 where(due_date) in(select max(due_date) from table1 as A where a.record_id=record_idand a.due_date is not null group by a.record_id,status)So the result that I want isRecord Status Max(due_date)549Not Started2004-06-09 00:00:00.000550Completed2004-05-05 00:00:00.000551Completed2004-05-10 00:00:00.000Any help is appreciated..ThanksAJ

View 3 Replies View Related

Distinct Count With Null Values (grand Total)

May 9, 2007

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of pulications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
--------------
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years


For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11


Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

View 1 Replies View Related

Distinct Count With Null Values (grand Total)

May 10, 2007

Hello,

I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of publications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years


For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11


Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

View 2 Replies View Related

How To Create An Copy Of A Certain Record Except One Specific Column That Must Be Different &&amp; Insert The New Record In The Table

Sep 1, 2006

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

View 6 Replies View Related

Transact SQL :: Distinct By One Column By Selecting Multiple Column?

Jul 17, 2015

I have a SQL Query issue you can find in SQL Fiddle

SQL FIDDLE for Demo

My query was like this

For Insert
Insert into Employee values('aa', 'T', 'qqq')
Insert into Employee values('aa' , 'F' , 'qqq')
Insert into Employee values('bb', 'F' , 'eee')
Insert into Employee values('cc' , 'T' , 'rrr')
Insert into Employee values('cc' , 'pp' , 'aaa')
Insert into Employee values('cc' , 'Zz' , 'bab')
Insert into Employee values('cc' , 'ZZ' , 'bac')
For select
select col1,MAX(col2) as Col2,Max(Col3) as Col3
from Employee
group by Col1

I supposed to get last row as 

    cc  Zz  bab

Instead I am getting 

  cc  Zz  rrr 

which is wrong

View 8 Replies View Related

Counting Distinct Records Of Column 1 With A Certain Value In Column 2

Feb 6, 2008

Ok, so I need to count the Distinct records from column 1 in which there is not a true value in any of the records for that distinct column 1 number. Here is a short example of my records:dbo_dbWafer_Slicing


dbo_dbWafer_Slicing



WaferID
SawDate
SawRunNumber
   Pass





03-157.05    
1/8/2008 9:54:00 AM    
03-157
0




03-157.03
1/8/2008 9:53:00 AM    
03-157
-1




03-157.04
1/8/2008 9:53:00 AM    
03-157
0




03-157.02
1/8/2008 9:52:00 AM    
03-157
-1




03-157.01
1/8/2008 9:50:00 AM    
03-157
-1




03-165.06
1/4/2008 10:46:00 AM    
03-165
0




03-165.07
1/4/2008 10:46:00 AM    
03-165
0




03-165.04
1/4/2008 10:45:00 AM    
03-165
0




03-165.05
1/4/2008 10:45:00 AM    
03-165
0




03-165.02
1/4/2008 10:44:00 AM    
03-165
0




03-165.03
1/4/2008 10:44:00 AM    
03-165
0




03-165.01
1/4/2008 10:43:00 AM    
03-165
0





 So, how many Distinct SawRunNumbers had no passing wafers? In trying to do this I've come up with:"SELECT COUNT(DISTINCT SawRunNumber) AS BouleCount FROM dbWafer_Slicing WHERE (SawDate >= @MinDate) AND (SawDate <= @MaxDate) AND (Pass = 1) HAVING (COUNT(DISTINCT WaferID) > 0)" but that doenst work. It still counts records where pass = 0 for distinct SawRunNumbers even if one record within that SawRunNumber is passing. From the above sample data I should get a result of 1 not 2 or 3. Can I do this with this set of data? I'm using SQL Server 2005 EE.Thanks for your help. 

View 6 Replies View Related

Cannot Insert The Value NULL Into Column 'OrderID' -- BUT IT IS NOT NULL!

Apr 2, 2007

I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
 

 
 

Server Error in '/' Application.


Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:



Line 89: sContact.Phone = sPhone.Text.Trim
Line 90: sContact.Email = sEmail.Text.Trim
Line 91: sContact.Save()
Line 92:
Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb    Line: 91 Stack Trace:



[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102



Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

View 8 Replies View Related

DB Engine :: Not Able To Make Column To Null Value From Not Null

May 13, 2015

It's giving me an error while I'm trying to change column value from not null to null..

'tblid' table
- Unable to modify table.  

Cannot insert the value NULL into column 'ValidID', table 'Xe01.dbo.Tmp_tblid'; column does not allow nulls. INSERT fails.

The statement has been terminated.

View 4 Replies View Related

Column Locked In Record Even Though No One Accessing Record

Mar 18, 2008



Hello. I have a database with a record that has two columns locked. descrip1 and descrip2. they are both nvarchar(max) columns. These are the only two columns of the record that remain locked. I am certain no user is accessing the record. I have even moved a backup of the database to my testing computer and the lock still exists. How do I remove the lock from these two columns on that particular record.

I can edit these two columns on other records. I have researched "Unlock" on MSDN but it doesn't seem to apply to t-sql. Any help would be greatly appreciated.

Thanks. Gary.

View 4 Replies View Related

Inserting Null Into A Record

Sep 8, 2007

I am simply trying to use SQLCommand in .net to insert a record into a SQL Server 2005 table. There are 2 fields being pulled from a user input for that could have no values selected. In this case I want to insert a null value into the record. I get an error that Null is no longer supported and that I should use System.DBNull, but that can't be used as an expression.
 How do I do this?
Thank you in advance.

View 1 Replies View Related

SQL Select DISTINCT?

Oct 16, 2006

OK I have a Forum on my website make up of 3 tablesTopisThreadsMessageI show a list of the 10 most recent Changed Threads.  My Problem is that my Subject field is in the messages Table, IF I link Threads to Messages then try to use Select Disticnt I get mutliple Subject fields as the messsges are not unique (obvisally) So I want to get the top 10 Threads by postdate and link to the Messages table to get the Subject headerAny help? Or questions to explain it better?

View 5 Replies View Related

SELECT Distinct Help

Apr 19, 2007

Hello Everyone
Hopefully someone can help me create a SQL statement for this.
I need the ff: fields
Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE, Specialty
Let say I have a table.
Prov_ID, Record_ID, PROV_NAme,     LOC_city,   LOC_Zip_CODE.    Specialty1000      999       Mike James      Plano       75023         Internal Medicine1000      998       Mike James      Allen       75021         Internal Medicine3333      700       John Smith      Arlington   70081         Dermatologist3333      701       John Smith      Dallas      72002         Dermatologist2222      630       Terry Walker    Frisco      75001         Optalmologist2222      632       Terry Walker    Dallas      76023         Optalmologist4444      454       Tim Johnson     San Anontio 72500         Internal Medicine 4444      464       Tim Johnson     Frisco      72660         Internal Medicine 
I want to select only "one" instance of the provider it doesnt matter what is selected
either the first address or the second address.
It should show
Prov_ID, Record_ID, PROV_NAme,     LOC_city,   LOC_Zip_CODE.   Specialty1000      999       Mike James      Plano       75023         Internal Medicine3333      700       John Smith      Arlington   70081         Dermatologist2222      632       Terry Walker    Dallas      76023         Optalmologist4444      464       Tim Johnson     Frisco      72660         Internal Medicine 
And yes, the table is not Normalized..Is there anyway I could get away with it without having to normalize?
Thanks
Lorenz

View 4 Replies View Related







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