|
That causes error message 8120 in Microsoft SQL server (similar message in MS Access):
Column [Acc No] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
|
|
|
|
|
Yeah, slight typo there did it on the quick, will have to read it properly next time!
SELECT [Acc No] FROM table
GROUP BY [Acc No], State
HAVING COUNT(State) > 1
Sorry about that 
|
|
|
|
|
Create Table T1 (Col1 int, Col2 varchar(50))
Insert into T1 Select 1001,'WA'
Insert into T1 Select 1002,'NY'
Insert into T1 Select 1002,'NY'
Insert into T1 Select 1002,'NY'
Insert into T1 Select 1003,'CA'
Insert into T1 Select 1003,'CA'
Insert into T1 Select 1001,'CA'
Insert into T1 Select 1002,'NY'
select distinct(col1) from t1 group by col1,col2 having count(col1)<2
output will come one record.
because state only one record have multi state
|
|
|
|
|
Jammo gave this exact same answer 2 hours earlier and it was a lot more understandable. Try using the formatting tools for placing code, your answer will be much more readable!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Arun's answer is clearly different from Jammo's. And Jammo's SQL statement fails with an error message.
|
|
|
|
|
My bad - I did not look close enough at the answer.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Nice answer. +5
can I suggest that you try to format your code so that it is easier to read?
i.e.
SELECT DISTINCT(col1)
FROM t1
GROUP BY col1,col2
HAVING COUNT(col1) < 2
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Why do you expect "1002" to be among the output? Only "NY" is associated with "1002", though 4 times. But you said "multiple states".
|
|
|
|
|
select [Acc No],count(*) from table group by [Acc No] having count(*)>1
|
|
|
|
|
SELECT DISTINCT A.Acc_No,count(*)
FROM [Test].[dbo].[Table_2] A
JOIN [Test].[dbo].[Table_2] B
on A.Emp_state = B.Emp_state
group by A.Acc_No having COUNT(*)>1
|
|
|
|
|
Hi all, I'm trying to insert info from text boxes into an Access database using a stored procedure and it seems to some what work. The only problem is values from the text boxes are not entered into the database, instead the parameter names are inserted into the database. The following are how I named, set, and added all my parameters:
param = new OleDbParameter("@myvalue", value);
oCommand.Parameters.Add(param);
My stored procedure takes the following form:
INSERT INTO Table( field1,field2)
VALUES ('@value1', '@value2');
I have also tried naming, setting, and adding all my parameters using the below method which gave the same exact result as the above method:
param = new OleDbParameter();
param.ParameterName="@myvalue";
param.Value=value;
oCommand.Parameters.Add(param);
Please point out what the problem is if you can spot it, thanks.
modified 3-Aug-12 16:23pm.
|
|
|
|
|
INSERT INTO Table( field1,field2)
VALUES (@value1, @value2);
You don't need the quotes or they'll be interpreted as literal strings.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
Hi Mark, thanks for replying I've already tried using no quote, single and double quotes without much luck. However, I've just found out that since I'm using Access, I have to use square brackets. My problem is now solved.
modified 3-Aug-12 17:31pm.
|
|
|
|
|
Really? How bizarre. Years and years since I used it and I don't recall having to do that. Oh well, relearn something, err, old, every day...
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
mark merrens wrote: Really? How bizarre.
VALUE is a reserved keyword; it would be like having a variable called "SELECT".
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
ASPnoob wrote: VALUES ('@value1', '@value2'); ... param.ParameterName="@myvalue";
As mentioned, remove the apostrophes.
The names have to match as well.
|
|
|
|
|
i could not change a field in a table when table saved
when i want to change a field or add a field an error raised
appreantly i have right to change everything
could you solve my problem 
|
|
|
|
|
Yazdan Attarian wrote: when i want to change a field or add a field an error raised
Unless you share the exact error with details, it would be difficult for anyone to even guess out.
|
|
|
|
|
Yazdan Attarian wrote: could you solve my problem
Maybe, probably.... if you would actually describe a particular problem.
Yazdan Attarian wrote: an error raised
Does not in any way describe any sort of a problem. We are not mind readers.
Why is common sense not common?
Never argue with an idiot. They will drag you down to their level where they are an expert.
Sometimes it takes a lot of work to be lazy
Please stand in front of my pistol, smile and wait for the flash - JSOP 2012
|
|
|
|
|
This is a SSMS setting. Sorry I am currently running something that is tying my system up so I cannot find it under Tools\Options
|
|
|
|
|
Do it using SQL Script (like alter etc...) instead of using SSMS
Niladri Biswas
(Code Project MVP 2012)
|
|
|
|
|
Suppose I have two tables (Emp and EmpCopy) in sql server database with following data:
Case 1: Data in Emp and EmpCopy table present like below:
Table: Emp
Id Name
----------------------
1 Emp_Name
2 Emp_Age
3 Emp_City
Table: EmpCopy
Id Name
--------------------
1 Emp_Name
2 Emp_Age
Case 2: Data in Emp and EmpCopy table present like below:
Table: Emp
Id Name
--------------------
1 Emp_Name
2 Emp_Age
Table: EmpCopy
Id Name
---------------------
1 Emp_Name
2 Emp_Age
3 Emp_City
Now according to cases given above I want retrieve records from both the tables, just like given below
Case 1:
Id Emp_Column EmpCopy_Column
1 Emp_Name Emp_Name
2 Emp_Age Emp_Age
3 Emp_City NULL
Case 2:
Id Emp_Column EmpCopy_Column
1 Emp_Name Emp_Name
2 Emp_Age Emp_Age
3 NULL Emp_City
Please reply ASAP.
Thanks in advance.
|
|
|
|
|
Suppose you had a script that created those tables, and inserted some data in it; wouldn't that cost about just as much time as writing all those fields like that?
The advantage of a script would be that it's clearly to read, easy to see what is going on, easily validated using copy/paste, and you'd probably get more (usefull) answers - simply because more people tend to paste a piece of Sql and try it, than to read a long story.
Rohit Kesharwani wrote: Please reply ASAP.
If your boss planned in too little time to research, than that's HIS problem. Not mine, not yours.
To prove that point, I'll be fetching some coffee. I suggest you post your code in PRE tags, including one or two queries that you already tried. They needn't be good, we're not going to whine about it; it's just a good idea to have "proof" you tried something.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: To prove that point, I'll be fetching some coffee. I suggest you post your code in PRE tags, including one or two queries that you already tried. They needn't be good, we're not going to whine about it; it's just a good idea to have "proof" you tried something.
+5 purely for that!
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Hi,
i have a SQL database select Query which brings 60 columns from 10 tables via join. i need to fetch 300000(3 Lacs) rows from database. It takes 18-19 seconds to fetch 300000 rows. I want to decrease this execution time to 10-12 secs.
I used "DATABASE ENGINE TUNING ADVISER of SQL SERVER 2008 R2, it suggested me 2 create some Indexes and statistics. I have created all this, but could not improved the performance of my query.
can anybody suggest me right solution ???
This my query-
select
i.IssueId, i.IssueNumber, i.TeamId, t.Name AS TeamName, i.IssueStatus, p1.TextValue AS StatusText, p1.SortNumber AS StatusOrder, i.Substatus,
p2.TextValue AS SubstatusText, p2.SortNumber AS SubstatusOrder, i.CreatedBy, cc.FullName AS CreatedByFullName, i.CreatedAt, i.UpdatedBy,
cu.FullName AS UpdatedByFullName, i.UpdatedAt, i.ReportedBy, cReported.FullName AS ReportedByFullName, i.ReportedAt, i.Title, i.Description, i.Severity,
pSeverity.TextValue AS SeverityText, i.Priority, pPriority.TextValue AS PriorityText, i.IssueType, pType.TextValue AS TypeText, pType.SortNumber AS TypeOrder,
i.Subtype1, pST1.TextValue AS SubType1Text, pST1.SortNumber AS SubType1Order, i.Subtype2, pST2.TextValue AS SubType2Text,
pST2.SortNumber AS SubType2Order, i.ProjectId, pr.Name AS ProjectName, i.LocationId, lc.Name AS LocationName, i.DepartmentId, dp.Name AS DepartmentName,
i.Escalated, i.AssignedTo, ucAssign.FullName AS AssignedToFullName, i.AssignedAt, i.DueAt, i.ClosedAt, i.ClosedBy, udf.UDFString1, udf.UDFBool1, udf.UDFInt1,
udf.UDFUserId1, udfc1.FullName AS UDFUser1Name, udf.UDFPickList1, udfp1.TextValue AS UDFPickList1Text, udfp1.SortNumber AS UDFPickList1Order, t.TenantId,
uc.LoginEmail, uc.UserId, DATEDIFF(d, i.CreatedAt, GETDATE()) AS Age, i.AttachmentCount
FROM dbo.ITIssue AS i INNER JOIN
dbo.PicklistItem AS p1 ON i.IssueStatus = p1.PicklistItemId LEFT OUTER JOIN
dbo.PicklistItem AS p2 ON i.Substatus = p2.PicklistItemId INNER JOIN
dbo.PicklistItem AS pType ON i.IssueType = pType.PicklistItemId LEFT OUTER JOIN
dbo.PicklistItem AS pST1 ON i.Subtype1 = pST1.PicklistItemId LEFT OUTER JOIN
dbo.PicklistItem AS pST2 ON i.Subtype2 = pST2.PicklistItemId LEFT OUTER JOIN
dbo.PicklistItem AS pSeverity ON i.Severity = pSeverity.PicklistItemId LEFT OUTER JOIN
dbo.PicklistItem AS pPriority ON i.Priority = pPriority.PicklistItemId LEFT OUTER JOIN
dbo.Project AS pr ON i.ProjectId = pr.ProjectId LEFT OUTER JOIN
dbo.Location AS lc ON i.LocationId = lc.LocationId LEFT OUTER JOIN
dbo.Department AS dp ON i.DepartmentId = dp.DepartmentId INNER JOIN
dbo.TenantUser AS uc ON i.CreatedBy = uc.UserId LEFT OUTER JOIN
dbo.TenantUser AS uu ON i.UpdatedBy = uu.UserId LEFT OUTER JOIN
dbo.TenantUser AS uAssign ON i.AssignedTo = uAssign.UserId LEFT OUTER JOIN
dbo.Contact AS ucAssign ON uAssign.ContactId = ucAssign.ContactId LEFT OUTER JOIN
dbo.Contact AS cc ON uc.ContactId = cc.ContactId LEFT OUTER JOIN
dbo.Contact AS cu ON uu.ContactId = cu.ContactId LEFT OUTER JOIN
dbo.UDFValues AS udf ON udf.EntityId = i.IssueId LEFT OUTER JOIN
dbo.TenantUser AS udfu1 ON udfu1.UserId = udf.UDFUserId1 LEFT OUTER JOIN
dbo.Contact AS udfc1 ON udfu1.ContactId = udfc1.ContactId LEFT OUTER JOIN
dbo.PicklistItem AS udfp1 ON udfp1.PicklistItemId = udf.UDFPickList1 INNER JOIN
dbo.Team AS t ON t.TeamId = i.TeamId LEFT OUTER JOIN
dbo.TenantUser AS uRepored ON i.ReportedBy = uRepored.UserId LEFT OUTER JOIN
dbo.Contact AS cReported ON cReported.ContactId = uRepored.ContactId
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
