Quick Question: How To Force Uniqueness In SELECT Results?
May 31, 2008
All- I have what i'm sure is a question with a simple answer: Supposing I want to modify the select command below to force the display of records with only unique combinations of the two fields person_id and act_session_id (bolded). How would I do this. Note that neither is a key field. (Key field headcount_id was added for completeness.)
Thanks!
SELECT hd.headcount_id, hd.person_id, hd.act_session_id
FROM headcount as hd
I am using SQL Server Express and ASP.I have a table that contains news articles, headlines, start and enddates. I am trying to create a recordset that shows all of thearticles that are greater than or equal to the start date and lessthan or equal to the end date. For some reason I cannot get it tofunction properly using a statement similar to StartDate >= Now() ANDEndDate <= Now(). SQL doesnt like the Now() piece of the statement.Anyone have an idea how I can get this to work? I have also triedCURDATE() but to no avail.Please help.
Hi all. Just a quick question for you guys...I did a search on Select @@identity and I'm not sure I understand it. The program I'm migrating over (from sql server to oracle) has "Select @@identity" at the end of some insert statements. I'm just tryin to figure out what this does and what the Oracle equivlant would be? i have read posts(or in this case a blog) like this http://weblog.anthonyeden.com/archives/000054.html that states it gives you the value of the PK.
I've also read this http://www.kamath.com/tutorials/tut007_identity.asp stating select @@identity is handy to use as a "connection specific global variable." This all makes sense, however in the code I am examining select @@identity is not assigned to any variable. Basically, my question is if select @@ identity is not assigned to a variable in your code, what use does putting it at the end of a select statement serve? thank you
how would i write it to where i select a value = 0 where all are equal to 0 rather than one line. when selecting based off a key from another table how would i select the values that all bring back 0 for that key rather than just a line or 2? i hope that makes sense, lol.
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
Hi all, I copied and executed the following sql code in my SQL Server Management Studio Express (SSMSE): --SELECTeg8.sql from SELECT-Using correlated subqueries of MSDN2 SELECT Examples--
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product p
WHERE EXISTS
(SELECT *
FROM Production.ProductModel pm
WHERE p.ProductModelID = pm.ProductModelID
AND pm.Name = 'Long-sleeve logo jersey') ;
GO
-- OR
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN
(SELECT ProductModelID
FROM Production.ProductModel
WHERE Name = 'Long-sleeve logo jersey') ;
GO
========================================= I got: Results Messages Name o row affected ======================================== I think I did not get a complete output from this job. Please help and advise whether I should search somewhere in the SSMSE for the complete results or I should correct some code statements in my SELECTeg8.sql for obtaining the complete results.
select computer, count(*) as MissedCount from WInUpdates_Neededreq WHERE LoggedDate BETWEEN DATEADD (DAY, - 5, GETDATE()) AND GETDATE() and LastReportTime !< DATEADD (DAY, -5, GETDATE()) group by computer
I need to make a join onto another table but don't want to lose the coutn(*) as MissedCount.
How can I join to another table and still keep the count form the original table. I want ot join to tblogons.workstationname and return computer from the original query...
I need to investigate about what happened to our production server at the last weekend.i restored it to another server which is development. I restored it under a name "old_master_2015_07_10".
But if I run a query
SELECT * FROM [old_master_2015_07_10].[sys].[servers]
it actually selects from master.sys.servers, not from my old_master... In order to prove it, I created a linked server in this, development server, and if I run SELECT * FROM [old_master_2015_07_10].[sys].[servers], it selects it. And in database selection drop-down box I also selected old_ master_ 2015_07_10. What I think it apparently recognizes familiar names like sys.servers and redirects the query to the master.
What I can do to select really from old_master_2015_07_10 database? I already thought about renaming sys.servers to something different, but did not do it not to break something in master in case if SQL Server will run it in master as well.
When I select a fieldname in a db as a primary key this will keep the uniqueness of that fieldname, but how is that work when I select 2 fieldnames (in Enterprise Manager) and click on the Key it will mark them as my primary key, but I can insert duplicates values in either fieldname.
this violates the constraint if i give the two teams on the same date..how to give the condition such that it checks for the violation of condition if the team names are interchanged.. for eg
if i give the values as Insert Into Games values('chelsea',1,'arsenal',2,'17-1-2000'); and Insert Into Games values('arsenal',1,'chelsea',2,'17-1-2000');
the above should violate the constraint ..how to do it
I have one query which uses a join query to gather all the projects that should show up in someone's list over a period of time (returns and id (int) and name (varchar) paired dataset). I want to do a separate query that takes that list and selects all projects (same paired set ... id and name) EXCEPT where it matches an id on a row of the given result set. The one query looks like this ..DECLARE @startDate datetimeDECLARE @endDate datetimeDECLARE @userId UNIQUEIDENTIFIERSELECT @startDate = ppStartDate FROM ppTablewhere payPeriodID = @payPeriodIDSELECT @endDate = ppEndDate FROM ppTable WHERE payPeriodID = @payPeriodIDSELECT @userId = userID FROM usersTable WHERE userName = @userNameSELECT DISTINCT p.projectID, p.projectNameFROM projectsTable pLEFT JOIN projectMemberhsip m ON m.ProjectId = p.ProjectIdLEFT JOIN timeEntryTable t ON t.ProjectID = p.ProjectIdWHERE t.TimeEntryUserId = @userID AND t.TimeEntryDate >= @startDate AND t.TimeEntryDate <= @endDateORm.UserId = @userID I want to get the same selection from projectsTable WHERE it's not anything from this result set.Haven't been able to get it by modifying the WHERE logic. Is there a way to select all WHERE id != (resultSet from this SELECT)? TIA!
My company is upgrading one of our products; this involves some data migration and a question about existing indexes with uniqueness constraints has been raised. Here is the issue:
We have a database table with two particular columns that that we index with a uniqueness constraint. My question is: is the uniqueness constraint case sensitive?
For instance, if I have a database that is using a collation of SQL_Latin1_General_CP1_CS_AS. (case sensitive) and I insert data in these two columns as follows:
test col2test
Test col2test
would this uniqueness constraint be violated even though I'm using a case sensitive collation? I've done tests on this issue and I can't seem to insert this 2nd column; I'm just wanting to be certain that this is the case 100% of the time.
Hi, Can anyone tell me the order in which uniqueness constraints on indexes are enforced vs. when triggers are executed ? I have a unique constraint on an index and a trigger on the column on which the same index has been created. When a row is inserted, the trigger checks if the value for that column already exists in the table - if not, it inserts the row as is, else it gets the max() val of the column (based on another key column) and increments it by one, then does the insert. Creating an index across the two works fine, but if I set the Unique Values property for the index, subsequent inserts bomb out - yet there aren't any duplicates in the final table, as the trigger ensures this. Anyone got any ideas on this? My deduction is that the uniqueness constraint gets enforced before the trigger gets executed, but at the same time this *seems* illogical, as the row has not been inserted into the table at the point where the trigger is executed.
OK - maybe my Google-fu is off today but I'm trying to find out if using a sequence table will provide values that are as unique as the GUID is supposed to be.The reason is that I'm building a DW from the ground up (they won't let me expense my booze which I think is grossly unfair ) and rather than using GUID it ~seems~ like a sequence table would give better results. My concern is that the values won't be unique across tables.
Hi,First of all my apologies if you have seen this mail already but I amre-sending as there were some initial problems.This query is related to defining indexes to be unique or not andconsequences thereof.Some documented facts that I am aware of include1. Defining uniqueness allows optimiser to create optimal plans egselect based on keys in such an index allows the optimiser to determineat most only one row will be returned2. Defining uniqueness ensures that rule (business/Primary key) isenforced, regradless of how the data is entered.We have many cases where non unique indexes are defined. The approach todate has been that even though we are aware of some of the benefitsoffered by defining uniqueness , we have chosen not to add keys to nonunique indexes such that they become unique. The primary reason for thiswas that we did not want to make the keys comprising the indexesunnecessarily large and therefore ensuing consequences when DMLstatements are performed.However, I have concerns that having highly duplicate indexes can haveperformance impacts , including deadlocking. I am also aware Sybase usedto store duplicate values in overflow pages and therefore there wereperformance consequences. Could SQL 2000 have the same behaviour ?Thanking you in advancePuvendran*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
So, I have this idea of a database design I was hoping to get some input on. I usually go through the process of creating a separate column for identity, like an auto-increment integer which serves as a context specific ID that easily can be passed around. However one of the short-comings I've come to realize is that the integer is just that, unique to the context and requires context information to make sense. I'm pounding the idea of creating a separate table whose sole purpose is to provide global (database wide) unique IDs. As such each row in a table can still have an ID but instead of being tied to a specific context it will always make sense within the application. A foreign key constraint can ensure that and a relation. Now, with a global ID which I can create FKs to, I don't have to create a separate table for relations between entities and can have relations through these global IDs and I think that's a good thing because if there are many relations between many entities they don't have to be defined more than once. There is also a single "hub" where all relations fit which will allow me to access most information through some relatively simple joins. My concerns are performance and Linq to SQL implication. I'm worried that Linq to SQL might start pulling an excessive amount of data and I'm worried that the practicality of such a design might hurt the long running of the application development process. But I'm surprisingly interested in what it could mean for a more ubiquitous view of data and tagging. Basically a way of any data to have relations to any other data through this yet simple design decision. If you think this is a good idea or see no particular problem with, please let me know! If you think there's problems with my idea, please motivate your critic but do tell me what I should look out for, or if you know of any better approach to my idea of being able to look at the data through some more common mechanism (which applies to all tables and the entire database mostly from a programming perspective).
I want to make sure that usernames and folder names (that are stored in the database) are all unique when created by the admin through an admin control panel.
How would I code and use an Index to make sure that the 2 columns are unique?
I've looked at examples on the net but nothing is telling me how I can create an index through Enterprise Manager? Thanks
I want to make sure that usernames and folder names (that are stored in the database) are all unique when created by the admin through an admin control panel.
How would I code and use an Index to make sure that the 2 columns are unique?
I've looked at examples on the net but nothing is telling me how I can create an index through Enterprise Manager? Thanks :)
I"m trying to use a BULK INSERT command to insert data into a table from a file. There is a UNIQUE Index that is being violated and the BULK INSERT fails.
I do not want to drop or disable the index, however, i also do not want to load 'duplicate' records so i keep the CHECK_CONSTRAINTS parameter.
Is there a way to have the duplicate records outputed to the ERRORFILE ?
Is there a system table column to query to determine whether an index is defined as unique in SQL Server 2000? It is easy to find this info in SQL Server 2005, but I don't see a valid column for this on dbo.sysindexes.
I am reading "SQL Server Query Performance Tuning Distilled",on page 104 it talks about one of the index design recommendationswhich is to choose the column that has very high selectivity of valuesinstead of a column that has very few selectivity of values.My question is if I have currently indexes on my tables that have1, 2, 3, 4, ... values only on thousands of rows, are these nonclusteredindexes pretty much useless indexes that I should get rid of?And I know that pretty much the number of selectivity values willalways remain very low.Thank you
I am trying to round this data, but can't find a good resource on this. If my results are 15.6 I need it to round down to 15.5 and if my results are 15.4 I need it to round down to 15.0.ROUND(DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate - SUM(ISNULL(request_1.request_duration, '0')), .5) Any help would be greatly appreciated. A link to a good reference on rounding would help too.Thanks in advance!!!
I need to insert in TableB the results of a select over TableA so i used:
Insert into TableB (col1, col2, col3) select c1, c2, c3 from TableA
and works OK.
But now i need to use a Stored Procedure "Insert_in_TableB" that handles all the process of checking values, inserting data, logging steps, etc etc. That receives via its parameters the values for validate, etc and then insert them in TableB.
So, how can i execute that procedure (and define its parameters) in a easy way? Something like
Exec Insert_in_TableB (select c1, c2, c3 from TableA) or Select c1, c2,c3 into Insert_in_TableB...
I am creating a database in SQL2K for collating test results taken at regular intervals across several different sites and frequencies. The table will look something like this:
SiteDate Data A01/01/2013 ... B02/01/2013 ... C03/01/2013 ... A04/01/2013 ... B05/01/2013 ... C06/01/2013 ... And so on...
In total there will be about 300 sites, with up to 12 records per site every year. I want to be able to create a view showing the 4 most recent results for each site. Is there a simple way of doing this? Obviously getting the most recent result for each site is quite straight forward, but I can't work out how to get the last four. Unfortunately this is on a 2k box, although I could (at a push) use a 2k8 box if needed.
I want to create a stored procedure that returns a list of records froma table. But depending on a userID value given only certain recordswill be returned that they have access to.I think this might be hard to do in a single SELECT statement becausethe user might also belong to a group that might have permission, etc.Can you do something like this pseudo code in a T-SQL procedure?DECLARE cur CURSOR FOR SELECT * FROM myTableOPEN curFETCH NEXT FROM curWHILE @@FETCH_STATUS = 0BEGINif( accessGranted(curRecord.id) ){ addRecordToResultSet() }else { ommitRecordFromResultSet() }END
I have two tables I selecting name using like with %string% from the two tables but I need to order the result comes from the two table: 1- the exact match for the search string come first from the two table. 2- and the partial match comes last after the exact match.
this is my DDL for the two tables :
USE [Northwind] GO /****** Object: Table [dbo].[Person] Script Date: 04/25/2008 14:33:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Person]( [PersonID] [int] NULL, [Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
second table: USE [Northwind] GO /****** Object: Table [dbo].[Members] Script Date: 04/25/2008 14:33:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Members]( [MemberID] [int] NULL, [Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF
and this my search query I have it in a stored Proc.
select *
from
(
SELECT PersonID, Type, Name, email
FROM Person WHERE (Name LIKE '%'@Name'%') union all
I have an SSIS conversion issue. I'm pulling two tables from a DB2 database into SQL 2005. One table has a list of work orders, and the other has a list of work order comments. There is a unique identifier between the two tables so that a join can be used, however, due to size limitations, I need to be able to combine both tables.
The end result will be replicated out for SQL Mobile Edition and the file is too large when both tables exist so I am wanting to concatinate all the comments for each work order into a single text field in the work orders table.
Here is what I am wanting to accomplish:
UPDATE tblWorkOrdersSET Comments = (SELECT Comments FROM tblComments WHERE tblWorkOrders.ReqNum = tblComments.ReqNum)
I know that this statement will not work because there is a one-to-many relationship between the tables so each work order could get multiple results.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
I have the following select statement on my page: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:prbc_hrConnectionString %>" SelectCommand="SELECT emp_lname + ', ' + emp_fname + ' ' + emp_minitial + '.' AS emp_fullname FROM employee WHERE (emp_username = @emp_username)"> <SelectParameters> <asp:SessionParameter Name="emp_username" SessionField="Username" Type="String" /> </SelectParameters>I want to say "Welcome, emp_fullname" at the top of the page, but can't figure out how to write the results to the page. I am moving to ASP.NET 2.0 from PHP and am banging my head against the wall trying to figure out how to do these little things. I appreciate any help you can give.
my question is what all can i bind my results of a select query to.i know we can use Dataset and Sqldatareader. can we use something else like hashtables. for example we can have:reader = cmd.ExecuteReader(); can we have something likeHashtable = cmd.ExecuteReader();
Hello! I've got the following procedure: ALTER PROCEDURE [GetTimeDiff2] (@ID int) AS select A_ProspectPipeline.ID, (case when [Completion Date] is null then '13' else case when YEAR([Completion Date])>year(GETDATE()) then '13' else case when YEAR([Completion Date])<year(GETDATE()) then '1' else month([Completion Date]) end end end)- (case when YEAR([Start Date])=year(GETDATE()) then month([Start Date]) else case when YEAR([Start Date])<year(GETDATE()) then '1' else '13' end end)as [CY],
(case when [Completion Date] is null then '13' else case when YEAR([Completion Date])>year(GETDATE())+1 then '13' else case when YEAR([Completion Date])<year(GETDATE())+1 then '1' else month([Completion Date]) end end end)- (case when YEAR([Start Date])=year(GETDATE())+1 then month([Start Date]) else case when YEAR([Start Date])<year(GETDATE())+1 then '1' else '13' end end)as [NY]
from a_ProspectPipeline where A_ProspectPipeline.ID = @ID
What i need to do is insert the two returned values [NY] + [CY] into two different tables. Can anyone help me with this?
I need to keep track of the number of hits on a particular page. Im using a stored Procedure
What I want to do is get the number of hits and increment it by one :)
ie: Sub Procedure should be like below
SELECT noOfHits WHERE pageName = 'bla bla'
noOfHits = noOfHits + 1 etc.
Also, some of the pages will be added and deleted all the time, so before I increment the noOfHits variable I need to check that the pageName 'bla bla' exists. AND if it doesnt I need to create a pageName called 'bla bla'
What I need to do in essence is:
1. Check that a particular row exists. if it doesnt create it. 2. Increment a value (by one) to a column in this particular row.
Phew. Hope you got that. Any ideas much appreciated,
