How To Get Distinct Records In Table
Dec 25, 2013For examble:
In a table name like
1.Test
2.Test.
3.Test..
4.Test...
How will get the name "Test" using distinct keyword?
ADVERTISEMENT
How To Get Distinct Records In Table
Dec 26, 2013For examble:
In a table name like
1.Test
2.Test.
3.Test..
4.Test...
How will get the name "Test" using distinct keyword?
T-SQL (SS2K8) :: How To Get Distinct Records From Table
Apr 14, 2014Suppose I have 2 table
1)Main
2)History
Main table maintain all the records having columns MAIN_SKU,DEDUCTIBLE_AMT,model_id,catagory,ModifiedDate
IF DEDUCTIBLE_AMT is changes it will place entry in history table ,columns are same with history_id
i want to display distinct main_sku from history table(all columns) with last DEDUCTIBLE_AMT changed from history table
table structure
main table
MAIN_SKUDEDUCTIBLE_amtmodel_idcatagory
1100100phone
2150101phone
3200109smartphone
4100202smartphone
History table
History_idMAIN_SKUDEDUCTIBLE_amtmodel_idcatagoryModifiedDate
11150100phone4/14/2014
21200101phone4/13/2014
34109202smartphone4/14/2014
44101202smartphone4/13/2014
52200101phone4/13/2014
63100109smartphone4/12/2014
How To Copy Distinct Records From One Table To Another Using SQL 2005
Oct 24, 2007I have a table on an offline database having let say 2000 rows and same table on an online database with let say 3000 rows. Now I want to copy just those records (1000) from online database that are not in the offline database table. Also, both tables have same name, same schema etc.
View 3 Replies View RelatedCount Distinct In Large Table With 30M Records?
Jul 7, 2015I have a task to count distinct records in a big table with roughly 30M records, performance is an important factor. Query is to be written to calculate weekly stats, weekly record number could be as high as 1M.
The actual result is like:
ID Policy
350235744Credit Cards
350235744PCI
350235744PCI Audit
So the final number for this particular Policy is 3
I can write the query like:
select count(distinct Incident_id) policy_name
from Reporting_DailyDlpDetail
Where (year(INSERT_DETECT_TS)=2015) and (month(INSERT_DETECT_TS) =6) and (day(INSERT_DETECT_TS) between 2 and 9)
This returns 526254 and costs 11 seconds to complete
or a query like:
Select distinct Incident_id, policy_name
from Reporting_DailyDlpDetail
Where (year(INSERT_DETECT_TS)=2015) and (month(INSERT_DETECT_TS) =6) and (day(INSERT_DETECT_TS) between 2 and 9)
This returns 749687 and costs roughly 1 minute to complete.
Result is different from the two queries, I believe the later gives correct number. How can I count the distinct based on a combo?Considering the size of data, what is the best and most efficient way to run the stats calculation against over 30 different scenarios (different policies and alert types) and not timeout?
Trying To Get Distinct Records
Jan 7, 2008How do I get distinct TitleID and Titles? Right now I'm still getting duplicates on them both. Here's my stored procedure.
select Distinct (Titles.Titleid), Titles.Title as TITLE, classifications.[description]as TOPIC,Titles.descriptions,media.[description] as MEDIA
from Titlesjoin resources on resources.Titleid = Titles.Titleidjoin media on media.mediaid = resources.mediaidjoin titleclassification on titleclassification.titleid = titles.titleidjoin classifications on classifications.classificationid = titleclassification.classificationid WHERE Title LIKE 'p' + '%' GROUP BY Titles.titleid, titles.title,classifications.[description],Titles.[descriptions],media.[description]
Thanks!
Distinct Records
May 11, 2008hi,
i want to ask a simple question,
i have a sql server relational tables named "tbl_Contact" (w/c has a field of ID & ContactName) and "tbl_reviews" (w/c has ID,ContactCode & Reviews).
the tables relationship is one-to-many, now my question is, how can i display a single record per 'ContactCode' from 'tbl_review'??
thanks..
Only Get The Non Distinct Records
Oct 1, 2007I am trying to write a query that will only return the non distinct records
what i have is a bunch of line items that have an order id and a rate
there may be 10 line items with the same orderid, I need to get the orderid and rates for the orderids that have more than one rate
how would i do this?
Getting Distinct Records
Oct 16, 2007how do i get an entire record returned when looking for only column to be unique?
basically i have 3 columns
id, tranid, appliedtranid
tranid can have multiple appliedtranids
i need to grab the top 10 distinct tranids but i need the entire record
Get Top Distinct Records
Jan 31, 2008hi I am trying to write a query that will return the most recent status of an event
my query is below
SELECT DISTINCT eventname, MIN(Status) AS Expr1, MIN(RecordUpdated) AS Expr2
FROM EventQueue
GROUP BY AuditName
does this query always result with the most recent distinct eventname record?
I need to make sure that the recordupdated is the most recent for the eventname
Selecting Distinct Records
Sep 1, 2007Hi,
I'm just wondering if someone can help me with some SQL syntax stuff.I want to take this sql statement:
"SELECT TOP 50 tblProfile.chName, tblProfile.intCount FROM tblProfile, tblLinks WHERE (tblLinks.MemberID = tblProfile.MemberID) ORDER BY tblLinks.dtDateAdded DESC;"
and select only unique "chName's" records
Select Distinct Records
Oct 10, 2007Hello, I have the following tables: declare @B table (Bid int identity, description varchar(50)) declare @P table (Pid int identity, Bid int, description varchar(50)) declare @T table (Tid int identity, description varchar(50)) declare @TinP table (TinPid int identity, Tid int, Pid int) insert into @B (description) select 'B1' insert into @B (description) select 'B2' insert into @P (description, Bid) select 'P1', 1 insert into @P (description, Bid) select 'P2', 1 insert into @P (description, Bid) select 'P3', 2 insert into @T (description) select 'T1' insert into @T (description) select 'T2' insert into @T (description) select 'T3' insert into @TinP (Tid, Pid) select 1, 2 insert into @TinP (Tid, Pid) select 2, 2 insert into @TinP (Tid, Pid) select 3, 3 select * from @B select * from @P select * from @T select * from @TinP I need to get all records in T (Tid and description) which are related to a given BId So for @Bi = 1 I would get: Tid Description 1 T1 2 T2 So I need the distinct values. How to solve this? Thanks, Miguel
View 1 Replies View RelatedDisplay Distinct Records?
Dec 7, 2011TableName: Order_Archive
Fields:
orderid
load_date
filename
order_date
dollar
I load a file each week into a table, each file has unique orderid, load_date, filename, order_date and dollar. However the same orderid, order_date and dollar could appear in another file with different load_date and file_name.
File1:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-01', 'File1', '2011-01-01', '102'
'1002', '2011-01-01', 'File1', '2011-01-01', '103'
File2:
orderid, load_date, file_name, order_date, dollar
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'
Question:
whats is the best way to retrieve the distinct records that has the most recent load_date? expected results below:
Expected Results:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'
Count Distinct Records
May 15, 2008how can i count in sql the number or records taht would be returned if i did
select distinct site,date from allrecords
DISTINCT And Number Of Records
Mar 19, 2008Wondering if there is a way to do this...
I want to select the DISTINCT user name from each record in a table, and then have another field tell me how many records it found..
is there any way to do this with SQL?
SELECT DISTINCT user_Name FROM table ORDER BY user_Name
is what I have now....
need something that will return:
user_Name numRecs
bob 3
fred 6
sam 1
linda 2
Selecting Distinct Records Through CTE
Jan 11, 2013I have a table contains information related to sales:
SO number Order Date Customer SellingPerson
1001 2012/07/02 ABC Andy
1002 2012/07/02 XYZ Alan
1003 2012/07/02 EFG Almelia
1004 2012/07/02 ABC John
1005 2012/07/02 XYZ Oliver
1006 2012/07/02 HIJ Dorthy
1007 2012/07/02 KLM Andy
1008 2012/07/02 NOP Rowan
1009 2012/07/02 QRS David
1010 2012/07/02 ABC Joey
Now, i want to write a query using CTE that gives me first five distinct customer in result set:
SO number Order Date Customer SellingPerson
1001 2012/07/02 ABC Andy
1002 2012/07/02 XYZ Alan
1003 2012/07/02 EFG Almelia
1006 2012/07/02 HIJ Dorthy
1007 2012/07/02 KLM Andy
I wrote this query :
With t(so_number,order date,customer, SellingPerson)
as
(select top 5 so_number,order date,customer, SellingPerson from t)
select distinct billingcontactperson from t order by so_id
And getting this error:
Msg 252, Level 16, State 1, Line 1
Recursive common table expression 't' does not contain a top-level UNION ALL operator.
How To Select Distinct Records From 3 Or More Tables...?
Jun 1, 2001Attached is my select query, but it is returning the database values twice.
Can anyone tell me how I tell it to stop! A sub query I guess....
SELECT DISTINCT
dbo.Lead_Entry.Lead_ID, dbo.Lead_Entry.Lead_Source, dbo.Lead_Entry.Lead_Approved, dbo.Lead_Entry.Solution, dbo.Lead_Entry.Lead_Date,
dbo.Customer_Company.Company_Name, dbo.Customer_Contacts.Contact_Name
FROM dbo.Customer_Contacts INNER JOIN
dbo.Customer_Company ON dbo.Customer_Contacts.Company_ID = dbo.Customer_Company.Company_ID CROSS JOIN
dbo.Lead_Entry
Selecting Distinct Records Quickly
Mar 26, 2007Good day,
I have a table of approximately 10 million rows. The table has 3 field making up the key, namely:
ID, Date, Program
I need to extract all the distinct Program's from the table.
I have don so with:
Select distinct Program from table
This unfortunately takes roughly 2 minutes which is far to long. Is there something I can do to help speed this process up?
Thanks in advance.
Making Duplicate Records Distinct
Nov 25, 2014I have some duplicate records in my table, I need to make those records so they are not duplicate. There is a small dateTime field in that table, I want to add seconds to each row of that field in sequential order so that the record becomes distinct so for e.g if I have this in the field
22-04-2014 10:00:00
22-04-2014 10:00:00
22-04-2014 10:00:00
22-04-2014 10:00:00
So I want to make it like this so that all the duplicate rows becomes distinct
22-04-2014 10:00:01
22-04-2014 10:00:02
22-04-2014 10:00:03
22-04-2014 10:00:04
I am not sure if it is possible, but if it is then how can I achieve this. There are around 20 columns in this table or if there is any other way to make these record distinct.
Trying To Return Whole Records With Distinct Fields
Oct 17, 2006Hi
I am trying to write a query that will return a full record with a particular distinct field (the rest of the record being the first such record that includes the distinct field).
For example, for the following:
Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
Grape Y Green
Grapefruit N Yellow
I would want to return (assuming Colour was the distinct field):
Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
How do I do this? I've tried using a join (of all different kinds) with a subquery that uses SELECT DISTINCT but this doesn't seem to work. I've tried GROUP BY but none of the aggregate functions seem to just take the first found field.
Thanks for any help you can offer.
Selecting The Newest Records For Distinct Users
Oct 5, 2007I have the following Table:DataIDuserIDTimeStampI need to create a query that selects the "newest" records for all distinct users. I'm hoping to get this done in one query. I know I could write multiple queries to find all distinct users, and then the newest records based on that... and so on... but I'm not sure that's the most efficient. I have the following so far, but is it the most efficient (I have indexing on most of my columns)? SELECT DATA.DataID, DATA.UserID, UN.vchName, UN.vchImage, U.vchFirstName + ' ' + U.vchLastName AS 'FullName' FROM [tblData] DATA INNER JOIN [tblUnits] UN ON DATA.UserID = Un.UserID INNER JOIN [tblusers] U ON U.UserID = UN.UserID WHERE GPS.intGPSDataID IN (SELECT MAX([tbLData].DataID) FROM [tbLData] GROUP BY [tbLData].UserID)
View 4 Replies View RelatedHelp With Getting Distinct Records From Multiple Columns In SQL Query
Nov 3, 2005I'm exporting the following query to a datagrid, however in the result set, some values are duplicated (for various reasons... mostly old software and poor categorization)...On the records with identical values, I want to look at the account number and the DateOfService fields and search for joint distinct values and only display that...Current Example: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/10/2005 12:00:00 | 23489 | 10/15/2006 15:13:23 |Desired Result: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 23489 | 10/15/2006 15:13:23 |Here is the query I'm working with... just can't figure out how to join or limit the results to ONLY unique matches in Acct Number AND DateOfService. "SELECT tblCH.ProcedureKey AS CPT, tblPC.Description, DATEDIFF(d, tblPat.BirthDate, " & _ " { fn NOW() }) / 365 AS Age, tblPat.LastName, tblPat.FirstName, tblPat.BirthDate," & _ " CAST(tblCH.AccountKey AS varchar) + '.' + CAST(tblCH.DependentKey AS varchar) AS Account, tblCH.DateOfService " & _ " FROM dbo.Procedure_Code___Servcode_dat tblPC INNER JOIN " & _ " dbo.Charge_History___Prohist_dat tblCH ON tblPC.ProcedureKey = tblCH.ProcedureKey RIGHT OUTER JOIN " & _ " dbo.Patient_Info___Patfile_dat tblPat ON tblCH.AccountKey = (tblPat.AccountKey AND tblCH.DependentKey) = tblPat.DependentKey "Any suggestions from y'all SQL gurus? I have to have this report ready for production by tomorrow morning and this is the last fix I need to make =Thank you =)
View 6 Replies View RelatedSelecting Distinct Rows ?? From Over 10 Mill Records
Oct 27, 1998hi, I have a table that contains 11,169,000 rows that was downloaded from the main frame. There are alot of duplicate records in that table. I ran a query select * from tbl.... it still running and running and running ... it never stoped.... what seems to be the problem.... There are no primary keys or index in that table...
so my question , how would I deal with such table ... I want to run certain reports from that table and it seems that all my attempts failed? anyone can help
Select Distinct Records With Multiple Joins
Mar 13, 2008I have 3 table as below:
DSF
dsf_id
company_name
incorporation_date
secretary_name
ShareholdersDetails
shareholder_id
dsf_id
shareholder_name
DirectorsDetails
directors_id
dsf_id
directors_name
one company can have multiple shareholders and directors records.
i create a search query where users might search by company name, secretary name , shareholder name or directors name.
My select query is like below:
Code:
SELECT dsf.dsf_id, dsf.company_name,
dsf.incorporation_date, dsf.secretary_name,
s.shareholders_name, d.directors_name
FROM tbl_dsf dsf
LEFT OUTER JOIN tbl_directors d on dsf.dsf_id = d.dsf_id
LEFT OUTER JOIN tbl_shareholders s on dsf.dsf_id = s.dsf_id
[WHERE CONDITION]
The result for above query would be like:
Code:
abc | 1/2/1999 | william | marry | donna
abc | 1/2/1999 | william | jenna | donna
abc | 1/2/1999 | william | jolly | donna
abc | 1/2/1999 | william | marry | dolly
abc | 1/2/1999 | william | jenna | dolly
abc | 1/2/1999 | william | jolly | dolly
Is it possible to achive result as below:
Code:
abc | 1/2/1999 | william | marry,jenna,jolly | donna,dolly
Thanks
Transact SQL :: Get Only Those Distinct Records Which Satisfied Condition
Jun 3, 2015I need to write a query on a table..
table defination .. table Client_Master
file_id int identity(1,1) primary key
, client_id int
,LOAN_SANCTION_DATE datetime
,is_reject bit
has Data ..
FILE_ID CLIENT_ID
LOAN_SANCTION_DATE IS_REJECT
21139 22784
2014-02-03 00:00:00.000 0
21141 22784
2014-02-03 00:00:00.000 NULL
20869 22784
2014-02-03 00:00:00.000 1
20870 22784
2014-02-03 00:00:00.000 0
21571 22784
2014-02-03 00:00:00.000 1
66056 22784
2014-02-03 00:00:00.000 1
Above has 6 files entries for client id 22784 and LOAN_SANCTION_DATE 2014-02-03 out of which 3 are rejected ..
Now , i want to write a query to select those distinct client_id , LOAN_SANCTION_DATE from Client_Master where all files has been rejected ..
means by grouping client ID and LOAN_SANCTION_DATE all the files are rejected ..
I have wrote as below .. got the result but not satisfy with the query
SELECT DISTINCT CLIENT_ID,LOAN_SANCTION_DATE,COUNT(FILE_ID) AS No_Of_Files
,COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )AS No_Of_Rejected
FROM dbo.FILE_MASTER
GROUP BY CLIENT_ID ,LOAN_SANCTION_DATE
HAVING
COUNT(FILE_ID)=COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )
How To Use Distinct To Select Unique Records But Without Sorting ?
May 30, 2008
Hi
I need to select unique records from a Table. I'm using Distinct Keyword for this purpose. But the result set is showing distinct records in sorted order. I do'nt want to sort records. I need the order in which they are created in table.
Please suggest me the solution for this problem.
Thanks
Regards
Avinash Vyas
Transact SQL :: Exclude A Distinct Records From Select When One Condition Is True?
May 28, 2015I have customers named Alex (Cid=1), Bob (Cid=2), and Carrie (Cid=3) in a table customer.
Cid
First_Name
1
Alex
2
Bob
3
Carrie
I have products name Gin (Pid=1), Scotch (Pid=2) and Vodka (Pid=3) in a table products.
Pid
Product_Name
1
Gin
2
Scotch
3
Vodka
And I have a table that holds purchase called Customer_Purchases that contain the following records:
Cid
Pid
1
1
1
2
2
1
2
3
3
2
I would like to make a marketing list for all customers that purchased Gin or Scotch but exclude customers that purchased Vodka. The result I am looking for would return only 2 records: Cid’s 1 (Alex) and 3 (Carrie) but not 2 (because Bob bought Vodka).
I know how to make a SELECT DISTINCT statement but as soon as I include Pid=2 This clearly doesn’t work :
SELECT DISTINCT Pid, Cid
FROM
Customer_Purchases
WHERE (Cid = 1) OR
(Cid = 3) OR
(Cid <> 2)
Transact SQL :: Optimize Count Distinct For Multiple Groups Of Records?
May 21, 2015I have a CTE returning a recordset which contains a column SRC. SRC is a number which I use later to get counts and sums for the records in a distinct list.
declare@startdate date = '2014-04-01'
declare@enddate date = '2014-05-01'
; with SM as
(
SELECT --ROW_NUMBER() OVER (PARTITION BY u.SRC ORDER BY u.SRC) As Row,
u.SRC,
[Code] ....
-- If Referral start date is between our requested dates
ref.Referral_Start_Date between @startdate and @enddate
OR
-- Include referrals which started before our requested date, but are still active during our date range.
(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL ))
)
INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id
AND s.Service_Delivery_Unit_Id = 200
)
SELECT
count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,
[code]....
How To Automatically Create New Records In A Foreign Table When Inserting Records In A Primary Table.
Sep 13, 2006Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View 4 Replies View RelatedSQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1
Aug 11, 2015Table1 contains fields Groupid, UserName,Category, Dimension
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
Transact SQL :: Retrieve All Records From Parent Table And Any Records From Child Table
Oct 21, 2015I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
Inserting Distinct Data From One Table In Another Table, How?!?Really Urgent And Needing Help!!!
May 24, 2007Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
View 1 Replies View RelatedHow Do I Select All Records In One Table That Have NO Related Records In Another Table?
Jul 20, 2005I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks
View 2 Replies View Related