Help With Distinct SQL Statement

Oct 14, 2006

I'm trying to return a select statement with distinct values. I have 3
tables: Documents, DocumentAuthors and the linking table
DocumentAuthorsRel. Since a document can have multiple authors, the
DocumentAuthorsRel table holds the DocumentID and DocumentAuthorID
values.

I simply want to run a query which displays a list of
all the document titles (no document title should be repeated, i only
want to show the title once, distinct) held in the documents table, and
with the document title, show the last names of a documents author(s).

This
is the statement Im using which is returning duplicated document titles
(as a result of a document having multiple authors - found in the
DocumentAuthorsRel table)


SELECT Documents.DocumentID, Documents.Title, DocumentAuthors.AuthorLName
FROM DocumentAuthors INNER JOIN
DocumentAuthorsREL ON DocumentAuthors.DocumentAuthorID = DocumentAuthorsREL.DocumentAuthorID INNER JOIN
Documents ON DocumentAuthorsREL.DocumentID = Documents.DocumentID


Any help would be appreciated.

thanks, -lance

View 6 Replies


ADVERTISEMENT

Distinct In Select Statement

May 12, 2004

Hey there, is there a way I can use command such as distinct in a select statement to do the following. Lets say I want to do a search of products based off their location and I want to list the companies that will have products in that area. I only want to list the company once, but if I’m searching by products in the area I might come up with 15 results for that company. I have not written the code yet for this, I’m just planning ahead.

I’m programming using VB so I guess I would do something like this.

State = Trim(Request.QueryString("State"))

SelectStatement = "Select * From Products Where State='" & _
State & "'"

This would of course give me hypothetically speaking a list as long as the amount of products in one given area. Is there a way to cut this down and only list the company once? Any help would be greatly appreciated. Thanks in advance.

View 2 Replies View Related

Use Select Distinct Statement

Nov 14, 2014

I am new to SQL and am trying to use the Select Distinct statement and am having some issues getting it to work. When I use it on only 1 column it is working fine, but I want to use it on 2 columns it does not do what I expect it to.here is my code:

SELECT *
INTO #NEWTEMP
FROM DBO.REPORTDATA;
ALTER TABLE #NEWTEMP
ALTER COLUMN UTCTime DATE

[code]....

In the results table I have 3 entries for each signalid with the same UTCTime, I only want to have 1

View 6 Replies View Related

Retrieve Distinct Rows From Select Statement

Jan 10, 2014

Is it possible to retrieve Distinct rows from this Select Statement?

I'm getting the correct results, but duplicate rows because some customers place more than one order on the same day.

Code:
SELECT dbo.Customers.CustomerID, dbo.Customers.Title, dbo.Customers.FirstName, dbo.Customers.LastName, dbo.Customers.CustomerEmail, dbo.Customers.DateCreated,
CONVERT(char, dbo.Customers.DateCreated, 103) AS [DD/MM/YYYY], dbo.loyalty_points.LPoints, dbo.Orders.OrderID
FROM dbo.Customers INNER JOIN
dbo.loyalty_points ON dbo.Customers.CustomerID = dbo.loyalty_points.CustomerID INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
WHERE (CONVERT(char, dbo.Customers.DateCreated, 103) = CONVERT(char, GETDATE() - 6, 103))

View 8 Replies View Related

HOW TO USE DISTINCT IN SELECT STATEMENT TO FILTER OUT DUPLICATED RECORD??

Jan 5, 2001

I HAVE A SELECT STATEMENT WITH TEACHERS AND STUDENTS AND SOMETHING ELSE TOO.
FOR EACH TEACHER I ONLY NEED ONE(FIRST ONE) STUDENT.
HOW THE STATEMENT SHOULD BE?

SELECT DISTINCT .... TID, SID, SOMETHING ???????

View 3 Replies View Related

Help With SQL Statement, Pulling Back Duplicate Rows On DISTINCT Keyword

Feb 28, 2008

