How To Select Unique Row When Entire Row Not Unique?
I 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
View Complete Forum Thread with Replies
Related Forum Messages:
Unique Constraint And Unique Index, What's The Difference?
What'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?
View Replies !
Unique Constraint Vs Unique Index
BOL 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 Replies !
Unique Index Vs Unique Constraint
Hi 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
View Replies !
Select Unique
We have a transaction record that, for the sake of brevity, I will usea simple paradigm to convey my need:A sales clerk sells several pairs of shoes over the period of a day,each & every day the clerk works. Each transaction is recorded ina database. All clerks use a single physical register but login tocapture unique ID. As each clerk logs in, it automatically logs outthe prior user and writes a zero-dollar record.Each record logs the RcdNbr, clerk's ID, date-timestamp and salesamount. (Other fields not pertinent to this discussion)Example:Rcd...CID...yyyy-mmdd.hhmm...Sale001...ABC...2005-0101.0850...10.00002...ABC...2005-0101.0930...00.00003...DEF...2005-0101.1000...15.51004...DEF...2005-0101.1200...00.00005...ABC...2005-0101.1300...12.83006...ABC...2005-0101.1530...00.00The above is unsorted. I would first sort by CID (ClerkID) to get:Rcd...CID...yyyy-mmdd.hhmm...Sale001...ABC...2005-0101.0850...10.00002...ABC...2005-0101.0930...00.00005...ABC...2005-0101.1300...12.83006...ABC...2005-0101.1530...00.00003...DEF...2005-0101.1000...15.51004...DEF...2005-0101.1200...00.00As a double-check of a clerk's actual worked hours we want to extractthe last record for each clerk for each day. In the above sample, I'dneed records 004 & 006.The transaction file covers 6 months worth of data.Is there a statement that I can construct that would extract the last(greatest?) time for each clerk for each day?
View Replies !
Unique Select
Hi, There is a table called tblPrices with fields Security_ID int Bid_Price decimal(12, 4) Ask_Price decimal(12, 4) Price_Quote_Date smalldatetime In this table there are thousands of records. For each Security_ID, there are several records with different Price_Quote_Dates What I would like to have is: A select query which shows every single Security_ID with the prices for the LATEST date that there is a price for that Security_ID Example: 1,100, 102.32, 1/12/2005 1, 43, 76.33, 6/12/2005 2,65.77, 45.76, 12/12/2007 ... ... ... Result 1, 43, 76.33, 6/12/2005 2,65.77, 45.76, 12/12/2007 ... ... ... This is what I have started but it is not correct yet because the Security_IDs are repeated select Security_ID, Bid_Price, Ask_Price, max(Price_Quote_Date) from tblPrices group by Security_ID, Bid_Price, Ask_Price
View Replies !
Select Unique Rows For All Columns
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 Replies !
Select Unique Distinct Records For Column
Im trying to run a query that will pull distinct data not for theentire select query but for a single column. Here is my sql statementbelow. Why is it not working? It is probably something stupid. Thanksfor your help in advanceSELECT Count(MastrQualityTbl.[WORK NUMBER]) AS [TOTAL WORK RECEIVED],MastrQualityTbl.USERID, MastrQualityTbl.FIRSTNAME,MastrQualityTbl.LASTNAME, MastrQualityTbl.TEAM_NAMEFROM MastrQualityTbl as c1WHERE NOT EXISTS (SELECT MastrQualityTbl.[WORK NUMBER] fromMastrQualityTbl WHERE MastrQualityTbl.[WORK NUMBER] = c1.WORK NUMBER)andWHERE (((MastrQualityTbl.DATE) Between[Forms]![DashBoardForm]![FromDate] And[Forms]![DashBoardForm]![ToDate]) AND ((MastrQualityTbl.CATEGORY) In('NEW BUSINESS')))GROUP BY MastrQualityTbl.USERID, MastrQualityTbl.FIRSTNAME,MastrQualityTbl.LASTNAME, MastrQualityTbl.TEAM_NAME;Im trying to count only the unique WORK NUMBERS in this selectstatement.
View Replies !
How Can I Do Amalgamate 3 Select Queries And Then Get Unique Entries From The Result
Hi 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 Replies !
Need Help W/ SELECT From One Table, One Field, Multiple Unique Records
I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so: Anna Smith John Doe etc. I did not see IDs listed for any of the names in the table when I looked. There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually. Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them: strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC" This gives me the total list but I only want to bring back 6 of the 20 for the pulldown. Is there a way to modify this statement to pull only the records that I want? Thanks for any help you can give. AJ
View Replies !
A Unique Unique Constraint
Here 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
View Replies !
Unique Key
in sql server 2000 i know how to make primary key using enterprise manager i want to make one of the columns foreign key,how to do that using enterprise manager.and what is the difference between both.
View Replies !
Not Unique
How do I write a select statement that finds items in a table that are not unique between two fields using the following? Select A.KEY, AP.[EXPIREDATE] From ACTKEY A Left Outer Join ACTKEY_PRODUCT AP On A.KEY = AP.KEY
View Replies !
Unique IDs
I've always used the identity field in SQL server to maintain the unique id for a table. With the new DB design at work we brought in a dba and she made us move away from allowing SQL maintain the unique field and having us maintain the unique field in code. To do that we had to begin a transaction, do a select max(id) + 1, insert into table, commit transaction. Doing it this way, I'm starting to see deadlocks due to the transactions locking the table. Getting down to what I wanted to know, what is the pro's/con's you guys see in maintaining the unique ID this way and is there a better way of creating an unique id in T-SQL code? Thanks
View Replies !
PK Not Unique ?
I have one table called agents. In this table there are two columns, one called "company number" (NUMBER) and one called "company name" (VARCHAR). I have an index called agents_PK that are unique and indexes both columns with company number as first and company name as second in the column order. But when I look at the data in the column company number its not unique, I find several rows with the same number. How come ? Regards Mattias
View Replies !
Unique Id
Hi everyone, Is there any id unique for each row in a table by system, if then what is name for it and can one query this id? (apart from Primary key this id is created as rowid by the system in Oracle.) thanks for your help
View Replies !
Id Unique
Lorsque tu as edité un nouveau projet, tu a sans doute du prendre le même nom qu'un précédent. Lors du déploiement, l'ID du model ne peux pas être actualisé, alors il veux creer un nouveaux sur le server de rapport. Or, c'est impossible puisqu'il existe déja... Suprime le rapport qui porte le meme identifiant et re déploie le, ca fonctionnera sans souci, j'éspère
View Replies !
Getting The Unique Row
Hi I have a table as follows ID Cat Date -------------------- Id1 cat1 d1 Id2 cat1 d2 Id3 cat1 d3 Id4 cat2 d4 I wanted to retrived unique Cat for given ID's. I accept two ID's always Select distinct(Cat) from Table where ID='id1' or ID='Id2' This works fine for me and returns distincts values. Result -------- Cat1 But i also want the date along with Cat ( which ever is greater ) from theresult set. Expected result ---------- Cat1 D2( latest) Can any one please let me know the query ~Mohan
View Replies !
UNIQUE VALUE
Hello SQL gurus, can you help me with a problem. I have an Access Database and a table containing a list of currency values. I want to query this table to find out if there are any unique values, that is, an amount that only appears once in the table. Its probably very simple but I cannot work it out. The table is called Bids and the Field that I want to query is called Bids_value. Any ideas would be greatly appreciated.
View Replies !
Reg Unique Key
I know that primary key is a unique key .I read somewhere that a table can have both primary key and unique key at a time .Am I Right?Pl give clarification as asap. Thank U vedavathi zend
View Replies !
Unique Name
Hi guys, I need your help! May I know what can I do to get a single name using the sample table below? toptable name account a 1 a 2 a 3 b 1 b 2 c 2 d 3 e 1 e 2 result will be toptable name account a 1 b 2 c 2 d 3 e 2 I just want to get all single names
View Replies !
Unique Key
Dear experts, if i've created one unique key on one column, will it creates an index on that? if so, is there any way to find how every index was created? i mean wether it was created with create index or it was created while the primary key or unique key creation. thank you very much....i've been using the PBGUY query Select * from (select object_name(si.id) [Object],index_col(object_name(si.id), si.indid, sk.keyno) [Column_Name], case when (si.status & 16)<> 0 then 'clustered' else 'nonclustered' end [Index Type] from sysindexes si join sysindexkeys sk on si.id = sk.id and si.indid = sk.indid and si.indid between 1 and 254 and (si.status & 64)=0 ) as b where Column_Name is not null thank you very much
View Replies !
Unique Id
I am using the sql function unique id ( create a new default call it newid and then give it the value newid() ) The problem is I'm trying to automatically populate that uniqueid field with the random 36 bit character. if I create a new record it will create the 36bit character, but how do I add the 36bit character to an existing table?
View Replies !
Unique Column
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?
View Replies !
How To Insert A New Unique INT If None Is Given
I've got a table name Messages and each message has a MessageID (the primary, auto-generated column) and a ThreadID (for grouping replies together). When I create a new message, I want to insert a new ThreadID if none is given to the stored procedure. I'm looking for something like NewID() as the default value but that inserts a NewInt. Do I have to write my own trigger?And I know ThreadID should be a foreign key to a Threads table but I'm keeping it simple for now. I have the option of making that later if I like. Thanks.
View Replies !
Unique IDs Or Ints
Hi again, When developing web sites with Asp, DreamWeaver and Access, I've always used Unique IDs for setting things like userID or productID. However, with VWD Express and a .mdf db, this doesn't seem like an easy thing to do, at least not when inserting test data during development. The reason is that the uniqueIDs are 32 or so digits, and I have no idea what to write, and it's tiresome anyway. I used a book from Wrox (Begninning Asp.Net 2.0) to learn VWD and Asp.Net, and in their examples they actually use ints for setting their productIDs and the like. I'm really surprised to see that in a text book, but that's the way they've done it. So what's the best practice? What should I do? Please help me out here as I'm really confused! Thanks in advance, Pettrer
View Replies !
Unique PK In Each Table
Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate. Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks
View Replies !
Unique SQL Statement. I Don't Think This Is Possible
I have a list of zones and IP addresses assigned to those zones. On the page that manages those zones and their IP addresses needs to only display the RANGES of IP addresses that exist for that zone. For example if this is my data for Zone 1:IPAddress (removed first octet)002.202.148002.202.149187.202.243187.202.244187.202.245187.202.246187.202.247187.202.248187.203.187I want to display it as this on the management page:Zone 1 : IPAddress Ranges From 2.202.148 to 2.202.149 From 187.202.243 to 187.202.248 From 187.203.187 to 187.203.187Is there any way to make the SQL statement list the mins and maxes for every range? Or is this something I just HAVE to do in the code? I would like a record to return:MIN | MAX2.202.148 | 2.202.149187.202.243 | 187.202.248187.203.187 | 187.203.187I think I'm trying to expect a lot from SQL but I thought i would ask anyway. Thanks!~Cattrah~
View Replies !
How To Get Unique ID After Insert
<disclaimer>I'm only using Web Matrix and have very little SQL knowledge</disclaimer> I am doing an INSERT into a file where the primary key (UniqueID) is automatically generated. However, as soon as the record is inserted I need the UniqueID for further processing. The only way I can see for me to get the UniqueID is to do a SELECT immediately after the INSERT, which seems a bit of an overhead. So, just checking really, is this my only option or is there a super impressive way of passing the UniqueID back via the INSERT. Thanks Mark
View Replies !
Unique Keys
Hello,I plan to create a table with 3 unique keys.Combination of three fields has to be unique for each row in a table thatare vendor ID (char 8), vendor name (char 40), and vendor office (5).Will it be okay to have a unique key which has a long character such asvendor name?How should I index those three fields? Those fields will be searched manytimes.RCW
View Replies !
Using Unique Indexes
Why, in the process of creating a unique index, does SQL Server allowme to select the "Ignore duplicate keys" option? Wouldn't I justcreate a non-unique index if I wanted to ignore duplicate keys? Icame across this fact while preparing for the SQL Server design exam.
View Replies !
Is A GUID Always Unique?
Greetings All, I have read many upon many articles here regarding GUIDdata types and uniqueness. There have been many opinions regardingthe effectiveness of GUID's and when they should/should not be used.However, every article strongly implies, if it does not state itoutright, that GUID's are always unique. My question is this, whathappens if you have a database that uses GUID's and the NIC is changedout on the box? From what I understand the MAC address of the NIC isused as part of the algorithm to generate a GUID. If you change outthe NIC after generating 1 billion GUID's do you run the chance ofgenerating a duplicate GUID?I look forward to your insightfulness on this issue.Regards, Louis.
View Replies !
Getting Unique Rows
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 Replies !
Unique Field
I have a database table with a primary key that auto-increments - using MS SQL Server 2000. However I have another field (standard field) that must not allow duplicates. Besides writing code to sift through the database every time to check for duplicates (which would take long to search through the db every time), is there any setting in MS SQL Server 2000 that allows for an automatic check for duplication and will prevent duplicates!?? Here's an example of what I mean... PK Entry_Sheet Cust_Id Date 1 62345 cust1 16/11/2004 2 45663 cust6 16/11/2004 3 82917 cust1 16/11/2004 4 19283 cust2 17/11/2004 5 28764 cust2 18/11/2004 I have everything created, with data already in the table. What I need now is some way to prevent duplicates from occurring. The Entry_Sheet value needs to be unique. Instead of writing code to run through the entire db each time an insert is performed, is there a simpler way using a SQL Server DB!??
View Replies !
Unique Identifier
I'm trying to create a unique identifier number that meets the following criteria. The unique identifier needs to be a concatenation of two values submitted from a form and the identity value (primary key) for the new record that is inserted into the database. So, if the first field is the year and the second field is a objnumber, the unique identifier number would have the format: ("YR" + "objnumber" + primary key value), where the year and object number are what the user selected in the form. I have a stored procedure that I use to handle the insert, which also returns @@identity for the purpose of passing that value into another stored procedure that inserts child records. So, within my stored procedure, is there a way I can create the unique identifier number and return that value back to the application? I'm not sure how to accomplish this? Here is my stored procedure: CREATE PROCEDURE dbo.REQ_HDR_INSERT @ddo varchar(50) = null, @requestor varchar(100) = null, @dt datetime = null, @abrtype varchar(20) = null, @subject varchar(250) = null, @description varchar(500) = null, @review char(10) = null, @ay char(4) = null, @origallo varchar(50) = null, @reqallo varchar(50) = null, @logl_del_dt datetime = null, @phys_del_dt datetime = null AS Insert into dbo.DIM_ABR_REQ_HDR (ABR_ddo, ABR_requestor, ABR_dt, ABR_type, ABR_subject, ABR_description, ABR_review, ABR_AY, ABR_orig_fund_allo, ABR_req_fund_allo, ABR_LOGL_DEL_DT, ABR_PHYS_DEL_DT) values (UPPER(@ddo), UPPER(@requestor), @dt, UPPER(@abrtype), UPPER(@subject), UPPER(@description), UPPER(@review), @ay, convert(money, @origallo), convert(money, @reqallo), @logl_del_dt, @phys_del_dt) return @@identity GO I would be using @ay and @ddo as the first two parts of the unique identifier number. Any help is appreciated. Thank you, -D-
View Replies !
Is Transaction Name Has To Be Unique?
Hi I remember that once I had a problem when using a cursor in a sp and when several instances of the sp were running I had a problem when the first sp in the sequence deallocated the cursor and all the other who run in parallel had errors... Well, this is not the problem now, but my question is, if I have a sp that has begin tran t1, and several instances of the sp are running in parallel, and each of course has begin tran t1, should I expect the same collision effect like with the cursor? Is every tran has to be with unique name? Or maybe the server knows how to manage this and when one tran has started and another sp tried to start another with the same name it makes it wait until the first one committed or rolled back? Thanks, Inon.
View Replies !
UNIQUE But Not PRIMARY
Hello for MS SQL 2000 I want to CREATE a Table as follow : CREATE TABLE [dbo].[Local] ( [id_Local] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar] (100) NOT NULL, [Info] [nvarchar] (100) NULL ) id_Local is PrimaryKey autoincrement but Name must be UNIQUE (not 2 times the same name) how can script it ? thank you
View Replies !
Unique Problem???
Hi all, I have Sybase SQL Anywhere v5.x and MS SQL v7.0 installed on my comp whose specs are:Win 2k Professional Edition, 1.4 G P-4 processor, 2G Hz RAM. I have a databse which is around 600MB in size and my problem is the SQL server is using up like 1.5G of RAM!!! Is there anyway I could make the SQL server to use (as little as possible) of the RAM usage. Please help me out. TIA Avinash.
View Replies !
Unique Records
Is there a way to get a Stored Procedure to return only unique records from a table? I am using a Stored PRocedure to query a table and it returns all the records in the table and there are many duplicates in the information. I am using SQL Server 2K.
View Replies !
Unique Constraint
What is the simplest way to add a unique constraint on a field of type varchar(7) that can allow any number of <NULL>'s? I only want to ensure that when this field is updated, it is updated with a value that has not been used. IF EXISTS (SELECT Project FROM tbProjects WHERE Project = @cProject) RAISERROR('Project number already used!',16,1) ELSE UPDATE tbProjects SET Project = @cProject WHERE ProjectID = @iProjectID GO Also, I cannot allow the user to chante the project field value once it is set. Any suggestions? Mike B
View Replies !
Create Unique Row
I am new to MS SQL and I was wondering is it possible to create a table with unique rows?? By this I mean if a table has two columns then a duplicate row would be if BOTH columns matched two columns of another row. Thanks
View Replies !
Unique Index
Just found out that creating a unique index does not create a unique constraint, but creating a unique constraint creates unique index. But effectively they do the same thing.
View Replies !
Opinions On Unique IDs
I've always used the identity field in SQL server to maintain the unique id for a table. With the new DB design at work we brought in a dba and she made us move away from allowing SQL maintain the unique field and having us maintain the unique field in code. To do that we had to start a transaction, do a select max(id) + 1, insert into table, commit transaction. Doing it this way, I'm starting to see deadlocks due to the transactions locking the table. Getting down to what I wanted to know, what is the pro's/con's you guys see in maintaining he unique ID this way and is there a better way of creating an unique id in T-SQL code? Thanks
View Replies !
|
TRACKER
