I'm trying to read from a table and return only the unique records from a table.
The table has 3 columns,
ID - Autoinc
Amount - decimal 6,2
Name - varchar(10)
I am wanting to retreive only records 3 & 6 (values 0.05
0.06) as they are the only ones where amount is unique i.e. only 1 entry in the table.
I put the following sql statement together which achieves this but it it only returns the amount value
select Count(amount),amount from bids group by amount having count(amount)<2 order by amount
So I then expanded it to read
select Count(amount),amount,name from bids group by amount,name having count(amount)<2 order by amount
not sure where i'm going wrong but the results returned were
0.03
0.03
0.05
0.06
0.07
0.07
SELECT DISTINCT patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date FROM patient LEFT JOIN patient_record ON patient_record.patientID = patient.patientID WHERE (sub_categoryID = 4 OR patient_record.allocated = 4) AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5) GROUP BY patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date
This brings up duplicate records, my aim is to bring distinct records, now if I take out the other returned fields after patientID and using the following sql:
SELECT DISTINCT patient.patientID FROM patient LEFT JOIN patient_record ON patient_record.patientID = patient.patientID WHERE (sub_categoryID = 4 OR patient_record.allocated = 4) AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5) GROUP BY patient.patientID
This bring up distinct results, but I need to retrieve the other fields from the database i.e. patientFirstName and patientLastName
I am working on creating a report which is retrieving data from a SQL 2005 database and being displayed in a C#.net web page. Presently, I am binding the dataset to a ASP:Gridview on the web page. I am currently retrieving most of the needed fields using the following SQL statement:PROCEDURE [dbo].[pr_getReportTickets] @DateCreated nvarchar(15) ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure hereSELECT tbl_Queue.num_TicketNumber AS TicketID , tbl_Users.str_Name AS Technician , tbl_Queue.str_QueueLocator AS TechNTID , tbl_Queue.dat_ReceivedRequest AS dateCreated , tbl_Queue.dat_DueDate AS DueDate , tbl_Queue.str_TaskName AS TicketTitle , tbl_Queue.str_Requestor AS RequestorNTID --, I need most current dat_TimeStamp from tbl_Notes here for this record --, I need most current int_PercentComplete from tbl_Notes here for this record --, I need to sum up all of int_MinutesWorked fields from tbl_Notes here for this record , MasterEmp.dbo.fn_FormatFullName(tbl_employee.str_fname, tbl_employee.str_lname) AS RequestorName , tbl_Queue.str_TicketType AS TicketType , tbl_Status.str_TaskStatus AS TicketStatus , tbl_Severity.str_Priority , tbl_Complexity.str_Complexity , tbl_Severity.str_Priority + N' / ' + tbl_Complexity.str_Complexity AS Priority , tbl_Queue.dat_CompleteDate as DateCompleted FROM dbo.tbl_Queue LEFT OUTER JOIN MasterEmp.dbo.tbl_employee AS tbl_employee ON dbo.tbl_Queue.str_Requestor = tbl_employee.str_ntid LEFT OUTER JOIN tbl_Users ON tbl_Queue.str_QueueLocator = tbl_Users.str_ntid LEFT OUTER JOIN tbl_Status ON tbl_Queue.num_Status_CD = tbl_Status.num_Status_CD LEFT OUTER JOIN tbl_Severity ON tbl_Queue.str_Severity = tbl_Severity.str_Severity LEFT OUTER JOIN tbl_Complexity ON tbl_Queue.int_ComplexID = tbl_Complexity.int_ComplexID WHERE (dbo.tbl_Queue.dat_ReceivedRequest > CONVERT(DATETIME, @DateCreated, 102)) ORDER BY TicketType, tbl_Queue.str_Severity I also have another table called tbl_Notes. This table contains an unlimited quantity of records for every “num_TicketNumber�. This table contains the following fields: num_TicketNumber, str_TechRep, str_Notes, dat_TimeStamp, int_PercentComplete and int_MinutesWorked I need to add two more fields to the query, but I do not know how to tell the SQL statement how to retrieve the data for the specfic record and inbed the fields into the Dataset being returned from the database to the web page.1) The first field I need is the most current recorded Date field from the tbl_Notes table for the each of the records returned in the above SQL statement. Similar to: SELECT TOP (1) dat_TimeStamp FROM tbl_Notes WHERE(num_TicketNumber = xxxx) ORDER BY dat_TimeStamp DESC2) The other field I need to return with the Dataset is the sum of the int_MinutesWorked for each of the tickets being retrieved. Similar to: SELECT SUM(int_MinutesWorked) AS TotalMinutesFROM tbl_Notes WHERE (num_TicketNumber = 49)
Hi every body, I am making a program which is currently dealing with thousands of records. What I want is to have a button in the front END which make me able to fetch only first 100 record.If the desired record doesn't come with in first 100 record I press this button another time to fetch next 100 records i.e. from 101 to 199. How can it be possible in SQL Server stored procedure.
Eagerly waiting from all of you GENIUS people. Kailash
Hi, first off, I'm a TOTAL novice at this stuff, I'm just currently blundering my way through a complex site to learn stuff. I'm trying to call the newest addition to a SQL database into a webpage, in this case, it'll be 'newest user', one result only. I've done several other data retrival sections using a datatable, but the guy who was helping me though it is unavailable at the moment and I get the feeling I've jumped into the deepend slightly. Could anyone give me an example of how retrieving the First N Records from SQL should look in VS? Does it need to be in a data table or can it go in a label? Sorry if this is somewhat vague, but as I said, I've really only been using VS for a week!
I want to create sql for retrieving 10 records per time but need to know the total result matches. And I have previous and next button for retrieving previous or next 10 records.
I need help with this. When I run the below script (only select) it retrives around 130K records and gives me the output within 2 mins. Whenever I try to put the same output in a temp or permanent table it takes hours. Any Idea why?
SET NOCOUNT ON
DECLARE @ImportId INT SET @ImportId = 5151
DECLARE @ResultXML XML SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)
SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName, resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP, CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage, resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType, CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode) CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
INSERT into #TResults (DealName,CUSIP,Vintage,PoolType,PaidOff) SELECT resultNode.value('(./DealName)[1]','VARCHAR(200)') AS DealName, resultNode.value('(./CUSIP)[1]','VARCHAR(100)') AS CUSIP, CASE WHEN resultNode.value('(./Vintage)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./Vintage)[1]','INT') END AS Vintage, resultNode.value('(./PoolPoolType)[1]','VARCHAR(100)') AS PoolType, CASE WHEN resultNode.value('(./PaidOff)[1]','VARCHAR(100)') = '' THEN NULL ELSE resultNode.value('(./PaidOff)[1]','BIT') END AS PaidOff FROM @ResultXml.nodes('./WebService1010DataOutput') resultXml(resultXmlNode) CROSS APPLY resultXmlNode.nodes('./Results/Result') resultNodes(resultNode)
CREATE TABLE [dbo].[tblSubscription]( [SubscriptionID] [int] IDENTITY(1000000,1) NOT NULL, [SubscriberID] [int] NOT NULL, [Status] [int] NOT NULL, [JournalID] [int] NOT NULL,
CREATE TABLE [dbo].[tblTransaction]( [TransactionID] [bigint] IDENTITY(100000000,1) NOT NULL, [TransactionTypeID] [int] NOT NULL, [SubscriptionID] [int] NOT NULL, [Created] [datetime] NOT NULL,
CREATE TABLE [dbo].[tblMailing]( [MialingID] [bigint] IDENTITY(1000000000,1) NOT NULL, [SubscriptionID] [int] NOT NULL, [MailTypeID] [int] NOT NULL, [MailDate] [datetime] NOT NULL
So for each subscription there can be 1 or more transactions and 0 or more mailings, and the mailings are not necassarily related to the transactions. What I am having difficulty doing is this:
I wish to select tblMailing.MailingID, tblMailing.MailDate, tblMailing.SubscriptionID (or tblSubscription.SubscriptionID), tblSubscription.SubscriberID, tblSubscription.Status, tblTransaction.TransactionID, tblTransaction.Created, but I only wish to retrieve rows from the transaction table where tblTransaction.Created is the latest dated transaction for that subscription. I.E. (maybe this makes more sense..:) I wish to select all rows from tblMailing along with each mailing's relevent subscription details, including details of the LATEST TRANSACTION for each of those subscriptions.
I am currently working along the lines of MAX(tblTransaction.Created) and possibly GROUP BY in a subquery, but cannot quite figure out the logic.
if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?Possible? How can I do it?When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?I'm using SQL 2005 Express, not SQL 2000.
Hi all,I have a table with this structure:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/4/2007 | M1 | 1/2/2008 | R2 | 1//5/2007 | M2 | 1/6/2007 | RFor each customer ID, I need to retrive only the records with the mostrecent transaction date, and I did:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/2/2008 | R2 | 1/6/2007 | RThe problem is, each customer can make more than one transaction inthe same day (see above). In these cases, I need to retrieve only onerecord per customer, based on the importance of the transaction: F ismore "important" than R which is more "important" than M. So, in theexample above, I would only retrieve transaction F for customer 1 anddisregard transaction R: they were made on the same date, but I ammore interested in F than in R.Do you have any suggestions on how I could achieve this in SQL?I use Microsoft SQL server 2005.Thank you for your help!
Is there a way to get a Stored Procedure to return only unique records from a table? I am using a Stored PRocedure to query a table and it returns all the records in the table and there are many duplicates in the information. I am using SQL Server 2K.
SELECT COUNT(patient.patientID) AS total_patients FROM patient LEFT JOIN patient_record ON patient_record.patientID = patient.patientID WHERE sub_categoryID = 4 OR patient_record.allocated = 4
from the database this gives me a COUNT of 22, it should only be 10. I am doing a join and it gives the total records in the two tables where I only want the total in the left table(patient). How can I GROUP BY patient.patientID in a COUNT query
Hello Everyone and thanks for your help in advance. I have a SQL Server Table wtih approximately 100,000 records. I need to determine if there are duplicate records in this table. My probelm is that there is a unique ID column that was added for each row, so I'm not exactly sure how to filter the rows. Any help on this would be greatly appreciated. Thanks.
I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.
How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?
Does my request make sense? Please let me know if you have questions.
Let's say I have a data entry from a pool of employees: table is as follow: EmpNo Branch Date Amount 1 A101 11/30/2007 $0.90 1 A101 11/30/2007 $1.20 2 A101 11/30/2007 $0.90 3 A101 11/30/2007 $0.80
How can I select the whole table and only take in 1 unique latest entry if there are multiple entries for the same day, same branch under same employee number?
I am trying to create a select query similar to the following but the problem I am having is that I want to only select one record where there may be several with the same dw_order_no. I have tried various ways using SQL developer but without success
SELECT VE_EZP_ORDER_TRANS.EZP_BILL_STATUS AS EZP_BILL_STATUS1, VE_EZP_AGED_CUSTOMER_DEBT.SURNAME, VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE_CATEGORY, VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE, VE_ORDERLINE.DW_ORDER_NO,
Steve writes "I need a query to show all the medicare patients only. I want only one kind of insurance for each patient. I am getting records where each patient has more than one insurance like medicare and something else. Each patient has one,two, three or more insurances like medicare,medical,blue cross,tricare etc.
The result should look like this: Sam pick Medicare John white Medicare Ann Richmond Medicare
My query retrieves patients with more than one insurance.
select patient_first_name f_name,patient_last_name l_name,patient_zip zip, count(distinct payer_id) payer_id from claims where payer_id ='abcdefhj' group by patient_first_name, patient_last_name,patient_zip having count(distinct payer_id)= 1 order by patient_zip asc;
please help me. Greatly appreciate your response."
Could some one please help me with the following query.
I have multiple tables link together base on wo_No and prt_Mark fields and I get the following results. wo_No | prt_Mark | dwg_Seq | 324037 | d400 | 1S2 | 324037 | d400 | A1 | 324037 | d400 | 1 | 219001 | 56 | 2B | 219001 | 56 | 2C | 219001 | 56 | 2C |
What I would like is to combine the unique dwg_Seq where they have the same wo_No and prt_Mark. 324037 | d400 | 1S2,A1,1 | 219001 | 56 | 2B,2C |
I would also be happy with the following results get the Max unique dwg_Seq and count how many more unique dwg_Seq there are. 324037 | d400 | A1 + 2 | 219001 | 56 | 2B + 1 |
I have a stored procedure that appends data from a temp table to a destination table. The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates. The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table? Thanks in advance.SMc
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
I am trying to pull only those records with a maximum upload date for a file upload log.
This table keeps a list of files uploaded by supplierID and will have multiple records from the same supplier but with all different upload dates. Overall there are over 1,000 records for 48 uniqur suppliers.
The field names are: SupplierID, UploadDate, FeedFileName, RecordCount, FeedFileDate, RecordCount, and FeedLoaded.
So for each distinct SupplierID I'd like to grab the line item based on the max feedfiledate for each one - in other words I am trying to get the latest uploaded file information, how many records were in the file, and when it was uploaded... I've tried multiple group by and max clauses but there is something I am missing...
I'm hoping someone can point me in the right path with a design issue I'm running into. I'm somewhat new with database design and SQL server, so bear with me.
I'm creating a sort of project management system, and I've got a main PROJECT table, and then a STUDENT_PROJECT_PARTICIPANT table, with foreign keys to STUDENT and PROJECT tables. Each has an 'institute' field. What I want to do is to be able to report on the number of students involved in each project, the number of unique students involved in project with a particular institute, and then the number of unique students involved in all projects. I'd also like to be able to report the number of unique students involved in a custom-picked list of projects (this seems the least feasible). The trick is that I need to report this statics within a specific date range. My original idea was to just save statistics of the numbers of students involved in a project every time it is saved, but from there I'm not sure how I can find out the unique count for all projects, since I don't have the date that each student was saved. That's my basic problem. Let me know if you need any clarification.
If anyone has done something similar or has any advice for me on this, please let me know!
> This is a common problem with some solution[color=blue]>>[/color]/************************************************** *********************************** Problem:* Determine the Duplicated Records in a table using single SELECT.** We shall be using Northwind database, add some duplicate records.** Here we want to know if 2 columns (CompanyName,* PHone) are duplicated in a table.*** ShipperID CompanyName Phone* ----------- ------------------------- ------------------* 1 Speedy Express (503) 555-9831* 2 United Package (503) 555-3199* 3 Federal Shipping (503) 555-9931* 4 Federal Shipping (503) 555-9931* 5 Speedy Express (503) 555-9831* 6 Federal Shipping (503) 555-9931***************************************************** **/================================================== SOLUTION 1: Gives me the IDs that are duplicated.================================================== SELECTShipperID, CompanyName, PhoneFROMSHIPPERSWHEREEXISTS (SELECTNULLFROMSHIPPERS bWHEREb.CompanyName = SHIPPERS.CompanyNameAND b.Phone = SHIPPERS.PhoneGROUP BYb.CompanyName, b.PhoneHAVINGSHIPPERS.ShipperID < MAX( b.ShipperID ))/* ********************* Output results********************/ShipperID CompanyName Phone----------- ----------------------------------------------------------------1 Speedy Express (503) 555-98313 Federal Shipping (503) 555-99314 Federal Shipping (503) 555-9931(3 row(s) affected)================================================== ===========SOLUTION 2: Gives me the data which are duplicate butnot the IDs================================================== ===========SELECTCompanyName, PhoneFROMSHIPPERSGROUP BYCompanyName, PhoneHAVINGCOUNT(*) > 1/* ********************* Output results********************/CompanyName Phone---------------------------------------- ------------------------Speedy Express (503) 555-9831Federal Shipping (503) 555-9931(2 row(s) affected)
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.
hilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required.
SCENARIO :
I am required to query from 2 tables for those unique record that meets both conditions below:- 1. Status is 1 @ max (trans_id), paychnl = CC 2. Status is 2 @ max (trans_id), paychnl = A or B
FYR, 2 tables and respective columns to query are as below:- table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE table CHFF --> col STATUS,PAYCHNL
FYI, status refers to the paychnl method status:- ==> 1 means the current paychnl method ==> 2 means the previous paychnl method
paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE.
Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID.
Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE table CHFF --> col STATUS,PAYCHNL
FROM DBO.PTFF PTFF JOIN DBO.CHFF CHFF ON CHFF.ID = PTFF.ID WHERE PTFF.TRANSDATE BETWEEN 130501 AND 130831 AND PTFF.TRANSCODE='T522' AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B')))
However, the script above returns :-
1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2, 2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B 3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID
Our users use Report Builder for writing ad-hoc queries. They have run into a problem where Report Builder is returning only distinct records is the entities unique identifier is not added to the report.
Example:
TransID Type TransQuantity
1 CS 4 2 CS 4 3 CP 2 4 CN 3
Adding the above fields to the report builder canvas will return 4 rows. However, if I were to add only Type and TransQuantity to the canvas as such, Report Builder will only return 3 rows:
Type TransQuantity
CS 4 CP 2 CN 3
Is there any way to have Report Builder return the actual number of records? Our users often will export the results from Report Builder into Excel to slice and dice the data, and with the functionality the Report Builder has right now, incorrect values will be certain.
