This is not really a question, maybe a start of discussion if this could be a feature in coming versions...
Let's say I have a table like this:
id text1 text2
int varchar varchar
1 abc ghi
2 abc ghi
3 def ghi
and I want to query using group by, I would write SELECT text1, text2 FROM table GROUP BY text1, text2
This is not too bad, but imagine I got several columns of text I need to query, then I need to put them in both places, SELECT and GROUP BY. If I do use functions like SUBSTRING or concatenation on the columns, I need to repeat these in the GROUP BY as well.
It would be nice if I could say "GROUP BY id", and since id is unique on the table it is obvious that all columns taken from this table are already grouped and can be queried without having to have them in group as well.
For functions this is of course only possible if all parts only refer to columns from this table, or absolute entries, like LEFT(varchar ,3) or int+1 or such.
With this example, the benefit is not visible: if I added the id to the GROUP BY, I get every single row of course. GROUP on unique is useless... It does make sense if you query another table and join in this one as a lookup, and the id gets "multiple" this way.
As I said, just an idea to make the GROUP BY section more transparent and avoid repeating sections!
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
Item Color Quantity -------------------- -------------------- -------------------------- Table Blue 10 Table Red 20 Table Yellow 30 Chair Blue 40 Chair Red 50
I'm wondering if there is a group state like this: Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item which returns this:
I created an ouput text file that was perfect until specs were revised today to add quarterly tax and deduction summations for each employee. (Expected turnaround time for new spec: today!)
So my employee record now has 8 records (which I expected) because I had to join it to the pay_summary table. Like this:
SELECT COALESCE(CONVERT(char(1),e.record_status),'') + COALESCE(CONVERT(char(4),'97 '),'') and many more fields from employees as e JOIN emp_taxes as t on e.employee_no = t.employee_no LEFT OUTER JOIN pay_summary AS p ON e.employee_no = p.employee_no
Where dateadd(d, 0, datediff(d, 0, p.dated)) BETWEEN '20061231' AND '20070401'
and e.record_status not like 'D' and t.tax_authority_type = 'F' and e.company_no = '2' and e.employee_no = t.employee_no
order by e.employee_no
There are 6 or so fields I intend to sum as records to go into the file. In another language, in order to get one record per employee_no, the syntax would be like this:
sort on employee_no footing at employee_no report field_a field_b field_c subtotal field_e field_f subtotal
in which case fields c and f are numeric and summed from the multiple pay_summary records.
What is the (most efficient) SQL command to give only one record per employee while summing all the detail records?
I want the sum of this to be Client 1 + Client 2 + Client 3 + Client 4 which is $100. But if I just do a sum it adds up each row. I also have them grouped together, so when it's shown, I only seen one of each item, but a sum still grabs all the duplicates.How can I go about getting the unique sum
I have a table in which a non-primary key column has a unique index on it. If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year Month Date
And the following row groups:
Region Product SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
I developed a main report containing numerous subreports. But now I'm trying to page break on these subreports and because these subreports all have sub-sub reports, I get this error when I try to run this report..The value '22' is invalid. Valid values are between '0' and '1'. (rrRenderingError)..I am grouping on uniqueidentifers and I do not get errors on subreports that have sub-subreports. How can I avoid this error and get these subreports to page break? (I don't get any errors if I remove the page breaks).
I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.
What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).
I have tried 2 statements:
#1: SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#)) GROUP BY tblTable1.Name, tblTable1.INDate ORDER BY tblTable1.Name;
#2: SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#)) UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));
Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.
Example:
NameINOUT John Smith1/1/20141/2/2014 John Smith1/1/2014(blank)
I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?
Hello everyone, I am new to ERWIN and I need helps from the experts outthere.We are using ERWin 4.1.2771 and have reversed engineered some MS SQLServer 2000 databases.The problem we are having is that we have a FK on a column to a tablewhere the PK of the referencing table is on another column (such as anidentity column). We have a unique index on the column in the PK tableand SQL Server allows you to build a FK reference even though thecolumn is not defined as the PK.Does anyone know how to create this type of FK within Erwin?Thank You
I am looking for advice on how to handle the following situation.Data being saved to a database table. Unique index on column in table. If the constraint is violated, what is the best way in alerting the user? Catch the exception and display error message to user? How do I know the exception is because of a unique index violation?
How can I assign or retrieve a unique ID from a msSQL table where there isn't a unique column.
Is there a hidden 'raw id' i can retrieve from msSQL for each record?
I'm not allowed to change the current database at all, but the developers who created it managed to use it without Unique IDs.... which doesn't help me coming from a mySQL background where UID's are kind of mandatory.
This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list. Can anyone shed any light on the problem? Thanks.
hi, In the below code, i am trying to insert the identity value into the 2nd column of the Symp_Quote table. Schema of the table Symp_Quote is as follows.
1st column data is coming from the select statement. but for 2nd column i have to insert identity or some unique value. to do that i was trying withe the following code, but it failed.
INSERT INTO Symp_Quote ( QuoteRevision_ID, DocumentSeq, DocumentDisplayName ) SELECT S.QuoteRevision_ID, IDENTITY (INT,1,1) AS DocumentSeq, T.Name, FROM Symp S INNER JOIN Trilogy T ON SQR.tril_gid = FQR.tril_gid
I am using SQL Server 2005 Express Edition in conjunction with asp.net 2.0
Is there any way to guarantee when INSERTing data, that all values in a given column (which is of a char data type) are unique? Or do I have to do this programatically at the asp layer?
I just started with SQL Server Express and was playing around with the Management Studio.
I created a query to make a column unique:
ATLTER TABLE Table_Name ADD UNIQUE (Column_Name)
That worked perfect, if I fill in the same data in the next row of that column, I get an error as expected. But, how do I remove the UNIQUE property on that column? I can find it in the column properties within Management studio, neither I can't find the command to undo this action.
i dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS BEGIN SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] , MAX(T.[Second Start Date]) AS [Max Second Start Date], T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME FROM (
I have a producer table with a nullable column that stores SSN's. In some cases producers inherit SSN's from other producers. These records will have a null producer.ssn and a record stored in a child table to track the inheritance. Anyway, I've found two techniques to enforce uniqueness on a nullable column and wanted to get opinions as to which was better. First, write a trigger. Second, create a computed column that has a unique constraint on it. The computed column would use the SSN if not NULL Else use the PK identity value of the record. EXAMPLE DML:CREATE TABLE test ( ssn CHAR(9) NULL, testId INT identity(1,1) NOT NULL, ComputedConstraint AS CASE WHEN ssn IS NULL THEN CAST(testId AS CHAR(9)) ELSE ssn END, UNIQUE (ComputedConstraint)) Any comments would be greatly appreciated.
I am having problems with unique contraint, say I have a table called customer create table customer ( id int identity(1,1) not null, name nvarchar(50) not null, id_card_no nvarchar(20) unique, ); so when I do mutilple null inserts insert into customer(name, id_card_no) values("Customer 1", NULL); insert into customer(name, id_card_no) values("Customer 2", NULL); I get an error duplicated keys on the unique column.
In Oracle DB, unique columns are behaved a little different where multiple nulls are allowed in a unique column.
I know there are some tircks that you can do to allow multiple nulls in unique column in sql server(well, pretend to be) like creating a computed column, create a view and an index...
Are there any ways to archieve that in sql sever compact?
I don't immediately find if this is possible but hope someone can give me an answer: is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?
Example: table (tableid, instancetype, instancename, ..) instancetype can be A or B if it is A then instancename must be unique but for B instancename is not unique as these are copies from A
only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..
are there other solutions to make a constraint like this ?
Is it possible to create a unique constraint to a column from anothertable? For example:tb_current:current_names--------------aaabbbtb_new:new_name--------cccNow I want to create a constraint on tb_new.new_name to be unique withrespect to tb_current.current_names. However, tb_new.new_name shouldnot be unique to itself. So I should not be able to insert 'aaa' totb_new.new_name. But I should be able to insert 'ccc' totb_new.new_name.Here's the script to reproduce this example:create table tb_current(current_names varchar(10))create table tb_new(new_name varchar(10))insert tb_current values ('aaa')insert tb_current values ('bbb')insert tb_new values ('ccc')select * from tb_currentselect * from tb_newinsert tb_new values ('aaa') -- this should NOT be allowedinsert tb_new values ('ccc') -- this should be allowed
I am testing out the performance benefits of utilizing include column(s) to avoid RID lookups and so far it looks very promising. I am able to reduce the cost of the query significantly and execution time is very good. However my non-clustered index is part of the primary key constraint and I am wondering if it is possible to create the include column as part of the primary key. Or do I have to drop the pk constraint and create unique non-clustered index with include column?
I am in the "Check Constraints" window trying to add a constraint that during inserts only allow distinct Column2 insertions. What is the Expression for that? I tried UNIQUE(Column2) it give me an error.
Here is my table definition: Column Name Data Type Allow Nulls -------------------------------------------------------------------------------------------- Column1(PrimaryKey) int No Column2 nvarchar(MAX) No
I am having problems with unique contraint, say I have a table called customer create table customer ( id int identity(1,1) not null, name nvarchar(50) not null, id_card_no nvarchar(20) unique, ); so when I do mutilple null inserts insert into customer(name, id_card_no) values("Customer 1", NULL); insert into customer(name, id_card_no) values("Customer 2", NULL);
I get an error duplicated keys on the unique column. In Oracle DB, unique columns are behaved a little different where multiple nulls are allowed in a unique column.
I know there are some tircks that you can do to allow multiple nulls in unique column in sql server(well, pretend to be) like creating a computed column, create a view and an index...
Are there any ways to archieve that in sql sever compact?
I have a table with one of its column VARBINARY(MAX).
I want to make sure that the values in this VARBINARY(MAX) column is unique. SQL Server doesn;t allow to create Unique Constraint over VARBINARY fields - whats the best workaround for ensuring uniqueness on VARBINARY columns.
I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
Hi, can anyone guide me on how to create a unique field with SqlExpress and VC#.net?
I know how to create primary key with identity, however I need to create another unique column for "email". I have search high and low for a solution but found none, the closest i got is unique constraint which I've not idea what's that.
Please help, else I have to always do a duplication check before inserting new records.
