Trying To Return Whole Records With Distinct Fields

Oct 17, 2006

Hi

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.

View 11 Replies


ADVERTISEMENT

Select Distinct One Some Fields, But Return All Feilds

Jul 23, 2005

I was curious...Is there a way to select distinct on a combination of some fields andthe for each record returned also get the other fields of anarbitrarily chosen record matching the fields in the distinct record.For example, if I have a select distinct on say three fields:SELECT DISTINCT Code1, Code2, Code3but the table also has other fields, maybe Foo1 and Foo2, and I wantFoo1 and Foo2 to also be displayed. Since there may be multiplerecords that match a particular Code1, Code2, Code3, then I just wantone of those to be arbitrarily chosen.

View 3 Replies View Related

Return Non-matching Records On Two Fields

Jan 18, 2008



Here is a very basic question that I have.

I have two tables, A and B. Both have a customernumber and a batchid. This combination is unique in both tables.

How can I pull back the records from table A that do not have a corresponding combination in B?

I know I could find the ones that do match and then exclude them using an inner join and subquery, but is there a simpler way?


THANKS!

View 1 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

Use DISTINCT On Subset Of All Fields

Jul 5, 2001

My table contains customer records with multiple records per customer. As a result of a query, I´m only interested in one record per customer with the highest value of a certain field in the record.

I thought of using DISTINCT, but can I use DISTINCT on a subset of all fields? Or sort the table in a certain way that the query result only shows the first unique records for a customer.

Other ideas are welcome to.

View 4 Replies View Related

Group By Or Distinct - But Several Fields

Feb 11, 2014

How can I use a Distinct or Group by statement on 1 field when calling All or at least several ones.

Example:
SELECT id_product, description_fr, DiffMAtrice, id_mark, id_type, NbDiffMatrice, nom_fr, nouveaute
From C_Product_Tempo

And I want Distinct or Group By nom_fr

View 19 Replies View Related

Select All Fields But Using Method Of Distinct

Jul 28, 2015

I have a code that gives me the sample I am looking for, but only displays one field.

NuminHH is my field

7320EN_Var is my Table

Proof is the new table I want to create.

SELECT DISTINCT [NuminHH] INTO Proof
from 7320EN_Var

View 21 Replies View Related

Return 7 Col But DISTINCT On 3 Col From 2 Tables

Jul 13, 2006

Hello !
for MS SQL 2000


SELECT tableA.idA, tableB.idB,tableB.ColX,tableA.ColA,
tableA.ColB, tableA.ColC
FROM tableB RIGHT OUTER JOIN tableA ON tableB.idB = tableA.idA


I want to do a DISTINCT on

tableB.ColX, tableA.ColA, tableA.ColB
to return only the rows tableB.ColX, tableA.ColA, tableA.ColB which are differents

thank you for helping

View 14 Replies View Related

Return DISTINCT Values

Aug 22, 2007

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below??



Code Snippet
SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType
FROM @GResults r
LEFT OUTER JOIN
ReconComments cm
ON cm.GPositionID = r.GPositionID
WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)
ORDER BY GCustodian, GCustodianAccount, GAssetType;




Thanks.

View 11 Replies View Related

To Return Distinct Column Using INNER JOIN

Apr 23, 2008

Hello all,

I am using INNER JOIN to connect 2 tables together but I wish it to return distinct columns instead of repeating itself !

eg.
Current output would be:
UserID Name UserID OrderID
1 John 1 5
2 Bob 2 6

I want it to be:
UserID Name OrderID
1 John 5
2 Bob 6


I need to use SELECT * as there are many many columns and wish to save time :)


Cheers,

James

View 3 Replies View Related

Return Rows That Arent Distinct

May 5, 2008

I am trying to create a query that will find all the records that have the same value multiple times in the a column called phonenumber.

How do i return disticnt records having count greater than 1

View 5 Replies View Related

Return Distinct Values From Stored Procedures

Aug 17, 2006

I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!

View 3 Replies View Related

SELECT DISTINCT To Return Only The YEARS In A Date Field?

Mar 22, 2006

I have a table in my MS SQL 2000 database called News which has a field caled NewsDate. This is a standard Date field which stores the info in this format: 3/1/2001.

I want to create a query that returns one row for each year that there is a story.


