Getting Unique Rows From The Resultset
Nov 15, 2006
Hi,
I am trying to do a join which involves more than 3 tables. One is a parent table and the other two table have 1:n relationship with that parent table.
But duplicate records are being returned in the resultset. How do I eliminate duplicate records?
This is my query.
SELECT table1.* from table1 left outer join table2 on table1.id=table2.id
left outer join table3 on table1.id=table3.id
I tried doing DISTINCT here but with no success.
SQL with distinct clause.
SELECT distinct table1.id, table1.* from table1 left outer join table2 on table1.id=table2.id
left outer join table3 on table1.id=table3.id
This is the error I get:
The text data type cannot be selected as DISTINCT because it is not comparable
Please help.
View 4 Replies
ADVERTISEMENT
Nov 19, 2001
Hello all,
I'm trying to number rows in a resultset by a grouping.
e.g. if my data looks like this:
personname persondata
----------- -----------------
person1 data
person1 more data
person1 other data
person2 stuff
person2 more stuff
person2 even more stuff
person2 lots of stuff
person2 last bit of stuff
person3 info
person3 more info
I want it to return this, with x as a sub-numbered value (like an identity for each grouping):
x personname persondata
-- ----------- -----------------
1 person1 data
2 person1 more data
3 person1 other data
1 person2 stuff
2 person2 more stuff
3 person2 even more stuff
4 person2 lots of stuff
5 person2 last bit of stuff
1 person3 info
2 person3 more info
Any ideas?
Thanks,
Dan
View 2 Replies
View Related
Feb 26, 2004
Hi,
I have problem with query/sp which need to return one resultset with strucure like:
summary row1
detail row1
detail row2
detail row3
summary row2
detail row4
detail row5
...
Tables:
T_HW (HW_ID INT, CatID INT, other cols (NVARCHARs, INTs, DATETIMEs, NTEXTs etc.)
T_Category (CatID INT, Manufact NVARCHAR (100), other cols)
The output I need is ( '|' means cols separator) :
Manufact1 | No of recs from HW for this Manufact | nulls ...
null | null | HW1 from Manufact1 details ...
null | null | HW2 from Manufact1 details ...
Manufact2 | No of recs from HW for this Manufact | nulls ...
null | null | HW3 from Manufact2 details ...
...
it needs to be one result recordset
Thx in advance,
MST78
View 4 Replies
View Related
Feb 2, 2008
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. Please provide separate solutions for SqlServer2000/2005.
I have four tables namely – Forums,Topics,Threads and Messages in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-
SELECT Forums.forumid AS ForumID,Topics.topicid AS TopicID,Topics.name AS TopicName,LastPosts.date as LastPostDate,LastPosts.author AS Author,
(select count(threadid) from Threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid
LEFT OUTER JOIN
(
SELECT Topics.forumid,Threads.topicid,Messages.date,Messages.author FROM Topics
INNER JOIN Threads ON Topics.topicid = Threads.topicid
INNER JOIN Messages ON Threads.threadid=Messages.threadid
WHERE Messages.date=(SELECT MAX(date) FROM Messages WHERE Messages.threadid = Threads.threadid)
) as LastPosts
ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid
Whose result set is as below:-
forumid
topicid
name
LastPostDate
author
NoOfThreads
1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2
1
1
Java Overall
2008-01-27 14:46:41.000
c@b.com
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
2008-01-27 15:12:51.000
p@q.com
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0
I want the result set as follows:-
forumid
topicid
name
LastPostDate
author
NoOfThreads
1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
2008-01-27 15:12:51.000
p@q.com
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the message) from the Messages table as shown above.
When I use the query by using only three tables namely Forums,Topics and Threads, I get the correct result. The query is as:-
SELECT Topics.forumid AS ForumID,Forums.name AS ForumName,Topics.topicid as TopicID,Topics.name as TopicName,
LastPosts.author AS Author,LastPosts.lastpostdate AS LastPost,
(select count(threadid) from threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN (
SELECT Topics.forumid, Threads.topicid, Threads.lastpostdate, Threads.author FROM Threads
INNER JOIN Topics ON Topics.topicid = Threads.topicid
WHERE Threads.lastpostdate IN (SELECT MAX(lastpostdate) FROM threads WHERE Topics.topicid = Threads.topicid)
) AS LastPosts
ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid
Whose result set is as:-
ForumID
ForumName
TopicID
TopicName
Author
LastPost
NoOfThreads
1
Developers
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
Developers
2
JSP
NULL
NULL
0
1
Developers
3
EJB
NULL
NULL
0
1
Developers
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
Developers
5
AWT
NULL
NULL
0
1
Developers
6
Web Services
NULL
NULL
0
1
Developers
7
JMS
NULL
NULL
0
1
Developers
8
XML,HTML
NULL
NULL
0
1
Developers
9
Javascript
NULL
NULL
0
2
Database
10
Oracle
NULL
NULL
0
2
Database
11
Sql Server
NULL
NULL
0
2
Database
12
MySQL
NULL
NULL
0
3
Desginers
13
CSS
NULL
NULL
0
3
Desginers
14
FLASH/DHTLML
NULL
NULL
0
4
Architects
15
Best Practices
NULL
NULL
0
4
Architects
16
Longue
NULL
NULL
0
5
General
17
General
NULL
NULL
0
The scripts for creating the tables and inserting test data is as follows in an already created database:-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__35BCFE0A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__35BCFE0A
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__34C8D9D1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__34C8D9D1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Messages__thread__33D4B598]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Messages] DROP CONSTRAINT FK__Messages__thread__33D4B598
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Messages]
GO
CREATE TABLE [dbo].[Forums] (
[forumid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topics] (
[topicid] [int] IDENTITY (1, 1) NOT NULL ,
[forumid] [int] NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Threads] (
[threadid] [int] IDENTITY (1, 1) NOT NULL ,
[topicid] [int] NOT NULL ,
[subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[replies] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Messages] (
[msgid] [int] IDENTITY (1, 1) NOT NULL ,
[threadid] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[date] [datetime] NULL ,
[message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums] ADD
PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
PRIMARY KEY CLUSTERED
(
[topicid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
PRIMARY KEY CLUSTERED
(
[threadid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Messages] ADD
PRIMARY KEY CLUSTERED
(
[msgid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
FOREIGN KEY
(
[forumid]
) REFERENCES [dbo].[Forums] (
[forumid]
)
GO
ALTER TABLE [dbo].[Threads] ADD
FOREIGN KEY
(
[topicid]
) REFERENCES [dbo].[Topics] (
[topicid]
)
GO
ALTER TABLE [dbo].[Messages] ADD
FOREIGN KEY
(
[threadid]
) REFERENCES [dbo].[Threads] (
[threadid]
)
GO
------------------------------------------------------
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum');
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
insert into messages(threadid,author,date,message) values(1,'a@b.com','1/27/2008 2:44:39 PM','Where can I find Java tutorials.');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:45:28 PM','Please visit www.java.sun.com');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:46:41 PM','Do a Google serach for more tutorials.');
insert into messages(threadid,author,date,message) values(2,'x@y.com','1/27/2008 2:48:53 PM','Please provide some Beginner tutorials.');
insert into messages(threadid,author,date,message) values(3,'p@q.com','1/27/2008 3:12:51 PM','How many finds of layout managers are there?');
insert into messages(threadid,author,date,message) values(2,'l@m.com','2/2/2008 1:06:06 PM','Search on Google.');
View 5 Replies
View Related
Oct 10, 2012
I am creating a simple SSRS table report through Report Builder. My dataset is looking for the stored procedure . When I execute the Stored procedure through SSMS I get resutset for certain parameters. I execute the dataset (Store procedure) through query designer in dataset properties and I get results back. But when I try to run the report and see the preview, I do not get any results displayed. I been looking on the same issue form last 3-4 days and have not found any clue.
Following is the stored procedure I am using. Also I am passing multivalued parameter through report as well, and I am using spilt function to seperate the libraryid I am reading from parameter values. This works fine. I have similar kind of four other reports and with different stored procedure which exactly follow the same method , like multivalue parameters and other criteria are also very similar. All other reports works just fine.. This perticular report has issue for displying results, following is the stored procedure I am using
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
View 4 Replies
View Related
Jun 15, 2004
Is it possible to use the DISTINCT clause on just one field in the SELECT statement?
The following SQL statement causes an error:
SELECT DISTINCT appt.ref, appt.notes FROM Appointments appt
...because DISTINCT can't be used on the notes field as it of type 'text'.
How can I focus the DISTINCT keyword on just the ref field?
(I know ref is the primary key, so this example wouldn't need the DISTINCT keyword, but I've simplified a much more complex statement)
Paul
View 6 Replies
View Related
Jan 25, 2000
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique.
How do I filter out only the duplicate zips.
Randy Garland
View 2 Replies
View Related
Jun 18, 2004
Hi,
Following is my table:Bets
BetId GameID
500 108
500 109
501 108
501 109
501 110
502 108
502 109
I want BetId 500 and 502 to be returned as result if i give select
criteria where game id = 108,109.
Pls.Note: It should not return BetId 501 in the result, since it belongs to different combination(108,109,110).
Similarly if i give, select criteria where game id =(108,109,110) it should return
BetId 501.not the 500 and 502..which is different combination..
Hope i clarified my problem..pls help me in this regard.Thanks a lot...
View 2 Replies
View Related
Jun 3, 2008
I have the following sql:
SELECT DISTINCT patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE (sub_categoryID = 4 OR patient_record.allocated = 4)
AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5)
GROUP BY patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date
This brings up duplicate records, my aim is to bring distinct records, now if I take out the other returned fields after patientID
and using the following sql:
SELECT DISTINCT patient.patientID
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE (sub_categoryID = 4 OR patient_record.allocated = 4)
AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5)
GROUP BY patient.patientID
This bring up distinct results, but I need to retrieve the other fields from the database i.e. patientFirstName and patientLastName
Please can you help.
View 2 Replies
View Related
Sep 21, 2006
How would I get the unique email addresses and its associated row of data from a SQL Server table that has no unique fields defined? If there is a duplicate email address then only show the first one and not the other rows with the same email address. Example table and data UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997623 KAIS-22 A.KAIS-22@SampleISD.org997624 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.ZKAIS-22@SampleISD.org997627 KAIS-22 AX.KAIS-22@SampleISD.org Result UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.KAIS-22@SampleISD.org Thanks
View 13 Replies
View Related
Oct 4, 2006
I am a beginner at SQL so thanks ahead of time.....How do I get unique rows based on studentID? Distinct and group by don't seem to workDESIRED RESULTSStudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 640484566 TERE BALDERAZCURRENT SQL AND RESULTS.....SELECT ClassRosterRecID, StudentDataRecID, StudentDataKey, StudentID, FirstName, LastName, CurrentGrade, Gender, Ethnicity, EconDisadvantaged, TitleI, Migrant, LEP, Bilingual, ESL, SpecialEducation, GiftedTalented, AtRisk, CareerTech, Dyslexia, LastName + ', ' + FirstName AS LastNameFirstName, EconDisadvantagedSort, TitleISort, MigrantSort, LEPSort, BilingualSort, ESLSort, SpecialEducationSort, GiftedTalentedSort, AtRiskSort, CareerTechSort, DyslexiaSort, DistrictID, CampusID FROM vClassDemographicsDetail WHERE (DistrictID = '057910') AND (CampusID = '057910101') AND (LastName LIKE '%BALDERAZ%')StudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 634565491 MARINA BALDERAZ 634565491 MARINA BALDERAZ 640484566 TERE BALDERAZ640484566 TERE BALDERAZ640484566 TERE BALDERAZ
View 1 Replies
View Related
Mar 20, 2008
I got the following query:SELECT TOP (8) ext.extID, ext.Quote, ext.sourceTitle, ext.extRating, gf_game.gameID, gf_game.catID, gf_game.URL, gf_game.TitleFROM gf_game_ext AS ext INNER JOIN gf_game ON gf_game.gameID = ext.gameIDWHERE (ext.Approved = 1)ORDER BY ext.extID DESC which is e.g. producing this output: 6000 -some text- Title 90 1960 2 tom-cl tom cl5999 -some text- title 90 1960 2 tom-clcl asdf5998 -some text- title 90 1959 2 tom-cl-cl asdfWhat I'd like to do now is to filter out the duplicate GameIDs (= 1960) so that just one unique row with the gameid 1960 is remaining. If I put in a SELECT DINSTINCT TOP(8) it just counts for the table ext, but I need it to count for gf_game.gameID - is that possible?Thanks a lot!
View 9 Replies
View Related
May 17, 2007
Using DISTINCT with SELECT have effect only for one column.
But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work.
Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows
Select DISTINCT a4 From Y ---> " 85 rows
Any thoughts?
Jorge3921
View 14 Replies
View Related
Jul 24, 2013
I have a table in Access 2007 that has about 30 field names and I want to have a count of how many unique rows there are in each field. I want to have these results put into another table that will just have the field name and then the count of how many unique rows there are.
I have code in VBA that will loop through my SQL and change out the field name, but I can't seem to get the SQL right before I can start looping it. For just one field name this would be what I have to count the unique names...
So far I have this:
INSERT INTO newtable
COUNT(*) FROM (SELECT Raw_Table.FieldName, COUNT(Raw_Table.FieldName) AS CountOfFieldName
FROM Raw_Table
GROUP BY Raw_Table.FieldName);
And its not going too well.
View 1 Replies
View Related
Sep 18, 2007
As a beginner i am having trouble with this.
i have two different tables , both have a name column, nvarchar datatype.
I would like to select from table B all the rows which contain a name which is not in table A.
Then insert these rows, into table A
tried a few different ways & just keep getting strange errors that refer to courier font ??
SQL Team Your my Hero !
View 11 Replies
View Related
Nov 3, 2006
i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.
MemberID + Month + Year should ne unique in Temp table
View 1 Replies
View Related
Mar 18, 2007
Hi there, im still learning SQL so thanks in advance.I have a table with columns of customer's information, [customerID], [customerFirst], [customerLast], , [program] ... other columns ... There will be entries where there can be duplicate customerFirst and customerLast names. I would like to just return a single entry of the duplicate names and all associated row information. IE: [customerID], [customerFirst], [customerLast], [ email], [program] 01 Bill Smith bill.smith@hotmail.com ymca 02 Bill Smith bill.smith@hotmail.com Sports 03 jon doe jon.doe@hotmail.com AAA 04 jon doe jon.doe@hotmail.com Ebay 05 Paul Sprite paul.sprite@hotmail.com Rec Desired Returned result: 01 Bill Smith bill.smith@hotmail.com ymca 03 jon doe jon.doe@hotmail.com AAA
05 Paul Sprite paul.sprite@hotmail.com Rec So in my code i have this:dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); dAdapter.Fill(pocDS, "Data Set"); However this is throwing up an error when i build the app: An expression of non-boolean type specified in a context where a condition is expected, near ')'.
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:
An expression of non-boolean type specified in a context where a
condition is expected, near ')'.
Source Error:
Line 52: //dAdapter = new SqlDataAdapter("SELECT DISTINCT * FROM [Poc_" + suffix + "] ORDER BY [CustomerLastName]", cnStr); Line 53: dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); Line 54: dAdapter.Fill(pocDS, "Data Set");Line 55: Line 56: //Dataset for name comparison 1: Can someone explain to me why this error is happening?2: Can soemone confirm that my intentions are correct with my code?3: If I'm completely off, can someone steer me in the right direction?Thanks alot!-Terry
View 12 Replies
View Related
Apr 3, 2000
I have 4 rows which are exactly the same. I want to delete one row but i do not have any unique identifing columns. How should i delete that row ?
View 1 Replies
View Related
Oct 25, 2004
We are running the following query, which has a unique index on Table_2 (col1 and sys1), and Column col1 from Table_1 is unique.
select top 100 s.*, x.col1
from Table_1 s
left outer join Table_2 x
on x.col1 = s.col1 and x.sys1 = 'SYSTEM0'
Unfortunately this query returns duplicate rows. And every time the result is different
But once we dbcc dbreindex the unique index on Table_2, the result will not have any dups.
Any ideas?
Thanks
Steve
View 1 Replies
View Related
Mar 10, 2012
how to join these 3 tables but there are duplicate rows in the results. How can I get all the unique rows to only show up once.
My query is:
SELECT tbl_rate.rate_ID, tbl_rate.rate_site_IDref, tbl_rate.rate_rank_IDref, tbl_rate.rate_dollar, tbl_rate.rate_ot, tbl_rate.rate_dt, tbl_rate.rate_loa,
tbl_employee.employee_name, tbl_LEM.LEM_date, tbl_LEM.LEM_reg_hrs, tbl_LEM.LEM_ot_hrs, tbl_LEM.LEM_dt_hrs, tbl_LEM.LEM_tt_hrs, tbl_LEM.site_IDREF,
tbl_LEM.LEM_LOA, tbl_LEM.LEM_Expenseinfo, tbl_LEM.LEM_workorder, tbl_LEM.LEM_posted, tbl_LEM.LEM_Expense, tbl_LEM.LEM_tagnumberREF,
tbl_LEM.LEM_reg_rate, tbl_LEM.Lem_ot_rate, tbl_LEM.Lem_tt_rate, tbl_LEM.LEM_dt_rate, tbl_LEM.Lem_loa_rate, tbl_LEM.LEM_ID, tbl_LEM.LEM_equip_days
FROM tbl_rate INNER JOIN
tbl_employee ON tbl_rate.rate_rank_IDref = tbl_employee.employee_rankREF INNER JOIN
tbl_LEM ON tbl_employee.employee_ID = tbl_LEM.employee_IDREF
The results somehow need to be distinct by tbl_lems.lem_ID
View 4 Replies
View Related
Dec 8, 2013
I have a equipment table and the equipment has a coding for each place / location or custody it has had during its life. I need to select the latest (newest) row for each piece of equipment by getting the newest from_date and to_date field combination.The following is an example. I know how to get MAX date for one column but not with two columns (from and to DATES).
ID
NAME
FROM_DATE
TO_DATE
[code]...
View 1 Replies
View Related
Mar 20, 2015
We have a sorted data that looks like the first 3 columns below, and fourth colum is what I want to create)
Shift_start meal_break shift_endShift ID
1 0 0 1
0 0 0 1
0 0 1 1
1 0 0 2
0 0 0 2
0 0 1 2
I need to find a method to assign unique Shift IDS to rows that correspond to a single shift. For instance, the first shift would begin on the first row when shift_start flag is turned on, and end on the third row when shift_end flag is turned on.
Can I do this in SQL ?...some kind of grouping ?
View 2 Replies
View Related
Jan 14, 2015
I have this query and it works except for I am getting duplicate primary keys with unique column value. I want to combine them so that I have one primary key, but keep all the columns. Example:
Key column 1 column 2 column 3 column 4
A 1 1
A 2 2
B 2 3
B 5 5
it should look like:
A 1 1 2 2
B 2 3 5 5
Here is my query:
SELECT *
FROM [TLC Inventory].dbo.['2014 new$']
WHERE [TLC Inventory].dbo.['2014 new$'].mis_key LIKE '2%'
AND dbo_Product_Info#description NOT LIKE 'NR%'
AND dbo_Line_Info#description NOT LIKE 'OBSOLETE%'
Do I use a sum function?
View 7 Replies
View Related
Oct 25, 2005
My application is to capture employee locations.Whenever an employee arrives at a location (whether it is arriving forwork, or at one of the company's other sites) they scan the barcode ontheir employee badge. This writes a record to the tblTSCollected table(DDL and dummy data below).The application needs to be able to display to staff in a control roomthe CURRENT location of each employee.[color=blue]>From the data I've provided, this would be:[/color]EMPLOYEE ID LOCATION CODE963 VB002964 VB003966 VB003968 VB004977 VB001982 VB001Note that, for example, Employee 963 had formerly been at VB001 but wasmore recently logged in at VB002, so therefore the application is notconcerned with the earlier record.What would also be particularly useful would be the NUMBER of staff ateach location - viz.LOCATION CODE NUM STAFFVB001 2VB002 1VB003 2VB004 1Can anyone help?Many thanks in advanceEdwardNOTES ON DDL:THE BARCODE IS CAPTURED BECAUSE THE COMPANY MAY RE-USE BARCODE NUMBERS(WHICH IS DERIVED FROM THE EMPLOYEE PIN), SO THEREFORE THE BARCODECANNOT BE RELIED UPON TO BE UNIQUE.THE COLUMN fldRuleAppliedID IS NULL BECAUSE THAT PARTICULAR ROW HAS NOTBEEN PROCESSED. THERE ARE BUSINESS RULES CONCERNING EMPLOYEE HOURSWHICH OPERATE ON THIS DATA. ONCE A ROW HAS BEEN PROCESSED FORUPLOADING TO THE PAYROLL APPLICATION, THE fldRuleAppliedID COLUMN WILLCONTAIN A VALUE. IN THE PRODUCTION SYSTEM, THEREFORE, ANY SQL ASREQUESTED ABOVE WILL CONTAIN IN ITS WHERE CLAUSE (fldRuleAppliedID IsNULL)if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSCollected]GOCREATE TABLE [dbo].[tblTSCollected] ([fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldTimeStamp] [datetime] NULL ,[fldRuleAppliedID] [int] NULL ,[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOINSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (963, 'VB001', '2005-10-18 11:59:27.383', 45480)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (963, 'VB002', '2005-10-18 12:06:17.833', 45480)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB001', '2005-10-18 12:56:20.690', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB002', '2005-10-18 15:30:35.117', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB003', '2005-10-18 16:05:05.880', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB001', '2005-10-18 11:52:28.307', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB002', '2005-10-18 13:59:34.807', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB001', '2005-10-18 14:04:55.820', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB003', '2005-10-18 16:10:01.943', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB001', '2005-10-18 11:59:34.307', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB002', '2005-10-18 12:04:56.037', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB004', '2005-10-18 12:10:02.723', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (977, 'VB001', '2005-10-18 12:05:06.630', 96879)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (982, 'VB001', '2005-10-18 12:06:13.787', 96697)
View 4 Replies
View Related
Mar 14, 2006
SSIS 2005
Ok, I have a task in SSIS that does the following and works:
1) Brings in a txt file
2) Using a conditional component, checks for a value in the row.
3) Based on the value, splits the row into one of 3 tables (Header, Maintenance, or Payment)
Here is a print screen of what I have so far which splits Header rows into it's own table, Maintenance rows into its own table, and Payment Rows into its own table:
http://www.webfound.net/qst_how_to_add_header_PK_and_FKs.JPG
Here is a print screen of the conditional split:
http://www.webfound.net/conditional_split.jpg
Please take a look at the txt file here before it's processed:
http://www.webfound.net/split.txt
http://www.webfound.net/rows.jpg
Notice that the pattern is a header row, followed by it's corresponding detail rows. The detail rows are either Maintenance or Payment rows.
I need to somehow during the Script component or some other way, to assign a unique HeaderID (PK) to each of the header rows and add that ID to it's corresponding Maintenance and Payment detail rows in their corresponding tables as a PK. The problem is
1) I don't know how to do this in the flow of the components as I have it now
2) How do I tell it to create a new Header ID and Header FKs for the detail rows based off of each new Header row?
In the end (much later on in my entire package), the goal is to be able to run a stored proc to join and select the Header and Details rows back into a final table so I can then do more processing such as split each header and detail rows into their own txt files, etc....I don't need to go into details why but just know that this is the goal, therefore I need to relate each header row with their corresponding detail rows that are split off into a MaintenanceRow and PaymentRowTable
View 2 Replies
View Related
Sep 21, 2015
I am trying to calculate the maximum sales per store where multiple stores exist in the same region (i.e there must be doubles of that row) in order to determine the most profitable store per that region.
E.g.
Store Sales
A 500
B 200
B 100
C 400
C 800
D 100
D 200
D 700
Looking for result set like:
B 200
C 800
D 700
View 3 Replies
View Related
Apr 18, 2014
write a query which retrieves only unique rows excluding some columns.
IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19393Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
In the above table project number and id shouldn't be considered and I should get the unique rows considering rest of columns and sorted based on date. Expected result is
IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
View 4 Replies
View Related
Nov 9, 2015
We write to a log file each time a job runs. We give each job a unique batchid. I want to compare the run times of each step/record between two batch ids: '20150101888' and '20150101777'. Column Mins in the number of minutes each step ran. I am having trouble comparing the rows that have generic process and stepname – Trans Switch in this example. A new process within a batchid starts with a 'XX', 'Load'.
So I want to compare CA's Trans to CA's Tran Switch and ER's Trans Switch to ER's, etc. There can be multiple Trans Switch per process. There should be the same number between each batch, but no guarantees that something might change. Also, Trans Switch is not the record right after the new process (CA, ER) in production.
I have just made a very simplified example.
/**
Want to compare 20150101888 to 20150101777 and end up with this result set. Notice that the duplicate process/step within a process has the process (CA and ER in this example) and a sequential number added to it: 'CA Trans 1'. Need this to pull out the largest time differences.
Time difference, process, step, mins1, mins2, batchid1, batchid2
-6, CA, Load, 17, 23, 20150101888, 20150101777
0, CA Trans 1, Switch, 8, 8, 20150101888, 20150101777
-6, CA Trans 2, Switch, 9, 15, 20150101888, 20150101777
-4, ER, Load, 7, 11, 20150101888, 20150101777
-4, ER Trans 1, Switch, 7, 11, 20150101888, 20150101777
**/
[Code] ....
View 4 Replies
View Related
Jul 26, 2015
I have the following query:
Select p.Id [SenderId], p.Username, up.PhotoId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage,
p.LastLoggedIn, p.LoggedIn
[Code] ....
The above query returns me all messages (inbox functionality) that have been sent to mr.ReceipientId, the issue I have is when I send another email to the recipient the readdate field will be null, and the other emails linked to the recipient which have also been sent via me will have a readdate date. This causes duplicate rows to appear due to the case statement, I'm trying to figure out if / how it is possible to only display the one row per conversation and set newmessage to 1 if there is an un-read message otherwise show 0 ?
So instead of showing this:
2Person102015-07-26 17:07:24.9370
2Person112015-07-26 17:07:24.9370
I'm trying to get it to look like this:
2Person112015-07-26 17:07:24.9370
Or if we have no new mail (read date is not null) then it will appear like this, and I can confirm when there is no new mail it works as expected.
2Person102015-07-26 17:07:24.9370
View 1 Replies
View Related
Sep 8, 2006
i am getting the above error on my database i have 2 rows with the same info on and another 2 with the same info on. example:
ID username password
1 bob bob
1 bob bob
1 john john
1 john john
I know this is a fault with ms sql 2005 however how do i fix it?
Ive found this link which explains everything but how do i start a query. I tried clicking on new query and copying the code. What is table1 meant to be? the database is dbl.tbl_admin. It wont find my database.
Im not sure how to do it anyway.
I need to change it though as its my admin password and Ive given it out to web design companys
http://geekswithblogs.net/allensb/archive/2006/07/27/86484.aspx
Can some 1 read the above page and give me full instructions, I dont know what im doing thanks
info@uktattoostudios.co.uk
View 7 Replies
View Related
May 7, 2008
Hi there ...here comes a tricky one.
I have a database table which needs to make the Index "ParentREF, UniqueName" unique - but this fails because duplicate keys are found. Thus I now need to cleanup these duplicate rows - but I cannot just delete the duplicates, because they might have rows in detail tables.
This means that all duplicate rows needs an update on the "UniqueName" value - but not the first (valid) one!
I can find those rows by
SELECT OID, UniqueName, ParentREF, CreatedUTC, ModifiedUTC FROM dbo.CmsContent AS table0
WHERE EXISTS (
SELECT OID, UniqueName, ParentREF FROM dbo.CmsContent AS table1
WHERE table0.ParentREF = table1.ParentREF
AND table0.UniqueName = table1.UniqueName
AND table0.OID != table1.OID
)
ORDER BY ParentREF, UniqueName, ModifiedUTC desc
...but I struggle to make the required SQL (SP?) to update the "invalid" rows.
Note: the "valid" row is the one with the newest ModifiedUTC value - this row must kept unchanged!
ATM the preferred (cause easiest) way is to rename the invalid rows with
UniqueName = OID
because if I use any other name I risk to create another double entry.
Thanks in advance to whoever can help me
View 4 Replies
View Related
Jan 9, 2007
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this?
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
View 3 Replies
View Related
Jan 17, 2008
Hi,
I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.
View 7 Replies
View Related