Hello everyone, I'm working on a SQL statement that I "thought" worked fine until I noticed I was getting a duplicate row.  Below is the SQL statement from the stored procedure: SELECT DISTINCT number AS 'RteNum', leg_orig AS 'Origin',
leg_dest AS 'Dest', AcEquipment.EquipmentDesc AS 'EquipType',
SUBSTRING(trailer_option, 1, 1) AS 'TrailerOption',
leg_depart_time_local AS 'DeptTime',
leg_arrive_time_local AS 'ArrTime',
dev.fnConvertEffectiveDaysToDaysOfWeek(SUBSTRING(leg_effective_local, 2 ,7)) AS 'EffectiveDays',
TruckEditor.EffectiveDays as 'NewEffectiveDays'
FROM lhif_prod
JOIN AcEquipment ON AcEquipment.EquipmentType = lhif_prod.Equipment_Type
LEFT JOIN dev.TruckEditor ON TruckEditor.Origin = lhif_prod.leg_orig AND TruckEditor.Dest = lhif_prod.leg_dest
AND TruckEditor.RouteNum = lhif_prod.number AND TruckEditor.DeptDate = lhif_prod.leg_depart_date_local
WHERE leg_depart_date_local BETWEEN @DateStart AND @DateEnd
AND Type_Code = 'T' AND leg_orig = @LocID
ORDER BY RteNum, Dest, DeptTime  Here is what comes back from this query:ABE00     ABEA     ABER     CTV5             H    1855    1915    MTWT---    NULLABE01     ABEA     ABER     CTV5             H    1941    2001    MTWT---    NULLABE02     ABEA     ABER     CTV5             H    2045    2105    MTWTF--    NULLABE03     ABEA     ABER     CTV5             H    2059    2119    MTWTF--    NULLABE04     ABEA     ABER     CTV2.5          H    2245    2305    MTWTF--    NULLABE11     ABEA     ABER     WALKIN        H    2045    2100    MTWTF--    NULLABE11     ABEA     ABER     WALKIN        H    2045    2100    MTWTF--    MT-TF--ABE12     ABEA     ABER     WALKIN        H    2109    2124    MTWTF--    NULLEF038     ABEA     EWRHB    53BULK       H    0100    0245    -TWTFS-    NULLEF085     ABEA     EWRHA    CTV5           H    1955    2140    MTWT---    NULLEF106     ABEA     EWRHB    CTV5           H    1901    2046    -----S-    NULLEF140     ABEA     ABER     CTV5             H    0550    0610    M------    NULLEF166     ABEA     EWRRA    CTV5           H    2230    0010    MTWT---    NULLEF366     ABEA     EWRRA    CTV5           H    2230    0010    ----F--    NULLEF543     ABEA     EWRRA    CTV5           H    2200    2345    MTWTF--    NULL The 2 rows in bold are the issue right now.  There should only be 1 row (the 2nd one where the last column is not null).  I'm not sure why it returns both columns when I'm doing a join on there to add that last column.  Can anyone help me out with this?  I'm not very strong in SQL, so if I'm overlooking something, I'd appreciate any help you can provide.  Thanks. 

View 2 Replies View Related

Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?

Jul 6, 2007

Hi, I have the following script segment which is failing:

CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))

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.

Any ideas?

View 2 Replies View Related

Trying To Add A NON-DISTINCT Field To A DISTINCT Record Set In A Query.

Mar 12, 2007

I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks

View 2 Replies View Related

DISTINCT To ShortDateString, Not DISTINCT To The DateTime; How?

May 8, 2006

Hello,
I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )
This app also calculates the total number of proposals coming from a specific branch in a given date bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand prints them in a table (my target table).
This target table has as many rows as the result of  the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the"SELECT DISTINCT BranchID FROM Proposals". This target table converts the DateTime values ToShortDateString so that we are able to see comfortably which branch office has sent how many proposals in a given day.
So far so good, and everything works fine except one thing:
Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and11.11.2005 10:45:30) cause some trouble in the target table,  where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)
What I need to do is to get a result like (in a neat fashion :)
"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"
So, how to do it in a suitable way?
Thanks in advance.

View 4 Replies View Related

How To Show Distinct Rows Of The Column Of The Dataset And Number Of Distinct Rows Of That Column

Mar 29, 2007

suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"

I want's some thing like

field1 rowcount
aaa 5
bbb 6

View 1 Replies View Related

Distinct Values Among Distinct Column Values

Jan 9, 2015

Okay, I've been working on this for a couple of hours with no success. I'm trying to find the number of telephone numbers that are associated with multiple students at different school sites. I've created a temp table that lists all phone numbers that are associated with more than one student. I'm now trying to query that table and count the number of telephone numbers that are associated with more than one site. Essentially, I'm looking for parent/guardians that have students at different sites.

Here's an example of what I'm hoping to accomplish:

*In this example, I'm just trying to get a count of the different/distinct school sites associated with each number. If I can, at the same time, limit it to a count of > 1 (essentially excluding parents with students at the same site), even better :)