For example, if I had this data...
3/1/2001, 6/27/2003. 9/17/2003, 1/1/2006, 4/5/2006

the query would return this result:

2001
2003
2006


This is the query I've started with:


SELECT DISTINCT NewsDate FROM News ORDER BY NewsDate DESC


What modifier can I apply to the NewsDate field to extract JUST the year from the table? If this were ASP I would try something like Year(Date), but, of course, I can't do that here.

Is this even possible? I've been looking up date functions, but haven't found anything that will work in a select statement. ANY and ALL advice will be greatly appreciated.

View 1 Replies View Related

Trying To Get Distinct Records

Jan 7, 2008

How 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! 
 
 
 

View 18 Replies View Related

Distinct Records

May 11, 2008

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

View 7 Replies View Related

Only Get The Non Distinct Records

Oct 1, 2007

I 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?

View 15 Replies View Related

Getting Distinct Records

Oct 16, 2007

how 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

View 4 Replies View Related

Get Top Distinct Records

Jan 31, 2008

hi 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

View 2 Replies View Related

Selecting Distinct Records

Sep 1, 2007

Hi,
 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 

View 9 Replies View Related

Select Distinct Records

Oct 10, 2007

 Hello, 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 Related

Display Distinct Records?

Dec 7, 2011

TableName: 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'

View 3 Replies View Related

How To Get Distinct Records In Table

Dec 26, 2013

For examble:

In a table name like

1.Test
2.Test.
3.Test..
4.Test...

How will get the name "Test" using distinct keyword?

View 1 Replies View Related

Count Distinct Records

May 15, 2008

how can i count in sql the number or records taht would be returned if i did

select distinct site,date from allrecords

View 1 Replies View Related

How To Get Distinct Records In Table

Dec 25, 2013

For examble:

In a table name like

1.Test
2.Test.
3.Test..
4.Test...

How will get the name "Test" using distinct keyword?

View 2 Replies View Related

DISTINCT And Number Of Records

Mar 19, 2008

Wondering 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

View 1 Replies View Related

Selecting Distinct Records Through CTE

Jan 11, 2013

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

View 9 Replies View Related

Return All Fields Of Database

Feb 16, 2007

Hello,

Is it possible to return all the field names of a database. I do not want the data rows. Just a list of fields in the databse.

Thanks

View 6 Replies View Related

How To Select Distinct Records From 3 Or More Tables...?

Jun 1, 2001

Attached 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

View 4 Replies View Related

Selecting Distinct Records Quickly

Mar 26, 2007

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

View 14 Replies View Related

T-SQL (SS2K8) :: How To Get Distinct Records From Table

Apr 14, 2014

Suppose 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

View 3 Replies View Related

Making Duplicate Records Distinct

Nov 25, 2014

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

View 1 Replies View Related

Expression Divide By 0 - How To Return N/A For Fields

Jun 3, 2015

I am new to SQL. I added a calculated field to my dataset with and expression to calculate efficiency.

My expression is as follows: =Fields!EstTotLbrHrs.Value/Fields!ActTotLbrHrs.Value

In some cases my value for ActTotLbrHrs is 0, so it returns #Error

How do I return "N/A" for fields with ActTotLbrHrs = 0

View 1 Replies View Related

Merging Queries To Return Different Fields In Same

Oct 25, 2005

hi all,

can anybody help in combining all the mentioned queries into a single query so as to display all fields in a row.

1.number of imported imported animal type:

select count(*) as import_lic_no from appln_clip ac,consign_animal c,pet p

where ac.ac_id=c.ac_id and p.pet_no=ac.clip_id and ac.appln_id like 'A8%'

2. number of imported animal type that are licensed:

select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id


3.percentage: 2/1*100

select percentage=

((select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id)
(select count(*) as lic_imp_ani_type from pet p,clip c where p.pet_no=c.clip_id))*100

4.local animal type:total pet records - 1

select number=

(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id)


5.local animal type that are licenced:total pet records-2

select number1=
(select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)


6.percentage: 5/4*100

select percentage=((select count(*) from pet p) - (select count(*) from pet p,clip c where p.pet_no=c.clip_id)/
(select count(*) from pet p) - (select count(*) from appln_clip ac,consign_animal c where ac.ac_id=c.ac_id))*100

thx,
vani

View 1 Replies View Related







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