Report Unique Entries
Dec 1, 2006e.g. column name: nameID, projectID)
2, 123
3, 213
3, 243
4, 987
5, 243
5, 213
I want the result to be:
2, 123
4, 987
How would I write the query?
Thanks in advance.
ADVERTISEMENT
Counting Unique Entries In A SQL Statement
Nov 29, 2006
I have a complicated problem, and I'm new to SQL so any help would be greatly appreciated.
I am creating an export file (fixed width) that contains a breakdown of items on an invoice, and each "export file" can contain many invoices. The problem is that I need to apply an incremental "invoice" count on each line. This isn't as simple as doing a running sum of "1" on each record, because the first 5 rows may all be on the same invoice, and all rows need to be identified as being associated with "invoice 1". The next invoice will be known as "invoice 2" and again may contain many rows, all requiring "invoice 2".
Does this make sense?
EG.: I am shipping products, and the breakdown is: Vessel, Voyage, Invoice No, Product, Mark.....
SAGMIR 025 001 HEM/FIR HLF550...
SAGMIR 025 001 HEM/FIR KILN-D HLF505...
SAGMIR 025 002 HEM/FIR HLF660....
The SQL statement that produces the above is a Select query with a grouping on VES/VOY/BL_ID/PRO/MARK where the "BL_ID" indicates they are on the same invoice, but is not the incremental number I require. Complicated, i know...
Thanks in advance for anyone who can help.....if this explanation isn't clear please tell me!
Michael
How To Force Unique Entries In A Linking Table?
Jul 20, 2005I have a table 'Group2Operation' that stores many to many relationsbetween the 'Group' table and the 'Operation' table (each group is haspermission to perform one or more of the available operations)PROBLEM=======I need to prevent duplicate entries being created. e.g. lets say thatin the 'Group2Operation' table a record links the 'editor' group tothe 'publish' operation. Should I prevent an administrator creating aduplicate of that record? (Otherwise deleting that permission willhave to be done twice or more for it to be effective)SOLUTION?=========So far I've done this with a trigger:CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2OperationFOR INSERT, UPDATEAS UPDATE Group2OperationSET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTagThe 'NoDuplicate' unique index column in the Group2Operation tablestores a concatenation of the unique group and operation identifiers.So when an attempt is made to create a record, the trigger is fired.If there is a duplicate, this will mean a duplicate entry in the'NoDuplicate' column. As a result, the INSERT or UPDATE will fail andthe duplication will be prevented.WHAT DO YOU THINK?==================What do you think? Am I going about this in the right way? Is atrigger a good way to do this or should I rely on application logic toprevent duplicates?Any help appreciated by this db novice.John Grist
View 2 Replies View RelatedHow Can I Do Amalgamate 3 Select Queries And Then Get Unique Entries From The Result
Mar 7, 2006Hi AllStrange request I know, but could somebody give me pointers on how I can put3 queries into 1 'thing' and then get only the unique entries from this'thing'.To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queriesthemselves aren't that complex and all return the same 2 fieldsets of stockcode and stock desc. Because these separate queries might bring back thesame stock code/description I need to amalgamate the data and then queryagain to bring out only distinct stock values, eg:Query 1 brings back:stock code stock descIVP Invoice PaperSTP Statement PaperKGC Keyboard Coveretc... etc...Query 2 brings back:stock code stock descIVP Invoice PaperBOB Back PackKGC Keyboard Coveretc... etc...Query 3 brings back:stock code stock descKGC Keyboard Cover3.5"D 3.5" Disksetc... etc...I need to produce 1 resultset that shows:stock code stock descIVP Invoice PaperBOB Back Pack3.5"D 3.5" DisksKGC Keyboard CoverSTP Statement Paperetc... etc...(all unique entries)I'm currently just bringing back the 3 query results in Excel, but I'd liketo be able to do the above.In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do?ThanksRobbie
View 1 Replies View RelatedI Need The Report To Include Entries Not Found In Database
Nov 27, 2007I have a report that accepts a list in the filter and searches the data base for the items in the list. How do I get the result to include all items in the list even if they are not located in the database? Thanks.
View 4 Replies View RelatedSearching For Entries In Subsequent Log Entries?
Dec 2, 2013If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it?
I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above.
------ SQL -------
SELECT
job.job_number,
(SELECT MAX(jsl.job_log_number)
FROM job_status_log jsl
WHERE
job.job_number = jsl.job_number AND
jsl.status_code = '0130') as Last_Early_Warning_Status_Entry
[code].....
In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.
Saving Report With Unique Report Name
Mar 24, 2008Hello,
I want to have my report in SSRS run automatically (I believe with the subscription mode) so it generates a PDF file every night. The problem is when it saves the PDF file, it is named the same as the report name. I need a unique report name with each night. It can be part of the date or whatever. Does anyone know how to set this?
Thanks,
Warren
Bug? Report Builder Returning Only Unique Records!
Feb 8, 2007Our users use Report Builder for writing ad-hoc queries. They have run into a problem where Report Builder is returning only distinct records is the entities unique identifier is not added to the report.
Example:
TransID Type TransQuantity
1 CS 4
2 CS 4
3 CP 2
4 CN 3
Adding the above fields to the report builder canvas will return 4 rows. However, if I were to add only Type and TransQuantity to the canvas as such, Report Builder will only return 3 rows:
Type TransQuantity
CS 4
CP 2
CN 3
Is there any way to have Report Builder return the actual number of records? Our users often will export the results from Report Builder into Excel to slice and dice the data, and with the functionality the Report Builder has right now, incorrect values will be certain.
Thanks,
Taurkon
Report Model - How To Create A Unique Logical Primary Key In A Named Query
Apr 7, 2008
Hi,
Te following situtation is :
ReportModel is created ,there is only a named query in DSV ,it has a few tables in it(The relationship are inner joins and outer joins).
The question is how could I create a unique logical primary key to identify each unique row in the named query dataset, and also you cannt generate a model unless the named query has a logical primary key . how can I solve this problem,any help?
SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)
Jul 5, 2015This index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?
Sep 22, 2004A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
View 8 Replies View RelatedUnique Constraint Vs Unique Index In MS SQL 2000
Jul 20, 2005HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies View RelatedUnique Constraint And Unique Index, What's The Difference?
Jun 24, 2006What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
Unique Index Vs Unique Constraint
Mar 7, 2001Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?
Which one do one use ?
thanks
sonali
Unique Constraint Vs Unique Index
Jan 20, 2006BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
View 2 Replies View RelatedUnique Index Vs Unique Constraints
Mar 26, 2008
hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
How To Select Unique Row When Entire Row Not Unique?
Mar 12, 2008I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today.
Here is the code
Code Snippet
create procedure SearchPartKeyword
(
@Keyword varchar(250) = null,
@Universal_Id varchar(10) = null
)
as
select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM,
p.Legacy, p.Folder, p.Printer
from Parts p inner join notes n on p.PartNumber = n.Identifier
where n.Universal_ID = @Universal_ID and p.Description like @Keyword
union
select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly,
p.DMM, p.Legacy, p.Folder, p.Printer
from Parts p
where p.Description like @Keyword
and the results:
PartNo Description SO Notes LA DMM Legacy Folder Printer
de90008 MAIN BOARD 1 DGF1 114688 0 0 0
de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
Thank You
Dominic Mancl
What 's Difference Between Unique Key And Unique Index
Nov 13, 2007What 's difference between Unique key and unique index in SQL server 2005?
View 9 Replies View RelatedRegistry Entries
Dec 1, 1998Below are the registry entries I want to update but "Trusted_Connection" = "Yes" does not seem to have any effect when it is run. Any ideas??
REGEDIT4
[HKEY_CURRENT_USERSoftwareODBCODBC.INIAAED]
"Driver"="C:WINNTSystem32sqlsrv32.dll"
"Description"="AAED"
"Server"="SIM0108"
"Trusted_Connection"="Yes"
[HKEY_CURRENT_USERSoftwareODBCODBC.INIODBC Data Sources]
"AAED"="SQL Server"
Finding Last Entries
Jan 27, 2005Hi, I am searching for the most easy SQL solution:
Lets say i have 500000 rows of cars in one table with 30000 different car ID's.
This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?
Thx for reply.
dajm
Keeping Last 10 Entries By ID
Sep 24, 2006Hello
my table :
Report :
R_id (PK)
RName
RDate
i am having a few 10.0000 lines and i want to keep the last 10 (or less if not in the table) rows maximum for each name
i can have 100 report by name (100 rows with the same name and of course R_id and RDate are different)
how can i do it ?
thanks a lot for helping
No Further Entries Of A Value In Table
Mar 30, 2012I have a table which stores phone numbers of a customer in a table.
Say this table is as below
CustomerName - PhoneNumber
Customer 1 - Phone number 1
Customer 2 - Phone number 2
Customer 2 - Phone number 3
Customer 3 - Phone number 4
What would be the best approach to prevent adding another entry against Customer 2. I should be able to add new customers and add multiple phone number against all other Customers. The restriction should be only against Customer 2.
Want To Pull Last Three Entries From DB
May 14, 2004I am trying to pull the last three entries from a table in my database but I am having trouble writing the correct query. The database has multiple entries for each item in my database but I want to pull just the last three for each item. I have tried the TOP function with the items ordered in descending order but that only works when I define each item seperatley. I need to know how to pull the last three entries for every item I have in the table.
Thanks,
Tim
Maximum Entries In MS SQL
Nov 8, 2005Hello
Without technical information and DB design, I wish to know the maximum entries in MS SQL in million. I guest for access are a limit of 2 million and a 2GB DB.
How much for MS SQL in max. entries and how much in GB?
A lot of thx for your feedback.
Dominique Javet
Duplicate Entries
Mar 28, 2006I'm extracting data from a log (log_history) of patients where nurses perform various actions on a call, such as assessing and reassessing, despatching etc. This is the script:
Select
L.URN,
LH.THE_TIMESTAMP,
LH.ACTION_TYPE,
LH.ACTION_BY,
LH.ACTION_REQD,
LH.NOTE,
em.position_type_ref
From
LOG L
Join Log_history LH on (L.URN = LH.LOG_URN)
left outer join employee em on (em.code = LH.action_by)
Where
(L.Taken_at >= :DateFrom and L.Taken_at <= :DateTo) and (LH.ACTION_TYPE = 'D') and (em.position_type_ref ='NU')
Order By
L.URN ASC, LH.THE_TIMESTAMP DESC
The result I get shows duplicate 'timestamp' entries and I only want to return unique timestamp entries. Does anyone have any ideas. I'm self taught and have hit a wall
Help With Multiple Entries
Jun 12, 2006Hi,
I need help desperately. The following is an example of the data that I have.
I have two tables one name Customers and the other named Orders. The tables have the following attributes
CUSTOMER
CustomerNum, CustomerName, OrderNum
ORDER
OrderNum, OrderDesc, CustomerNum
Bare in mind that the CustomerNum can have multiple customers attached to it, example a whole family (I know bad database design but it's too late to change) Let's use the following info for the tables respectively
CUSTOMER
0001 Sharon Bigbsy 1234
0001 Dale Bigbsy 1235
0001 Omar Bigbsy 1236
ORDERS
1234 Chips 0001
1235 Gatorade 0001
1236 Candy 0001
The query i'm using is select Customer.CustomerNum, Customer.CustomerName, Orders.OrderDesc from CUSTOMER, ORDERS where CUSTOMER.CustomerNum = ORDER.CustomerNum
The results are as follows
0001 Sharon Bigbsy Chips 1234
0001 Sharon Bigbsy Gatorade 1235
0001 Sharon Bigbsy Candy 1236
0001 Dale Bigbsy Chips 1234
0001 Dale Bigbsy Gatorade 1235
0001 Dale Bigbsy Candy 1236
0001 Omar Bigbsy Chips 1234
0001 Omar Bigbsy Gatorade 1235
0001 Omar Bigbsy Candy 1236
It's giving all the orders place to each customer name but I need only the following
0001 Sharon Bigbsy Chips 1234
0001 Dale Bigbsy Gatorade 1235
0001 Omar Bigbsy Candy 1236
I'm using SQL in MS Access 2003. Please disregard the abnormalities of the tables, it's someone else's work I inherited with over 6000 entried.
Any help will be greatly appreciated
Duplicate Entries
Feb 7, 2007I have an application that allows the user to enter data into a table. There are multiple users so I put in some code that, I thought, would keep 2 users from creating a new record at the same time. The IDs for the records are identical and this is causing a problem.
The IDs are in the format of ####-mmyy. at the start of each month the #### part goes back to 1.
We tried a test today where we had 2 users click on the New button at exactly the same time. The IDs that were created were identical. Is there anyway on the database that I can prevent this from happening?
Here is how I create the new record id:
I get the MAX(ID) from the table
I add 1 to the ID and then insert a new record with the new ID into the table.
Any help is appreciated.
Thanks,
enak
Entries In Error Log
Jan 7, 2008Hi all
can some one there please let me know how to configure error logs. what i observe on some of the servers error log got entries for all conenction, database backup, logbackup etc..... but on some there r entries only about database backup.
thanks in advance
Current Log - Too Much Entries?
Jul 20, 2005Hello,does anyone know what could be wrong? There's a lot of similar entriesin the SQL Server current log:2004-01-21 17:54:00.01 spid57 Starting up database 'DB_NAME'2004-01-21 17:54:06.45 spid57 Starting up database 'DB_NAME'2004-01-21 17:54:59.13 spid56 Starting up database 'DB_NAME'There are hundreds of similar entries that repeat every few seconds. Thedatabase works quite slowly and the users get 'Connection timeout'message very often.Thanks for any advice....:: fabio
View 1 Replies View RelatedHow To Remove Dup Entries
Sep 26, 2007Hi All
In my application i have to get the data from .csv file. My requirement is that file may consists of duplicate entries
I ant to remove the dup entries and i want to place in the table.
Waiting for valuable replies
Thank u
Baba
Duplicate Entries
Sep 14, 2007
I have an issue where certain parts of data are repeated several times after i create my query. Without providing my SQL code for now could anyone suggest possibly the main reason(s) for data being duplicated?
Thanks
A Unique Unique Constraint
May 1, 2008Here is the table I created:
create table Test (
[recId] [int] identity(1, 1) not null,
[code] [varchar](50) not null,
[prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)),
constraint [PK_Test] primary key clustered
(
[recId]
) with fillfactor = 90 on [primary]
) on [primary]
go
insert into Test (code, prime) values ('AVA', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('CAR', cast(1 as bit))
insert into Test (code, prime) values ('CAR', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(1 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))
select *
from Test
I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail:
-- This should fail
insert into Test (code, prime) values ('RLW', cast(1 as bit))
Thanks for you help!
Regards,
Anand
Getting Random Table Entries
Jan 20, 2008Hi,I have a form that should show 2 pictures based on table entries.I want those 2 pictures to be randomly selected based on a database table. So, my table has all the entries, and I want to pull out a random entry that has been approved to display it.Can someone help me with the sql query?I can do SELECT VoteId FROM tblVotes WHERE Approved=True..But how do I make selection a random one that changes every time the user gets another entry?
View 2 Replies View Related