How To Get Unique Record With Columns
Mar 20, 2013
I have result set as the attached in screenshot, where the candidateid gets duplicated, but then the column university and careerchoice column has different values for the same candidate. (the CareerChoice and University comes from different tables and i have used union clause)
What want is e.g. CandidateId 186 repeats three times, it should be one row, with three columns all filled with the values which are in different rows.
So that I don't have duplicate candidate ids and i have single row with all column data.
View 6 Replies
ADVERTISEMENT
Sep 15, 2014
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
View 4 Replies
View Related
Oct 24, 2005
HI
I HAVE THREE TABLE GEN, PARTY, TXN (GEN & PARRTY IS MASTER TABLE AND
TXN IS TRANSACTION TABLE)
IN TXN TABLE - STORE ALL DATA IN WHICH SAME VOUCHER NUMBER IS REPEATED
ACCORDING TO JOURNAL ENTRY)
NOW PROBLEM IS THIS AT THE TIME OF DISPLAY THE RECORD ON SCREEN I HAVE
TO SHOW ONLY THE UNIQUE VOUCHER NUMBER ONLY..
PLS GUIDE IN THIS QUERY HOW CAN I GET THAT RESULT.
SELECT DISTINCT TXN.VOUCHER, GEN.GLNAME, PARTY.PARTYNAME, TXN.DOCDATE, TXN.GLCODE, TXN.PARTYCODE, TXN.AMOUNT, TXN.VTYPE, TXN.CBCODE, TXN.ADJ, TXN.DC, TXN.NARR
FROM (TXN LEFT JOIN GEN ON GEN.GLCODE=TXN.GLCODE) LEFT JOIN PARTY ON PARTY.PARTYCODE=TXN.PARTYCODE
WHERE TXN.VTYPE='JV'
ORDER BY TXN.VOUCHER
THANKS
ASM
View 3 Replies
View Related
Jan 9, 2008
I am trying to make a where clause using the sql server unique record id that is set automatically when a record is inserted. It is the record id that is unique for one table.
In a where clause. What type should go where the _______ is below?
Here is the program
<%@ page language="vb" %><%@ reference page="try7.aspx" %><% @import namespace="system.data"%><% @import namespace="system.data.sqlclient"%>
<html> <head> <title> </title> <script language="vb" runat="server"> sub page_load(sender as object,e as eventargs) response.write(request.queryString("a")) response.write(",") response.write(request.queryString("b")) get1.text=request.queryString("b") end sub
sub star(sender as object,e as eventargs) dim connection1 as new sqlconnection("")
const q1 as string="update tbltest set code=@code where dxid=@dxid" dim command1 as sqlcommand=new sqlcommand(q1,connection1) command1.parameters.add("@code",sqldbtype.varchar).value=get1.text command1.parameters.add("@dxid",sqldbtype._________).value=request.queryString("a")
command1.connection.open() command1.executenonquery() command1.connection.close() end sub </script> </head> <body> <form runat="server"> <asp:textbox id="get1" columns="6" maxlength="6" runat="server" /> <asp:button id="buttonsave" text="click" onclick="star" runat="server" /> </form> </body></html>
View 3 Replies
View Related
Jul 20, 2005
I have a stored procedure (below), that is supposeto get a Reg Number from a table, (Reg_Number), insuch a way that every time the stored procedure is called,it will get a different reg number, even if the storedprocedure is called simultaneously from two differentplaces,However it is not working that way.If two different users access a function in thereVB program at the same time, the two different userswill get the same reg number.I have looked at the stored procedure, it looks foolproof,yet it is not working that way.Thanks in Advance,Laurence NuttallProgrammer Analyst IIIUCLA - Division of Continuing Education'---------------------------------------------------------------------------Here it is:CREATE PROCEDURE sp_GetNextRegNum@newRegNum char(6) = NULL OUTPUTASLABEL_GET_ANOTHER_REG:Select @newRegNum =(select min(Reg) from reg_number)IF Exists (select Reg from reg_number where reg = @newRegNum )BeginDelete from reg_number where reg = @newRegNumIF @@Error <> 0BeginGoto LABEL_GET_ANOTHER_REGEnd--EndifEndELSEGoTo LABEL_GET_ANOTHER_REG--EndifGO
View 6 Replies
View Related
May 1, 2008
Hi
I am using sqlserver 2005 and want to generate unique numbers for all the records in a table using sql.
Something like table A has
FRUITS
-------------
Oranges
Mangoes
Apricots
....
I want to write a sql query that does something like
SELECT convert(varchar(8),getdate(),12)+'000001' my_number,
fruits
from table A
so the output is
my_number Fruits
080501000001 Oranges
080501000002 Mangoes
080501000003 Apricots
....
regards
Hrishy
View 4 Replies
View Related
Apr 23, 2015
I am new to SQL but trying to do join a few tables to get result showing showing one row per unique record.
Tables include:-
1. REQ
2. RFQ
3. PO
4. DOCUMENT (contains LAST_DOCUMENT_STATUS, DOCUMENT_ID, DOCUMENT_NUMBER, for example, REQ_CANCELLED, REQ_ID, REQ_NO)
5. DOCUMENT_STATUS (contains status of document, REQ_CREATE)
6. DOCUMENT_TRAIL (contains link between documents, PARENT_DOCUMENT, CURRENT_DOCUMENT, for example, REQ_ID (PARENT_DOCUMENT), RFQ_ID (CURRENT_DOCUMENT)
7. PO_REVISION (contains PO REVISION, when link with DOCUMENT, PO_REV_NO)
Currently when i tried to join all the TABLES, i get multiple lines against REQ_NO.
I realised the multiple lines generated due to the following:-
One to many relationships:
A. RFQ - 1 or more PO
B. PO - 1 or more PO_REVISON
I was thinking how to MAX the records in PO to show only the last PO_REVISION. It seems that DOCUMENT_TRAIL will contain 1 base document PO and 1 or more PO_REVISION.
View 13 Replies
View Related
Jul 11, 2006
Hi all,We have an app that uses SQL 2000. I am trying to track when a code field(selcode) is changed on an order which then causes a status field (status)to change. I tried a trigger but the app may use 2 different updatestatements to change these fields depending on what the user does. When thetrigger fires (on update to selcode), the status field has already beenchanged. So my trigger to record the changes from inserted and deleted donot get the true 'before' value of the status field.The app does use a log table that tracks these changes. The problem I amhaving is that 2 records are created, one for the change to selcode andanother for the change to status.I am looking for help with a script to combine the existence of these 2 logrecords into 1 unique record or occurance that I can track.example:ordlog: table that logs order changesordernr: order numbervarname: name of field being changedold_value: contents of field before changenew_value: contents of field after changesyscreated: date/time of log entrySELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'selcode' and ordernr = '10580'SELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'status' and ordernr = '10580' and old_value = 'A' andnew_value = 'O'So I need a way to combine these 2 log entries into a unique occurance. Theordernr and syscreated could be used to link records. syscreated alwaysappears to be the same for the 2 log entries down to the second. Selcodecan change from NULL to a number of different values or back to NULL.Statusis either 'A' for approved or 'O' for open. An order can have many logentries during its life. The selcode may be changed several times for thesame order.Ideally, I would like a result that links 2 log entries and shows the statuschanged from 'A' to 'O' when selcode changed.Thanks for your time.
View 1 Replies
View Related
Jan 19, 2007
I've been searching and trying out SQL statements for hours and I'mstill having a problem.I'm querying 3 tables... and I only want the first unique recordreturned.Currently, the data returned looks like this..............New York New York ANew York New York BNew York New York CLos Angeles California ALos Angeles California BLos Angeles California CI want the select statement to return this instead...New York New York ALos Angeles California AI'm using MS SQL server.please help?thanks for your help.
View 4 Replies
View Related
Oct 31, 2007
Hello,
I am working with a database that among other things uses multipart keys as the unique indexes which are not consistent from say one table where a parent record resides to another table which contains related child records. For example I am working with two tables right now, one that contains content that I'll call Contents and the other which contains Usage information about the contents (number of view, a rating and comments give by a customer) which I'll call ContentsUsage. The system that manages the data for the tables has a versioning system by which, whn a content item is added (first time) a "unique" id (guid) and a version number of 1 is created along with the rest of data items in the Contents table and likewise in the ContentsUsage table (essentially a one to one mapping) on the like named fields in that table. Now, each time a given record in the Contents table is updated a new version, with the same guid is created in the Contents and ContentsUsage table. So one side I have:ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2And the other table (ContentsUsage)ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2
While both of these tables have a quasi-unique record (row_id) of type char and stored as a guid neither obviously are the same in the two tables and having reviewed the database columns for these tables I find that the official unique key's for these tables are different (table 1, Contents combines the ContentGUID and Version) as the composite / mutli-key index, while table ContentsUsage uses the RowGUID as it's unique index.
Contents RowGUID (unique key)ContentGUIDVersionViewsRatingComments................RowGUID ContentGUID (unique key)Version (unique key)Description.....
Bearing this in mind I am unable of course to link directly the two tables by using the just the ContentGUID and have to combine the additional Version to I believe obtain the actual "unique" record in question. The question is in terms of writing queries, what would the most efficient query be? What would be the best way to join the two in a query? And are there any pitfalls with the current design that you can see with the way this database (or specifically these tables are defined)? It's something I inherited, so fire away at will on the critique. Having my druthers I would have designed these tables using a unique key of type int that was autogenerated by the database.
Any advice, thoughts or comments would be helpful.
Thanks,P.
View 8 Replies
View Related
Apr 9, 2008
Hello Hello,
This Noob has a question.
I have been atempting for days to figure this out.
I have this set up: (See Below)
The Problem I am having is occuring at the Table named OU in Maroon below.
It combines 2 chains of keys into one table to try and make a unique record.
The problem is that it is not.
Pasted below is a Query run on the data within the tables:
As you can see certain data is getting duplicated in this tabel.
I want 1 Unique record in the OU table based upon the Study ID Primary Key and the zCombined Primary Key.
Any Ideas as to what is wrong, or how to make it work would defineatly be appreciated.
Thanks a Bunch
Table: Sponsor
PK: Sponsor ID
Table: Protocol
PK: ProtocolID
FK: Sponsor ID
Table: Study
PK: StudyID
FK: ProtocolID
Table: OUPK: OUIDFK: StudyIDFK: zCombinedID
Table:zCombined
PK: zCombinedID
FK: TempID ~ To Temp Table
FK: ShipTypeID ~ To Ship Type Table
FK: CoordinatorID ~ To Coordinator Table
FK: BoxTypeID ~ To Box Type Table
Sponsor
Protocol
Study
OU~Column1
OU~Column2
Box
Coordinator
Ship Type
Temp
Omni
AAAAA
XPG
S0101
NA
Wheaton
Raul Vargas
Daily
-70
Omni
AAAAA
XPG
S0101
NA
Matrix
Raul Vargas
Daily
-70
Omni
AAAAA
XPG
S0101
NA
B-1
Raul Vargas
Daily
-70
Omni
AAAAA
XPG
S0101
NA
9 x 9 4mL
Raul Vargas
Daily
-70
Omni
AAAAA
XPG
S0101
NA
9 x 9 2mL
Raul Vargas
Daily
-70
Omni
AAAAA
XPG
S0101
S0201
Wheaton
Mike Keane
DNA
-40
Omni
AAAAA
XPG
S0101
S0201
Matrix
Mike Keane
DNA
-40
Omni
AAAAA
XPG
S0101
S0201
B-1
Mike Keane
DNA
-40
Omni
AAAAA
XPG
S0101
S0201
9 x 9 4mL
Mike Keane
DNA
-40
Omni
AAAAA
XPG
S0101
S0201
9 x 9 2mL
Mike Keane
DNA
-40
View 3 Replies
View Related
Aug 27, 2007
can someone pls show me a way to get an unique sequence at below senario:
PC1 & PC2 using their own local client progam to access to Database Server at SERVER1.
In the SERVER1, there is a table SEQUENCE in a database DATABASE1.
And the table's structure of SEQUENCE are SeqType & SeqNo.
Here is the sample data:
SeqType SeqNo
Invoice 100
DeliveryOrder 200
Now, how to prevent PC1 & PC2 to get a same Invoice No. if they request the Invoice No. at the same time?
Is it possible to lock the record Invoice when i perform a SELECT statement, then i update the Invoice to 101, lastly release the lock for Invoice?
pls advise. thanks.
View 1 Replies
View Related
Oct 19, 2007
Greetings,
I am running a proc (PROC1) that gets one record at a time and does some work against it.
select min(myid) as NextID
from tbl_ListOfThingsToDo
where AlreadyDone is null
Then once I have the MyID, I set the 'AlreadyDone' bit to True so that no one else picks that record up.
update tbl_listofThingsToDo set AlreadyDone=1 where MyID=(from above)
If there is only one PROC1 running, this is no problem. He just keeps grabbing the next record to operate on.
--but--
If I kick off multiple instances of PROC1 for scaleability reasons, how can I make sure that only one instance gets the next record? For example, couldn't the second instance get the same myid BEFORE the first instance has written that he already got it?
Design is to run 3-5 instances of this to get extra cycles.
Let me know your thoughts, and thanks..
Dan Ribar
View 3 Replies
View Related
Feb 10, 2014
I have a table (Billing Table) having multiple records for people having one record per person per each month.
How to get a list of the guys having record just for one month (say feb) and doesn't have any other months.
View 4 Replies
View Related
Sep 23, 2014
Table :StudentTeacherRelation
Id StdId TeacherName Day subject
1 1 Archana Monday English
2 1 Archana Tue Marathi
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi
6 2 Archana Mon english
7 2 Dipti Tues Hindi
Second table : Student
Id Sname Cid
1 Shalini 1
2 Monika 1
3 Rohan 3
I want to fetch uniq combination of stuid and subject.Result should show all subject of student whether may be teachername and day. If I choose shalini whose stuid is 1,all subject for shalini(hindi,english,marathi) should come. Record from either of three should come
Id StdId TeacherName Day subject
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi
I want fetch studentname along with teachername,day and subject whose cid = 1 here is my query
select Student.Sname,TeacherName, Day,subject
from StudentTeacherRelation
inner join Student
Student.id = StudentTeacherRelation.StuId
where cid = 1
I want place result of it in temp,Want fetch max(id) from temp table by doing group by on Sname and Subject.find all id from temp table where that id present in max id.
show
Id StdId TeacherName Day subject
where (1,2,3,4,5,6,7-- all id from temp) in (1,2,5,6,7 -- max id from temp by doing group by on Sname and subject)
So it will show record Id StdId TeacherName Day subject where id is 1,2,5,6,7.Only five record should come.How to do that?
View 1 Replies
View Related
Feb 21, 2008
Hi Guys, I came here again to ask something about my application, Is there a way to make 2 columns unique with each other even if they don't have a primary key? If there's a way how? I wanna make a rule so, my application wont replicate its record. Graciaz,Nhoyti
View 3 Replies
View Related
Feb 13, 2004
I use the identity = yes for my unique columns in most of my tables that need it. I am trying to decide if I should change identity = no, and instead manually update my unique number column myself by adding one when I insert new rows.
The reason I want to do this is for maintainabilty and ease of transfering data for backup to other sql servers. I always have trouble keeping the identity numbers to stay the same as they are in the original database because when they are transfered to a db that has identity = yes, the numbers get rearranged.
It will also make it easier to transfer data from original db to another sql server db and use the data right away without having to configure the destination db to disable identity and then enable it, etc.
Any pro's con's appreciated,
Dan
View 8 Replies
View Related
Apr 19, 2013
I find to be able to have multiple NULL entries in the following constraint:
Code:
CREATE UNIQUE INDEX my_uidx ON my_table(my_col1,my_col2) WHERE ??? IS NOT NULL;
But is not possible to check multiple columns in "WHERE".
I using SQL Server 2012 Express
View 3 Replies
View Related
Mar 7, 2006
Hi i am designing a database. It will be used by field guys who will insert new records and will replicate at the end of the day with a central database using merge replication. Can anyone tell me if I am choosing wisely or not with the following 3 points:
1. i am going to use a uniqueidentifier value in a column (using newid()) for the merge replication. (I don't care that it's an ugly value as i never plan to look at it.
2. I am using an identity column (INT)to create a unique value within the table
3. I want to create a unique column comprising of data from other columns (e.g. date+identity+salesrep) This gives me an intelligent candidate for a primary key. But I think i have to create this with an instead of trigger (is that right)
many thanks in advance
ICW
View 5 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
Feb 12, 2006
Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.
I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)
CREATE TABLE T_FK (ID1 INT, ID2 INT)
And I want to do:
ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)
I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.
Thank you.
Have a nice day.
View 4 Replies
View Related
Feb 23, 2006
my table :
CREATE TABLE [dbo].[users] (
[ID] [int] NOT NULL ,
[A1] [nvarchar] (100) NULL ,
[A2] [nvarchar] (100) NULL ,
[A3] [nvarchar] (100) NULL
) ON [PRIMARY]
i must keep ID columns as primary key
ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
but now A1+A2 must be unique
how can i do it ?
thank you
View 5 Replies
View Related
May 15, 2008
hi guys,
i have 3 tables (T1, T2, T3), each with the same structure:
ID1 -> char(10)
ID2 -> char(12) NULL
ID3 -> char(10)
Value1 -> money
Value2 -> money
Value3 -> money
Note1 -> Text
Note2 -> Text
ID1+ID2+ID3 is the clusterd unique key in each table
what i want:
ID1, ID2, ID3 (with distinct occurencies of all 3 tables), T1.Value2, T2.Value2, T2.Value3, T3.Value1, T3.Note1
what i tried is to get all possible rows with
SELECT ID1,ID2,ID3 FROM T1
UNION
SELECT ID1,ID2,ID3 FROM T2
UNION
SELECT ID1,ID2,ID3 FROM T3
but i dont know how to join or add the other columns. maybe with
WITH RowList (ID1,ID2,ID3) AS ( ... code above.... ) SELECT ...
any ideas?
thx
View 10 Replies
View Related
Oct 31, 2005
Can you create a unique constraint on multiple columns, or does it haveto be implemented as a unique index?If possible can someone please post some sample code?Thanks,
View 4 Replies
View Related
Jul 20, 2005
Hi,I would like to add a unique index that consists of two fields in atable.e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combinationmust be Unique.Can anyone tell me the actual sql syntax to create this index?Thanks,June.
View 3 Replies
View Related
Feb 9, 2007
Perhaps one too many 2000 DTS packages have permanently damaged my ability to think clearly - however, I've find myself very frustrated attempting to create a SSIS Data Flow which replaces a very simple 2000 DTS package.
Take data from table1 in database1, put it in table2 in database2. Table2 in Database2 has an additional column as part of the primary key - so I need to add an arbitrary unique value in each row as it's inserted. Previously, I did this in the transformation script through a variable I incremented.
What's the recommend method to do this now - since row level processing of variables seem to be a no-no?
View 5 Replies
View Related
May 31, 2007
In tables where reporting or historical information needs to be maintained I find that logical deletion of rows is better than actually deleting the row and any FK relationships. However when one of the columns in the table must be unique this introduces a problem because uniqueness only matters for "active" rows. I'm hoping someone can provide some advice on how to best handle this situation.
Here is an example to work from. Suppose we have a table that contains job positions. The table contains an IDENTITY column for the PK (to avoid duplicating position names), a Name column that must be unique and an IsActive column that is set to 0 when the position has been "deleted". This setup allows for historical reporting to properly pick up the position information even though it might no longer be used in a company. You can substitute your own scenarios here as there are quite a few.
Now if the Name column is marked as a unique column then the DB will enforce integrity which is what we want. However we will want to be able to "delete" a position but later add a new position with the same name. One could argue that we should just "undelete" the original position but that is not often a good idea so assume "undelete" is not an option. Given the unique constraint a new position could not be created if an existing position (active or otherwise) already existed with the same name. We could set up the unique constraint to include the Name and IsActive columns but now we are prevented from ever deleting two positions with the same name.
My initial thought is to create a new UniqueName column that is unique and remove the uniqueness constraint on the Name column. The UniqueName column is set (via a trigger) to the Name column when the position is active. When the position is deleted the trigger changes the UniqueName to include a timestamp or something to ensure uniqueness. This is handled by the system and not exposed to clients.
Any advice or alternatives on this approach (preferably with advantages and disadvantages)? Thanks.
Michael Taylor - 5/31/07
http://p3net.mvps.org
View 4 Replies
View Related
Aug 14, 2006
We have an app that threads together emails coming out of Exchange, using their messageid. To ensure threading works correctly, we need to ensure uniqueness of messageid, which we do with a unique index (we also need to be able to lookup by messageid when a message comes in).
We are currently porting the app from Oracle and PostgreSQL to SQL Server and are having problems with the 900 byte max length of an index. The problem is that the maximum size of a messageid (according to the Exchange docs) is 1877 bytes.
How can we guarantee uniqueness?
View 7 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
Jan 19, 2008
Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example:
Row 1 - Column 1 = "cool"
Row 1 - Column 3 = 91
Row 3 - Column 1 = "cool"
Row 3 - Column 3 = 91
I dont care about one column having duplicate values, I want to protect against Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?
View 4 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
Dec 30, 2004
I try to search my data and sort the result by importance.
I'm using a MS Access database and my data (table1) looks like this:
Code:
ID NAME TEXT
1 Apples Good red apples
2 Bananas Fine yellow bananas
3 Yellow apples Great yellow apples
I want to search the data and get a result where the column "NAME" is more important than "TEXT". My SQL looks like this:
Code:
SELECT id,name,text,1 AS searchorder FROM table1 WHERE name LIKE '*yellow*'
UNION
SELECT id,name,text,2 AS searchorder FROM table1 WHERE text LIKE '*yellow*'
ORDER BY searchorder
The output is this:
Code:
ID NAME TEXT SEARCHORDER
3 Yellow apples Great yellow apples 1
2 Bananas Fine yellow bananas 2
3 Yellow apples Great yellow apples 2
So far so good - the order by importance works - but I do not get unique columns because of the searchorder column.
Can I fix my SQL so I get unique columns where the last line of "Yellow apples" does not appear or am I lost in space?
Best regards,
Peter from Denmark
View 2 Replies
View Related
Jun 20, 2008
Hi Guys.
I have a problem. I have two feilds in a table. Both of them are (int). I want to be able make sure the same number is not put in both columns for each row. Both colums have to be a different number, and also if one is null, the other cannot be. How do I go about enforcing this policy?
For Example:
This is what I want
ID Column1 Column2
--- -------- ---------
1 5 8
2 5 7
3 null 10
4 6 2
This is what I DO NOT want:
ID Column1 Column2
--- -------- ---------
1 5 5
2 5 5
3 7 7
4 null null
I created a unique constraint between the 2 columns, but it still allows dups within the row. Please help me.
View 1 Replies
View Related
