Help Please Retrieving Unique Records.

Apr 15, 2008

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)

If there are records like

ID Amount Name
1 0.03 Name1
2 0.07 Name9
3 0.05 Name3
4 0.03 Name8
5 0.07 Name4
6 0.06 Name7


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

Appreciate any help here.

View 2 Replies


ADVERTISEMENT

Retrieving Unique Rows

Jun 3, 2008

I have the following sql:

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

Please can you help.

View 2 Replies View Related

Retrieving Unique Fields From Table Into Existing Dataset

Jun 18, 2008

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)

View 4 Replies View Related

Retrieving Selected Records

May 29, 2001

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

View 1 Replies View Related

Retrieving The First N Records From A SQL Query In VS 2005

Apr 6, 2006

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!

View 3 Replies View Related

How To Create Sql For Retrieving 10 Records Per Time In .NET

Aug 4, 2004

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.

Thanks

View 6 Replies View Related

Retrieving Records And Inserting Into Table

Mar 19, 2008

Folks:

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)


===================================================================================

Same Query when trying to insert the records in a temp table it takes hours.

===================================================================================

SET NOCOUNT ON

DECLARE @ImportId INT
SET @ImportId = 5151

DECLARE @ResultXML XML
SET @ResultXML = (SELECT ResultXML FROM tbRequests WITH(NOLOCK) WHERE ImportId = @ImportId)

create table #TResults
([ID] [INT] IDENTITY(1,1) NOT NULL,
DealName VARCHAR(200),
CUSIP VARCHAR(100),
Vintage INT,
PoolType VARCHAR(100),
PaidOff BIT)


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)

SELECT * FROM #TResults


============================================


Thanks !

View 7 Replies View Related

Retrieving Selected Join Records

Nov 22, 2006

Hi,
I have the folowing 3 (SS2005) tables:


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.


Any help appreciated.


Thanks, KoG

View 4 Replies View Related

Retrieving Records Within An Index Range, The Nth Record?

Mar 5, 2007

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.

View 14 Replies View Related

DB Engine :: Table Partition - Retrieving Records

Jul 2, 2015

I need to partitioning the table on which most expensive query run.

Every day 500000 lac records inserted/update in that table

How to create what impact on performance while retrieving the records.

View 5 Replies View Related

SQL Question ( Retrieving Records Based On A Priority Order )

Feb 24, 2008

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!

View 1 Replies View Related

Unique Records

May 6, 2004

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.

View 12 Replies View Related

UNIQUE Records

May 12, 2008

I have the following sql:

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

Thanx, Robson

View 2 Replies View Related

Selecting Unique Records

Sep 17, 2007

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.

View 4 Replies View Related

Inserting Unique Records

Apr 24, 2007

Hello,

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.

Thank you for your help!

CSDunn

View 3 Replies View Related

Latest Unique Records, How To Get?

Dec 3, 2007

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?

Thanks! :D

View 7 Replies View Related

Selecting Unique Records

Mar 21, 2014

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,

[code]...

View 3 Replies View Related

How To Get Unique Records When There Are More Groups

Oct 7, 2005

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."

View 1 Replies View Related

Combining Unique Records Into One

Mar 5, 2008

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 |

Any help would be greatly appreciated.

View 2 Replies View Related

Append Only Unique Records In SQL Table

Nov 23, 2007

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

View 2 Replies View Related

Help With A Join That Needs To Return Unique Records

Apr 28, 2008

Hi All,

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

thanks in advance

View 11 Replies View Related

Check For Duplicate Records No Unique ID

Mar 17, 2014

Using SSE 2012 64-bit...How can I check all fields for duplicate records?I tried OVER PARTITION..But that is returning an error message

Code:
USE db
SELECT ROW_NUMBER() OVER (PARTITION BY all fields)
ORDER BY ID --Not unique) AS RowNumber

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

View 4 Replies View Related

Query To Retrieve Unique Records

Jun 7, 2008

I am new to SQL, still learning.

If I want to retreive unique records for e.g. VendorName,City and State however no 2 vendors must be of the same city or state.

How can I do this?

I have used Select and distinct but that still brings unique vendornames in the same state and city. I only need one vendor per city and State.

Help!

View 4 Replies View Related

Pulling Unique Records With Max Dates

Mar 5, 2006

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...

Thanks Much in advance

View 4 Replies View Related

Design For Reporting Unique Records

Apr 12, 2007

Hello,

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!

Thanks!
Daryl

View 6 Replies View Related

How To Determine The Unique IDs Of Duplicated Records

Feb 21, 2006

> 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)

View 1 Replies View Related

How To Combine 2 Records Into 1 Unique Record

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

MSSQL Query To List Out Unique Records

May 22, 2008

 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..

View 5 Replies View Related

How To Return An Unique From Two Table With Duplicated Records?

Sep 27, 2005

Hello, everyone:

I have two tables with some duplicated records like,

ZZZTest:
C_IDC1C2C3
10AAA
20BBB
30AAA
40BBB


ZZZTestTable:
D_IDC11C22C33
1AAA
2AAA
3BBB
4BBB
5AAA
6AAA
7BBB


I wand to get the unique records by SELECT / JOIN statement. Now I used a query,

SELECT * FROM ZZZTest t
INNER JOIN ZZZTestTable tt
ON t.Col1=tt.Col11 AND t.Col2=tt.Col22 AND t.Col3 = tt.Col33

and got the records like,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA1AAA
10AAA2AAA
30AAA2AAA
20BBB3BBB
40BBB3BBB
20BBB4BBB
40BBB4BBB
10AAA5AAA
30AAA5AAA
10AAA6AAA
30AAA6AAA
20BBB7BBB
40BBB7BBB



What I am expecting is,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA2AAA
20BBB3BBB
40BBB4BBB

Any suggestion will be great appreciated.

Thanks

ZYT

View 3 Replies View Related

Query On Unique Records With Multiple Criteria

Oct 19, 2013

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

SELECT DISTINCT PTFF.TRANSCODE,PTFF.ID,PTFF.TRANS_ID,PTFF.TRANSDATE,PTFF.EFFDATE, CHFF.STATUS,CHFF.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

Samples of the result:-

IDSTATUSTRANS_IDPAYCHNLTRANSDATEEFFDATETRANSCODE
51881712CC13082920130920T522
9361164CC13081620140813T522
78531153CC13082020130814T522
8949151CC13081220130801T522
8949251B13081220130801T522
19081455CC13051620131129T522
19082455A13051620131129T522
19081409CC11101920111129T522
19082409A11101920111129T522
19081404CC11092920111129T522
19082404B11092920111129T522

View 7 Replies View Related

Unique Email Address Records Are Required

May 10, 2007

ms sql server

data:
idno Email Category
1 a@a.com c
2 b@b.com c
3 b@b.com c
4 b@b.com c

only unique email address record are requierd.
1 a@a.com c
2 b@b.com c
what could be the query for such requirements.

View 5 Replies View Related

Transact SQL :: Assign Unique Number To Records?

Nov 15, 2015

Lets say I have a table - tblProducts

View 4 Replies View Related

Bug? Report Builder Returning Only Unique Records!

Feb 8, 2007

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.

Thanks,
Taurkon

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved