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;
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.
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!
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.
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.
t1 has a 1 to many relationship to t2 (no keys on t2)
SELECT DISTINCT t1.name, t1.title FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id
That works fine... Now, I want to include a third table... Table3: regionID , RegionName, location,...
SELECT DISTINCT t1.name, t1.title, t3.RegionName FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id INNER JOIN table3 t3 ON t2.regionID = t3.regionID
A sample result would be....
Bill, Owner, England Jim, Owner, France Jim, Owner, Spain
However, I would like to only list the individuals (ie. Jim, Owner) one time! Only the first 2 fields (name, title) need to be distinct. How do I reference the third table without including it in the DISTINCT keyword?
// C - Categories, P - Parameters, CP_Link - linkage table between C and P
// Basically, it is a list of features belonging to a particular category.
DROP TABLE [C]; CREATE TABLE [C] ( ID_CAutoinc, Name varchar(32), PRIMARY KEY( ID_C )
[code]....
OK, this works fine, but I want to get a list of parameters (from P table) which holds only those items that linked to all categories requested. In other words, I want only 'leg' and 'hand', but not the 'beard', because 'girls' not linked to 'beard'.Only common features for all of categories listed "IN (,,,)" wanted.
query1="select co.*, ct.*, from contacts co, enquiries en, contactPersonType ct where co.co_contactId=en.en_contactId and co.co_contactType=ct.ct_contactTypeId"
i want to display only the contact persons details who made enquires. this gives me duplicate records. im not displaying enquiry details. how can i use distinct with this?
I am attempting to run the following select statement joining multiple tables but in the end result I would like only Distinct/Unique values to be returned in the invlod.lodnum column.
[select pw.schbat, adrmst.adrnam, adrmst.adrln1, adrmst.adrcty, adrmst.adrstc, adrmst.adrpsz, invlod.lodnum, shipment.host_ext_id, shipment_line.ordnum, car_move.car_move_id from aremst join locmst on (aremst.arecod = locmst.arecod) and (aremst.wh_id = locmst.wh_id)
I need info from 2 Tables. from the Table 2 I just need 1 column. When i ask for this column the output I get is data repeating themselve many times.
Distinct, should give me unique data, but is doesnt.... the code:
SELECT DISTINCT FSenddate, FSupplyIDName, FSupplyerNumber,FBillNo,FSourceBillNo,FItemName,FItemModel, FAuxQty,FAuxTaxPrice,FHeadSelfP0237 FROM vwICBill_26 WHERE FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
This code just works in Table1 (vwICBill_26)
but with table 2 (vwICBill_1)
SELECT DISTINCT vwICBill_26.FSenddate,vwICBill_26.FSupplyIDName, vwICBill_26.FSupplyerNumber,vwICBill_26.FBillNo, vwICBill_26.FSourceBillNo,vwICBill_26.FItemName, vwICBill_26.FItemModel,vwICBill_26.FAuxQty, vwICBill_26.FAuxTaxPrice,vwICBill_26.FHeadSelfP0237, vwICBill_1.FDate,vwICBill_1.FContractBillNo FROM vwICBill_26,vwICBill_1 WHERE vwICBill_26.FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE() AND vwICBill_1.FContractBillNo=vwICBill_26.FSourceBillNo
The last sentence is the problem I want that it shows me the data that is not equal. As soon as I implement the not equal it shows me the massive repeating data. I mean even without the last sentence I get this data output.
All together, I want a clear database output without data repeating. Any ideas how it may work without DISTINCT?
I think this problem is a typical amateure problem, but I would apreciate help!
I need to get all distinct values from 10 different tables that exist in the field [favoritesport] And each table holds close to 50K records so I am looking at 500,000 records to get distinct values for. Would the fastest, less intrusive way of achieving this be to just create a UNION ALL so run
Select Distinct([favoritesport]) from table1 Union Select Distinct([favoritesport]) from table2 Union Select Distinct([favoritesport]) from table3 etc etc etc
12.) Now you have two different tables - each with two columns.Table #1Single Column2 rows with a value equal to 1 and 2Table #2Single column2 rows with a value equal to 2 and 4Construct a statement returning in a single column all the valuescontained, but not the common values.This is another question that I got in an interview, I missedit.......Thanks,Tim
Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.
I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.
I want to return a list of user tables from a database where the rowcount is 0. This will be a 3 step process: (1) truncate all 'New%' tables, (2) load data via ODBC/DTS into 'New%' tables, (3) list all 'New%' tables with zero rows (i.e. those that didn't get loaded, as all tables in the ODBC data source contain data).
I've tried: select left(s2.name,32) as TableName, max(s1.rows) as Records from sysindexes s1 inner join sysobjects s2 on s1.id=s2.id where type = 'U' and s2.name like 'New%' group by s2.name HAVING max(rows) = 0 ORDER BY TableName but of course there are multiple rows in sysindexes and the routine does not reliably return the correct list; for example the data in sysobjects & sysindexes, without the max and group by, might look like: TableNameRows NewARTxn0 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 NewARTxn1214800 I was hoping to come up with a single T-SQL statement that I could use in an xp_sendmail situation to email me the results.
From the INFORMATION_SCHEMA.TABLES view I want to return the TABLE_NAME of tables that have columns say, named Email and EmailStatusId. Is it possible to do this with a single select statement or would I have to use two selects for this?
Hi there,I have tables with such structuretransaction_YYMM(idx,date,company_id,value)where YYMM stands for 2digits year and monthI want to define query (maybe view, procedure):select * from [???] where date>='2007-01-01' and date<='2007-04-30'which will grab data fromtransaction_0701transaction_0702transaction_0703transaction_0704and return all as onebest regardsRafal
I am joining on two tables, and returning the values that do not exist in #topoftheline -- well now I see that their are multiple calltimes so I want to return ONLY the most recent call time. What would I change in my syntax to only return that most recent datetime?
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
I used a function to create dataset as below: Public Function GetSQLDataSet(ByVal SQL As String) As DataSet ...... MyConnection = New SqlConnection(MyConnectionString) MyCommand = New SqlCommand(SQL, MyConnection) MyDataSet = New DataSet MySQLDataAdapter = New SqlDataAdapter(MyCommand) MySQLDataAdapter.Fill(MyDataSet) ...... End function It works fine. How to code a function to return a dataset in which there are two tables and relationship?
I would like to know if it's possible to return a single record by joining the tables below. [Persons] PersonID [int] | PageViewed [int] =============== ================= 1 10 2 5 3 2 4 12
[PersonNames] - PersonID JOINS Persons.PersonID PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int] =============== ============== ======================= =================== 1 1 Samantha Brown 5 1 2 Samantha Green 10 2 3 Richard T 10 3 4 Riko T 0 4 5 Sammie H 0
[AltNames] - backup for searches caused by common spelling mistakes AltNameID [int] | AltNames [nvarchar] ================ ============================= 1 Sam, Samantha, Sammie, Sammy 2 Riko, Rico
[PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID] NameID [int] | AltNameID [int] ============= ================ 1 1 4 1 3 2 This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch ========= ============ ================= ================= 1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy 2 5 Richard T Richard T 3 2 Riko T Riko T, Riko, Rico 4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy
[MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames].
The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field. Any help would be greatly appreciated. Thanks, Pete.