===================================
Temp table
===================================
SCHOOL | STU_ID | STU_PHONE
101 | 12345 | 111-222-3333
101 | 23456 | 111-222-3333
102 | 34567 | 111-222-3333
101 | 45678 | 999-888-7777
101 | 56789 | 999-888-7777
101 | 67890 | 555-555-5555
102 | 78901 | 555-555-5555
103 | 89012 | 555-555-5555

==================================
Wanted query results
==================================
STU_PHONE | #Students | UNIQUE_SCHOOLS
111-222-3333 | 3 | 2
999-888-7777 | 2 | 1
555-555-5555 | 3 | 3

View 1 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View 2 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

------------- ----------------------

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related

DB Engine :: Can't Use The MERGE Statement / How To Design WHERE Condition For Insert Statement

Nov 5, 2015

I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.

View 2 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

Not Too Many Distinct..

Sep 27, 2007

If I know this SELECT will get me unique username to configname records:Select DISTINCT configname, username FROM EtechModelRequests

JOIN CC_host.dbo.usr_smc ON
username = user_id

JOIN Webservices.dbo.PartNumberPricingImport ON
PartNumber = configname

Where RequestDateTime > '9/26/2007' And country = 'US' And interfacename Like '%download%' And result = 0  
 How do I show the other fields I need? The field I need is List Price but I don't want to DISTINCT on it too.

View 2 Replies View Related

Distinct And Top

Mar 31, 2008

Hi I have two tables linked by MemberID and I'll like to produce a list from the two tables but also want to specify one table to only use the Top 50 distinct records
 
Table 1 (top 50 Distinct)
Memberid (distinct)PictureDateTakenGalleryID
Order By DateTaken
Table 2
MemberidFirstNameLastNamePrivatelist
MemberidPictureFirstNameLastNameGalleryIDPrivate

View 6 Replies View Related

Distinct

Apr 2, 2004

I have a query and it is bringing up multiples of the same data
what i would like to know is if there is a way to use something like Distinct that i can use as a clause
such as

Select *
from Table
Where Distinct ColumnName

I know that distinct doesnt work in this situation
I would like to know if there is a command to do this or a way to fix it
if i use this

select distinct columnName,columnName2
from table

it returns the rows where columnName and ColumnName2 both are not the same
My purpose is that i need to select more than one column but i would like non duplicates based on ONE column name

thanks for any help

View 20 Replies View Related

Using DISTINCT

May 31, 2002

I have 6 fields in my table and I want to display only distinct values from one of those columns. However, I also need to use the fields from the remaining 5 columns in my asp page. For example,

Columns:
CompID
CompanyName
Ticker
Industry
CEOName
MarketCap

In one table on the page, I want to display only unique entries for 'CompanyName' which I've been doing with this statement:

SqlText = "SELECT DISTINCT CompanyName FROM [tablename] WHERE UserID=" & intUserID & ""

