I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
I am in a situation where I need to find out unique field names with different values in a table having 200+ columns. Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values. Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values. I would appreciate If anybody gives me hint about achieving desired result
I have a table which maps two related IDs. That table has 3 columns: ID, BHID & EPID. I need to find all of the BHIDs where the EPID is unique. It seems easy enough, but I keep going in circles..
USE [CGB] GO /****** Object: Table [dbo].[ePID_BHID] Script Date: 04/15/2015 15:48:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
And I want to end with a table that has every unique combination of these records (the order being reversed would be considered 'unique' in this context)
hai I have to delete a single row from a table , where there are multiple rows with the same data and i want to delete only one row and i cannot alter my table structure. Thanks in advance satya
I am having a bit of difficulty trying to work how to write a query. I am normally only used to doing SELECT, UPDATE and DELETE queries ... the simple ones.
However I have come across a query whereby things get a lot more complex.
My company has an employee table with a way to store data that I have not been used to.
EMPLID, NAME, DATELASTMODIFIED, EmployeeStatus. (There are lots more but N/A)
The problem that I have is that there can be more than one row for an employee id. Therefore I cannot uniquely identify an employee id. Instead what I have discovered I have to use is to find an employee with the last modified date higher than the other rows with the same employee id.
The query I am trying to achieve is:
Code:
Count all employees that have an employee status of 'Employed'.
(Therefore counting only one employee per id rather than counting the same employee more than once)
The trouble is, translating this into a SQL query. I have tried to have a go but don't know how to query:
Code:
SELECT COUNT (DISTINCT EmplId)[ FROM Employeetable AS e WHERE e.LASTDATEMODIFIED <And Stuck> AND EmployeeStatus = 'Employed'
So I thought hmm lets pseudo code this, but then I get stuck into compelixty:
Code:
SELECT * FROM EmployeeTable WHERE EmployeeStatus = 'Employed'
int counter = 0; ForLoop (EmplId) { LastDate Date if(LASTMODIFIEDDATE > LastDate) { LastDate = LASTMODIFIEDDATE } //How do I return the value? Do I count the value? }
Has anyone come across this issue before? Can anyone help it quite fundemental to what I have to query so help on this is highly appreciative.
I have a scenario here where the data looks like -
IDÂ Â Â Â Â Â Â Date 100Â Â Â Â Â Â 07/01 100Â Â Â Â 07/02 100Â Â Â 07/03 100Â Â 08/01 100Â Â 08/02 100Â Â 08/15
Now I need to find out unique occurrences of ID - 100 ( where count =Â unique only if the occurrences are in consecutive days, gap of even 1 day causes it to be a different instance ) - SO with the above data I should have unique occurrences as 3.efficient way to calculate this ?
Hi I am new to SQL. My scenario is I get a string from the user I need to match this string with value present in 2 cloumns. If the string is present in either of the column then i should select that particular record from the table. Now my question is if i Have to match with one cloumn i can used charindex function is there any function with which i can match the string with data present in more than one cloumn.
I want to write a query to bring out any records in a table where any of its fields contain a null value. I could do it in this way:
select *
from customers where name is null or role is null or email is null u003c/p>u003cp styleu003d"margin:0cm 0cm 0pt">u003cfont faceu003d"Times New Roman" sizeu003d"3">u003cfont coloru003d"#000000">However I want to perform a similar function for a number of tables and the method above would mean more sql and maintenance.u003cspan>Â u003c/span>I was hoping there was a simple way to do the same thingu003cspan> ie find any rows with a blank fieldu003c/span>?u003cspan>u003cfont faceu003d"Arial" sizeu003d"2">Â u003c/font>u003c/span>u003c/font>u003c/font>u003c/p>u003c/span>u003c/font>u003c/div>",0] ); D(["ce"]);
//--> However I want to perform a similar function for a number of tables and the method above would mean more sql and maintenance. I was hoping there was a simple way to do the same thing ie find any rows with a blank field?
I have a web form with a check box list with 5 values. Each of them is an int value, and the user can select multiple values from the check box list. For example:
Status:
1) Single 2) Married 3) In Relationship 4) Divorced 5) Any
These values correspond to a column called "RelationshipStatus" which holds an int value of NULL or 0-5.
So the value passed to my SQL query would be any combination of 1, 2, 3, 4, or 5 (Which denote any of the above). It could look like this:
@Status = '132'
Now my question is how would I do a select statement that finds any row with one of those value (Any row with 1, or 3, or 2)? If there is a '5' in the varible then the select statement should return any row regardless of the value.
Table Name: "USER_TABLE" Column Name: "Relationship_Status" Value Type: INT
I have a query I am currently attempting to optimise. Â The query joins in on a sub table (queries the same table) to garner a maximum date value for each row to display. Â For example:
SELECT Column1 ,Column2 ,DateColumn FROM Table1 T INNER JOIN ( SELECT Column1 + Column2 AS ConcatColumn, Column3, MAX(DateColumn) AS dt FROM Table1 GROUP BY Column1 + Column2 ) X ON X.ConcatColumn = T.Column1 + Column2 AND X.dt = T.DateColumn
Is there any way I can write this another way (preferably more cleaner/optimised)?
There are around 6 more classes which I haven't mentioned. The resultant table that I need would be something like this.
Sections Class1 Class4 Others
-------------------------------------------------
A 30 40 50
B 40 10 25
C 20 5 15
Total 90 55 90
What I want is the top two maximum values to appear and everything else to be clubbed into Others and correspondingly their numbers to be summed up i.e. Dynamically depending on the total only those classes with maximum 'Total' value to appear.
Hi all...I've got a group of tables and I wanted to first see which tablescontained a fields containing the "Date/Time" data type. And I wassuccessful in doing so...here's the query:(select a.name tablename, b.name colnamefrom sysobjects a, syscolumns bwhere a.name = object_name(b.id)AND B.TYPE = 61 AND A.XTYPE = 'U')Now...my only issue is find which query I can run (On each table)which returns to me ONLY the values containing the date/time stampwith milli seconds (Ex: 01/01/2007 10:10:50:987)I require this since we are planning on shifting those tables to theOracle platform, and we've had issues doing this while loading theextracted information, since it was not taking the values containingthe "milliseconds".Is there a way to do this? Or is it better to simply remove themilliseconds all togeather? I wouldn't mind doing so provided that itdoesn't includes changes to the actual data type.
Here's what I'd like to be able to do: I have a queue that holds any number tasks, so something like this here:
queue_1 task_a
task_b task_c task_d
Workers are assigned to teams, Red team, Blue team, Green team. What I need to do is identify instances where all tasks for a given queue have been handled by one team. Once a task has been assigned to a queue any team can work on it, but when only one team has completed every task in a queue a bonus should be awarded.
I'm looking for this to award a bonus: queue_num task_num team queue_1 task_a red task_b red task_c red task_d red
No bonus for any team here queue_num task_num team queue_1 task_a red task_b blue task_c red task_d green
So the red team earns a bonus. Now, I have thousands of queues each containing any number of tasks. Using T-SQL how can I find all queues where only one team was responsible for completeing every task assigned to the queue? Do I have to use a cursor and eval each task coming through or is there a faster, more efficient way to handle this in SQL?
SELECT * INTO TEMP FROM (SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '01-01-2014' AS STARTDATE, '01-31-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '02-01-2014' AS STARTDATE, '02-28-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '03-01-2014' AS STARTDATE, '03-31-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A2000' AS CODE, '04-01-2014' AS STARTDATE, '04-30-2014' AS ENDDATE UNION SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '05-01-2014' AS STARTDATE, '05-31-2014' AS ENDDATE) X
I need to extract the date that the value in CODE column changes to another code for each value of CATEGORY and if there is no change, to record the original CODE value and its startdate for each CATEGORY.
INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd) SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL
Basically I'd like to mark the first Activity for each OpportunityID as a First Visit if its ScheduledEnd falls on the same day as the FirstVisit, and otherwise mark it as a Repeat Visit.
I have this so far, but it doesn't pick up on that the ScheduledEnd needs to be on the same day as the FirstVisit date to count as a first visit:
SELECT*, CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit) WHEN ScheduledEnd THEN 1 ELSE 0 END AS isFirstVisit, CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit) WHEN ScheduledEnd THEN 0 ELSE 1 END AS isRepeatVisit FROM#Visits
Hi all!In a statement I want to find the IDENTITY-column value for a row thathas the smallest value. I have tried this, but for the result i alsowant to know the row_id for each. Can this be solved in a neat way,without using temporary tables?CREATE TABLE some_table(row_id INTEGERNOT NULLIDENTITY(1,1)PRIMARY KEY,row_value integer,row_name varchar(30))GO/* DROP TABLE some_table */insert into some_table (row_name, row_value) VALUES ('Alice', 0)insert into some_table (row_name, row_value) VALUES ('Alice', 1)insert into some_table (row_name, row_value) VALUES ('Alice', 2)insert into some_table (row_name, row_value) VALUES ('Alice', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 2)insert into some_table (row_name, row_value) VALUES ('Bob', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 4)insert into some_table (row_name, row_value) VALUES ('Celine', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 6)select min(row_value), row_name from some_table group by row_name
What would be the best way to insert unique values into a table/unique column ? I cannot make that table/unique column as indentity. Right now, I use a staging table with indentity column, insert rows then insert rows back to final table.
Hi,I have two tables such that in each table I need to make sure thatcolumn x in table A and column y in table B have a unique valuesmeaning that a user cannot insert a value to column A if its alreadyexist in column B and vice versa.How can I enforce it? Please remember that this two different tables.Thanks,Ori.
Hi, I have been asked to write some code that can check a large table for duplicate values in a non pk column. The table may have up to 1000000 rows. The PK column is an auto increment field. For performance reasons the column in question could not be set to unique values only for inserts, an algorithm is used to create unique no's before the insert but what I am doing is double checking that their have been no duplicates created accidently. If their are duplicates I need to know what rows they occurred on.
Item Color Quantity -------------------- -------------------- -------------------------- Table Blue 10 Table Red 20 Table Yellow 30 Chair Blue 40 Chair Red 50
I'm wondering if there is a group state like this: Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item which returns this:
I need a query to return two values. One will be the total units and the other will be total unique units. See exmaple data below. It does not have to be one query. This will be in SP, so I can keep it seperate if I have to.
Total Units = 7 - easy to do by using count() Total unique units = 4 - I cannot use group by as it would return multiple results for each unit, which is not what we want.
I need a bit of help with a join. I have 2 tables :
TradeSummary has fields : SymbolID, CurrentPrice, TotalValue
Trades has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary and also get TradeValue from Trades for the record for max(ExecutionTime) tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
Hi I am using VS 2005 with SQL 2005. I had a Datagrid and i Need to bind data into it. I need to show Random Unique ID's based on the Parameter i pass. I wrote select top 5 * from Employee order by NEWID() for this i had created a Stored Procedure as create Proc Demo(@N int)as beginselect top @N * from Employee order by NEWID()end Its showing a syntax error near @N. How can i pass the parameter for Select Top @N * from .......
I have this query and it works except for I am getting duplicate primary keys with unique column value. I want to combine them so that I have one primary key, but keep all the columns. Example:
Key column 1 column 2 column 3 column 4 A 1 1 A 2 2 B 2 3 B 5 5
it should look like:
A 1 1 2 2 B 2 3 5 5
Here is my query:
SELECT * FROM [TLC Inventory].dbo.['2014 new$'] WHERE [TLC Inventory].dbo.['2014 new$'].mis_key LIKE '2%' AND dbo_Product_Info#description NOT LIKE 'NR%' AND dbo_Line_Info#description NOT LIKE 'OBSOLETE%'
I am trying to insert values into a table that doesn't exist there yet from another table, my problem is that because it is joined to the other table it keeps on selecting more values that i don't want.
Code Snippet
SET NOCOUNT ON
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID) SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'
WHEN 2 THEN '8'
WHEN 3 THEN '9'
WHEN 4 THEN '10'
END
FROM Members M
INNER JOIN _MemberProfileLookupValues ML
ON M.MemberID = ML.MemberID
WHERE M.Active = 1
AND OptionID <> 6
When i execute that code it returns all the values, let say OptionID = 3 is smoking already exists in the MemberProfileLookupValues table then it is going to select that persons memberID
I want to insert only members values that aren't already in the _MemberProfileLookupValues from the Members table (I think that it is because of the join statement that is in my code, but i don't know how i am going to select members that aren't in the table, because i have a few other queries that are very similar that are inserting different values, so ultimately
ONLY INSERT THE MemberID the values 6 and the statusID of X if it is not in the table already.
Any ideas / help will be greatly appreciated. Please help.
ID      Set Date         DB Date 100    Null                07/01/15 100    07/05/15        07/02/15 100    07/10/15       07/08/15
I want to able to get 2 unique dates
1. 07/02/15 - As I want the DB date for IDÂ - 100 when set date changed from a null value to non null value.
2. 07/08/15 - As I want the DB date for ID - 100 when a non null set date changes.
The table has such records for lot of different ID's.
I'm trying to use merge data from a staging table to a production table. There are a lot of duplicate values for serverName and I only want to insert one instance where there are duplicates.
How I can adapt the code I have so far to achieve this?
MERGE tblServer AS TARGET USING tblTemp AS SOURCE ON (TARGET.serverName = SOURCE.serverName)
WHEN MATCHED THEN UPDATE SET TARGET.serverName = SOURCE.serverName, TARGET.serverLocation = SOURCE.serverLocation
WHEN NOT MATCHED BY TARGET THEN INSERT (serverName, serverLocation) VALUES (SOURCE.serverName, SOURCE.serverLocation)
After adding a Unique constraint to a database I cannot add more than one record with a null value for the constrained field. I've tried both adding the constraint to an empty table as well as a table with multiple null values already in the subject field; both efforts have failed.
According to BOL SQL-7 allows Unique Constraints on fields with Null values. Am I missing a step? I do need to allow nulls in the field yet ensure that when there is a non-null value it is unique.
The SQL statement I've used is: ALTER TABLE tbl_MasterUIC ADD CONSTRAINT uniquesamplenbr UNIQUE NONCLUSTERED (samplenbr)