However, I also need to use the other values associated 'CompanyName' in my asp page after opening my recordset. I need to display <%=Rs("CompID"%>, <%=Rs("Ticker"%>, <%=Rs("Industry"%> etc. while maintaining the DISTINCT portion of my statement.

Thanks again.

View 1 Replies View Related

Using Distinct

Sep 12, 2006

I have 3 records in a db..

Table
id
file_name
category

Records
1
bob.jpg
male

2
rob.jpg
male

3
pam.jpg
female

I am trying to grab the distinct category so only 'male and female' is output, but i am also needing to grab the id, file_name to use later on the page as well.

When i try

select distinct category, id, file_name
from tbl_pictures

it outputs all the records since there is no exact match in all 3 fields,

but i am wanting this to happen

select distinct category
from tbl_pictures

but i am still needing to grab the other two fields because i need to use them in the next part of the page

how can i go about doing this

View 1 Replies View Related

Distinct

Apr 22, 2008

What is the difference between distinct and group by please?

View 4 Replies View Related

DISTINCT... Help :)

Apr 24, 2008

Hi,
I have data in several tables and Im having trouble filtering the data.

This is statement that Im executing:
1. SELECT DISTINCT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills

This is the statement that gives me the results I want:
2. SELECT DISTINCT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName

The problem with this is I need to have Persons.Skills in the statement and DISTINCT doesnt filter the data the way I need it to because the data in Persons.Skills is different which results in I get duplicated results.

Is it possible to do something like
SELECT DISTINCT column1,..,column5,(SELECT Persons.Skills)

by this I mean to apply distinct to some of the columns in the SELECT statement?

View 6 Replies View Related

Distinct Value

May 20, 2008

I require only the distinct objects that were depend on my stored procedures

i tried for sp_depends test_sp
for this
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolayout_slno
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolayout_type
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolevel1
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolayout_operator
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonorlevel1desc
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonorlevel2desc
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenoyesguid
dbo.abb_account_budget_dtluser tablenonocompany_code
dbo.abb_account_budget_dtluser tablenonofb_id
dbo.abb_account_budget_dtluser tablenonofin_year_code
dbo.abb_account_budget_dtluser tablenonofin_period_code
dbo.abb_account_budget_dtluser tablenonoaccount_code

being dbo.abb_account_budget_dtl were repeated more than once.i need only the distinct objects..any other system procedure is there??

View 1 Replies View Related

DISTINCT

Jun 3, 2008

I'm trying to find the Distinct for CODEID but it's still returning duplicate data. What is the best way to write this query so that the result I get are Distinct Codeid?

SELECT DISTINCT dbo.AgreementDurationCode.CodeID, dbo.Item.PartNumber, dbo.ItemShadow.AgreementDurationCode, dbo.AgreementDurationCode.CodeCategory,
dbo.AgreementDurationCode.CodeCategoryID, dbo.AgreementDurationCode.Code, dbo.AgreementDurationCode.CodeName,
dbo.AgreementDurationCode.CodeAbbreviation, dbo.AgreementDurationCode.CodeShortAbbreviation, dbo.Item.ItemName,
dbo.AgreementDurationCode.CodeMaxcimAbbreviation, dbo.ItemShadow.ItemStatusCode, dbo.ItemShadow.LicenseTypeCode
FROM dbo.Item INNER JOIN
dbo.ItemShadow ON dbo.Item.ItemID = dbo.ItemShadow.ItemID INNER JOIN
dbo.AgreementDurationCode ON dbo.Item.AgreementDurationCodeID = dbo.AgreementDurationCode.CodeID
WHERE (dbo.ItemShadow.LicenseTypeCode IN ('SEL', 'OLP', 'OLV')) AND (dbo.ItemShadow.ItemStatusCode = 'COM')

View 3 Replies View Related

How To Get Distinct ID With Same Value

Apr 18, 2014

I have data like this

course_id Session
--------- --------
1234 8.00 - 10.00
4567 8.00 - 10.00

From the above table i want the result to be like this

course_id1 course_id2 session
---------- ---------- -------
1234 4567 8.00 - 10.00

Looking for query to get the above result format.

View 3 Replies View Related

DISTINCT

Mar 13, 2007

Hi All,

I am using SQL Server 2000. I have 3 fields in my table. I want to do distinct on one field but also want to show the remaining two fields in the result. How can I do that?

Thanks

-G

View 6 Replies View Related

Using DISTINCT

Apr 6, 2007

Is there some way to use the distinct keyword so that it applies ONLY to a subset of the items in the select list???

For example, suppose I want to
select col_1, col_2, col_3, col_4

but I do not want distinct applied against all 4 items...
Maybe I want all 4 items in the selection list,
but I want distinct to use only col_3 as its filtration criteria...

I know the syntax shown below is not valid, but I am showing it anyway because I am hoping it will illustrate what I am looking for...

Is there a VALID syntax that is something like this???

select col_1, col_2, (distinct col_3), col_4
or
select col_1, col_2, distinct (col_3), col_4

View 5 Replies View Related

Using Distinct

May 22, 2007

I am trying to pull information out of a table using distinct, but instead of just pulling a certain column I want to pull multiple columns in a row?

However when I use the command below I only get the "workitem_number" column available, where there are approx. 4 other columns that I need (workitem_title, workitem_comment, ETC) When I add the additional columns (after the distinct statement) it doesn't work due to the type of data.

"SELECT DISTINCT workitem_number from workitem_cost_view where assigned_to_worker_nt_id = '" & Request.QueryString("Name") & "' AND workitem_start_on = '" & Request.QueryString("schDate") & "' order by workitem_number"

Is there any way to pull multiple columns, based only on distinct for one column?

I hope I'm explaining this correctly.

Thanks.

Rwj6001

View 13 Replies View Related

How To Have Distinct Value?

Sep 1, 2007

Hi

I have table 'TblProd' and columns ID, categories, make, description, model. I am using the below procedure


create procedure getsubcategories
@category nvarchar (32)
As
Select ID, make, model from TblProd where category = @category
GO

My question how do I get distinct 'make' and other respective columns.

advance thanks

View 11 Replies View Related







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