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---    NULL
ABE01     ABEA     ABER     CTV5             H    1941    2001    MTWT---    NULL
ABE02     ABEA     ABER     CTV5             H    2045    2105    MTWTF--    NULL
ABE03     ABEA     ABER     CTV5             H    2059    2119    MTWTF--    NULL
ABE04     ABEA     ABER     CTV2.5          H    2245    2305    MTWTF--    NULL
ABE11     ABEA     ABER     WALKIN        H    2045    2100    MTWTF--    NULL
ABE11     ABEA     ABER     WALKIN        H    2045    2100    MTWTF--    MT-TF--
ABE12     ABEA     ABER     WALKIN        H    2109    2124    MTWTF--    NULL
EF038     ABEA     EWRHB    53BULK       H    0100    0245    -TWTFS-    NULL
EF085     ABEA     EWRHA    CTV5           H    1955    2140    MTWT---    NULL
EF106     ABEA     EWRHB    CTV5           H    1901    2046    -----S-    NULL
EF140     ABEA     ABER     CTV5             H    0550    0610    M------    NULL
EF166     ABEA     EWRRA    CTV5           H    2230    0010    MTWT---    NULL
EF366     ABEA     EWRRA    CTV5           H    2230    0010    ----F--    NULL
EF543     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


ADVERTISEMENT

Select Distinct Rows From Duplicate Rows....

Nov 28, 2007

Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!

View 4 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

Pulling Uniquely When You Can't Use Distinct, Or An ID

May 18, 2006

So here is the problem

I have a table, with an identity in it, I have another table, with that number, it is a one to many relationship.

That table has Fname, Lname inisde of it, some numbers have 1 record, some have 4

Here is the query


Code:


SELECT
FName = ISNULL(UPPER(LTRIM(RTRIM(addr.FName))),'')
, LName = ISNULL(UPPER(LTRIM(RTRIM(addr.LName))) ,'')
, FName2 = ISNULL(UPPER(LTRIM(RTRIM(names.FName))),'')
, LName2 = ISNULL(UPPER(LTRIM(RTRIM(names.LName))) ,'')
, FName3 = ISNULL(UPPER(LTRIM(RTRIM(names.FName))),'')
, LName3 = ISNULL(UPPER(LTRIM(RTRIM(names.LName))) ,'')
, FName4 = ISNULL(UPPER(LTRIM(RTRIM(names.FName))),'')
, LName4 = ISNULL(UPPER(LTRIM(RTRIM(names.LName))) ,'')
, Address1 = ISNULL(UPPER(LTRIM(RTRIM(addr.Address1))) ,'')
, Address2 = ISNULL(UPPER(LTRIM(RTRIM(addr.Address2))) ,'')
, City = ISNULL(UPPER(LTRIM(RTRIM(addr.City))) ,'')
, State = (SELECT StateAbbr FROM ac_States WHERE StateID = addr.StateID)
, addr.Zip

FROM Edina_Class class
LEFT JOIN Edina_Names names
ON names.claimno = class.claimno
INNER JOIN Edina_Address addr
ON class.claimno = addr.claimno
where addr.isactive = 1



What I want is to pull back the unique names, so, if the use only had 1 name, name2-4 would be blank, if the person had 3 names name4 would be blank.

Since "name1" is in the address table, I can pull that one back easy, but what is happening, is the code is taking the first name is finds in the name table and filling in names2 - names4 with it.

I have no idea how to fix the person besides recreating the name to have all of the names per user in 1 record opposed to individual records in the names table.

Hope that made sense.

Thanks

View 5 Replies View Related

Pulling Data Back From 2 Tables - Join On Top 1 Record

Nov 7, 2013

I have sql pulling back data from 2 tables (Ticket, Assignment) matching on an ID.

however the Assignment table can have more than 1 record for a matching ID in the Ticket table so is bringing back rows for each of these entries HOWEVER i only want 1 row returned matching on the FIRST record in Assignment table (on earliest DateAssigned field)

Here's my code

SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t
INNER JOIN Assignment a ON (SELECT TOP 1 TicketID FROM Assignment
WHERE [TicketID] = t.TicketID
ORDER BY DateAssigned ASC) = t.TicketID

WHERE (CreatedByUserID NOT IN (SELECT u.UserID FROM Users u
INNER JOIN Profiles p ON u.UserID = p.UserID

[Code] ....

View 4 Replies View Related

Use Of DISTINCT Keyword

Mar 8, 2004

If I use DISTINCT isn't there a rule where it must be the first field selected? Also, there can only be one DISTINCT field in a query, correct?

ie,

SELECT DISTINCT fieldA, fieldB
FROM tableA


but not

SELECT fieldA, DISTINCT fieldB
FROM tableA

or

SELECT DISTINCT fieldA, DISTINCT fieldB
FROM tableA


thanks again, this is a great forum

ddave

View 6 Replies View Related

Regarding Distinct Keyword

Apr 5, 2008

hi guys i have a query that contains several table joins
when i run the query without select distinct x,y,z,w,.. or order by docno it takes around 20 second to finish execution, when i add select distinct x,y,z,w,.. or order by docno it ruturns the same result in just 2 seconds
is adding distict keyword or order by acts as an index for the query or what ?

.
.
.
.

here is my query :




SELECT distinct p.indocno,p.CHAR_FIELD2_AR, p.CHAR_FIELD1, p.REVISION_NO, CAST(p.INDOCNO AS int) AS INDOCNO, p.CHAR_FIELD3, p.CHAR_FIELD7_AR, T.DESCRIPTION,J.DESCRIPTION AS [Section], p.SUBJECT
FROM dbo.TECHNICAL_MAIN p INNER JOIN
(SELECT MAX(revision_no) d, char_field1 c, char_field2_ar e, subcat_id j
FROM technical_main m
WHERE revision_no IN ('0', '1', '2')
GROUP BY char_field1, char_field2_ar, subcat_id) b
ON p.REVISION_NO = b.d AND p.CHAR_FIELD1 = b.c AND p.CHAR_FIELD2_AR = b.e AND p.REVISION_NO IN ('0', '1', '2')
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP T ON p.CHAR_FIELD7_AR = T.ID
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP J ON p.CHAR_FIELD3_AR = J.ID AND p.SUBCAT_ID = b.j

Good luck for all the folks

View 4 Replies View Related

Using The DISTINCT Keyword

May 11, 2006

Hi all,

I have two datatables in my database. The first table, named Books, has two columns: BookID and Author (BookID is the primary key). The second table, named Purchases, has three columns: PurchaseID, BookID, BuyerID (Purchase ID is the primary key).

The idea here is that the Books datatable contains information regarding the book and its author while the Purchases datatable contains information on who has purchased what book.

Now, say I want to write an SQL query to extract a list of all the authors who have written a book purchased by buyer X. How would I go about doing this without having any duplicate entries? I figured that the following would work:

SELECT DISTINCT * FROM Books INNER JOIN Purchases ON Books.BookID=Purchases.BookID

But this ends up generating duplicate BookIDs if the Purchases table contains several buyers who have bought that Book. I know I could use BookID rather than * in the above query and that would work, but in reality I'm dealing with more complex tables and I would rather keep the * in there to actually get all the data out in one go.

View 3 Replies View Related

Select Distinct Keyword Problems...

Aug 26, 2005

Let's say i have a database with the following structure and datatablename: customerscustomerID| customername | PictureID|1 | MyCustomer | 1.jpg |1 | MyCustomer | 1_1.jpg |1 | MyCustomer | 1_3.jpg |2 | MyCustomer2 | 2.jpg |3 | MyCustomer3 | 3.jpg |3 | MyCustomer3 | 3_2.jpg |4 | MyCustomer4 | 4_2.jpg |4 | MyCustomer4 | 4_1.jpg |Is it possible to pull back only one entry per customer? I don't carewhich Picture ID it uses. I would perfer if the query would return thetopmost PictureID for a customer, but i don't really care.desired outputcustomerID| customername | PictureID|1 | MyCustomer | 1.jpg |2 | MyCustomer2 | 2.jpg |3 | MyCustomer3 | 3.jpg |4 | MyCustomer4 | 4_2.jpg |I have tried using the DISTINCT keyword, but it does not really helpme. my original thought was to use..."Select Distinct CustomerID, Customername from Customers" but then idon't have access to the PictureID? can i use a sub query?

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

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

Sql - Pulling Data From Specific Rows

Nov 12, 2006

I have a table, multiple columns, thousands of rows.

Six of the columns is the data that i need to work with...

col1, col2, col3, col4, col5, col6

col1 and col2 - go together - example. col1 = amount col2 = description
col3 and col4 - go together col3 = amount col4 description
col5 and col6 - go together col5 amount and 6 description

i need to pull search the table based on an auto number "id" and pull in the necessary two columns that correspond with a set value in the description.

example:

if col4 has "fee applied" in the description i need to pull the amount.

Please help...

Thank you in advance

View 3 Replies View Related

How To Limit No Of Rows While Pulling Data From Oracle To Sql Through SSIS

Oct 23, 2007



Hi,

I want to pull sample records lets say 1000 rows only from oracle database to sql server. Is there any option in ssis to limit the number of rows?

View 4 Replies View Related

Most Recent 3 Episodes Per Blog (was [URGENT] Pulling Certain Rows From Table)

Mar 29, 2006

Code:


create table Channels (ChannelID int(5), other varchar(255));

create table Blogs (BlogID int(5), BlogXMLPath varchar(255));

create table BlogAssociations (BlogID int(5), ShowID int(5), ChannelID int (5));

create table Episodes (EpisodeID int(6), BlogID int(5), ShowID int(5), ChannelID int(5), Other varchar(30));

insert into Channels values (1, 'Channel 1');
insert into Channels values (2, 'Channel 2');
insert into Channels values (3, 'Channel 3');
insert into Channels values (4, 'Channel 4');

insert into Blogs values ('1', 'blah');
insert into Blogs values ('3', 'blah');
insert into Blogs values ('4', 'blah');
insert into Blogs values ('12', 'blah');
insert into Blogs values ('34', 'blah');
insert into Blogs values ('35', 'blah');
insert into Blogs values ('67', 'blah');

insert into Episodes values (1, 3, '', '', 'Episode 1, blog 3');
insert into Episodes values (2, 3, '', '', 'Episode 2, blog 3');
insert into Episodes values (3, 3, '', '', 'Episode 3, blog 3');
insert into Episodes values (4, 3, '', '', 'Episode 4, blog 3');
insert into Episodes values (5, 1, '', '', 'Episode 5, blog 1');
insert into Episodes values (6, 1, '', '', 'Episode 6, blog 1');
insert into Episodes values (7, 1, '', '', 'Episode 7, blog 1');
insert into Episodes values (8, 4, '', '', 'Episode 8, blog 4');
insert into Episodes values (9, 4, '', '', 'Episode 9, blog 4');
insert into Episodes values (10, 4, '', '', 'Episode 10, blog 4');
insert into Episodes values (11, 4, '', '', 'Episode 11, blog 4');
insert into Episodes values (12, 4, '', '', 'Episode 12, blog 4');
insert into Episodes values (13, 12, '', '', 'Episode 13, blog 12');
insert into Episodes values (14, 12, '', '', 'Episode 14, blog 12');
insert into Episodes values (15, 12, '', '', 'Episode 15, blog 12');
insert into Episodes values (16, 12, '', '', 'Episode 16, blog 12');
insert into Episodes values (17, 12, '', '', 'Episode 17, blog 12');
insert into Episodes values (18, 34, '', '', 'Episode 18, blog 34');
insert into Episodes values (19, 34, '', '', 'Episode 19, blog 34');
insert into Episodes values (20, 34, '', '', 'Episode 20, blog 34');
insert into Episodes values (21, 34, '', '', 'Episode 21, blog 34');
insert into Episodes values (22, 35, '', '', 'Episode 22, blog 35');
insert into Episodes values (23, 35, '', '', 'Episode 23, blog 35');
insert into Episodes values (24, 35, '', '', 'Episode 24, blog 35');
insert into Episodes values (25, 35, '', '', 'Episode 25, blog 35');
insert into Episodes values (26, 67, '', '', 'Episode 26, blog 67');
insert into Episodes values (27, 67, '', '', 'Episode 27, blog 67');
insert into Episodes values (28, 67, '', '', 'Episode 28, blog 67');
insert into Episodes values (29, 67, '', '', 'Episode 29, blog 67');
insert into Episodes values (30, 67, '', '', 'Episode 30, blog 67');

insert into BlogAssociations values (3,'',1);
insert into BlogAssociations values (12,'',1);



There is my above sql script, i'm currently using mysql at home to test things but will convert to work in mssql.
I need to get associated BlogID for Certain ChannelID from the BlogAssociations table.
Then using those BlogID's that it finds for let just say ChannelID "1" list off the 3 most recent EpisodeID's from the Episodes Table per each BlogID.
So if ChannelID "1" has four BlogID's associated with it then in the Episodes table list off the recent 3 EpisodeIDs for BlogID "3", then the 3 most recent EpisodeIDs for BlogID "5" and so on and so on.

I have this which kind of does what I want:

Code:


select
e.BlogID, e.EpisodeID, e.other
FROM
episodes e, BlogAssociations ba
WHERE
e.BlogID = ba.BlogID and ba.ChannelID = '1'
ORDER BY
e.EpisodeID DESC;



It returns:

Code:


+--------+-----------+---------------------+
| BlogID | EpisodeID | other |
+--------+-----------+---------------------+
| 12 | 17 | Episode 17, blog 12 |
| 12 | 16 | Episode 16, blog 12 |
| 12 | 15 | Episode 15, blog 12 |
| 12 | 14 | Episode 14, blog 12 |
| 12 | 13 | Episode 13, blog 12 |
| 3 | 4 | Episode 4, blog 3 |
| 3 | 3 | Episode 3, blog 3 |
| 3 | 2 | Episode 2, blog 3 |
| 3 | 1 | Episode 1, blog 3 |
+--------+-----------+---------------------+



But as you can see it lists off 5 of the Episodes for BlogID "12", I only want the most recent 3 as previously stated. It also lists off more than 3 Episodes for BlogID "3". How in the world do I go about doing this?
I'm making this a stored procedure so I can't use php otherwise I wouldn't even be posting

Thanks so much!

View 6 Replies View Related

How To Bring Back The Distinct Values In Single Column From Two Tables

Jul 20, 2005

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

View 1 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

DISTINCT Not Working To Eliminate Duplicate Column Names

Feb 28, 2008

In my employee table has the following fields empid, empFname, empLname, email, city
Say it has data like follows:
1, Lucy, Sam, l@some.com, city1
2. Sam, Wite, l@some.com, city2
3. Laura, Mac, l@some.com, city2
4. Stacy, Soo, s@no.com , city1
So in my case I want to show all the column but I want to eliminate multiple email addresses.  I tried Distinct but its not workin because here every column is not distinct.  So what should I use?
In my case I only want to show empID 1, 3, 4.  I want to show all the columns

View 5 Replies View Related

Putting The Like Keyword In My Select Statement

Jan 23, 2008

I have the following sql string in my asp.net, its meant to retreive a value based on a text box value being "like" a value from my database, however when i place a word in the textbox nothing happens, can someone please  take a look at the statement and see if its well formed,
String sql = "SELECT fName FROM Customers WHERE fName LIKE " + "'" + fName.Text + "/%' OR PostCode= " + "'" + Postcode.Text + "'";

View 3 Replies View Related

SQL Server Does Not Support LIMIT Keyword In SQL Statement?

Jun 5, 2004

What is the SQL Server equivalent of the LIMIT keyword available in MySQL?

SELECT * FROM MyTable WHERE ID = @ID LIMIT 1


this SQL does not work? I get an error saying that there is an error at keyword LIMIT. When I remove LIMIT 1, it works...

is there anything like this in SQL Server?

View 4 Replies View Related

Correct Syntax To Pull Back Duplicate Vendors Based On 6 Fields From Two Different Tables

Feb 20, 2015

I'm looking for the correct syntax to pull back duplicate vendors based on 6 fields from two different tables. I want to actually see the duplicate vendor information (not just a count). I am able to pull this for one of the tables, something like below:

select *
from VendTable1 a
join ( select firstname, lastname
from VendTable1
group by firstname, lastname
having count(*) > 1 ) b
on a.firstname = b.firstname
and a.lastname = b.lastname

I'm running into issues when trying to add the other table with the 4 other fields.

View 5 Replies View Related

Keyword Seach (Not Full Phrase) Parameterise Sql Statement

Apr 11, 2007

hi,
i want to do search by keywords for e.g "John Smith". should search for "John" and "Smith"
it is easy to do it using dynamic sql statement.
but i am using parameters sql.
this is my sql
"select * from emp_tbl where fname like '%' + @keyw + '%' or lname like '%' + @keyw + '%' " 
the above sql will search by full phrase
how can i make it search each word in the phrase.
 
aslo, i am searching for 70-551 exam. to upgrade my mcad to mcts.
can anybody help. 
 

View 9 Replies View Related

Incorrect Syntax Near The Keyword 'EXEC' (Dynamic Sql Statement Error)

Nov 1, 2007

Following is the stored procedure iam trying to create.Here i am trying to

First create a table with the table name passed as parameter
Second I am executing a dynamic sql statement ("SELECT @sql= 'Select * from table") that returns some rows.
Third I want to save the rows returned by the dynamic sql statement ("SELECT @sql= 'Select * from table") in the tablei created above.All the columns and datatypes are matching.

This table would be further used with cursor.
Now i am getting a syntax error on the last line.Though i doubt whether the last 3 lines will execute properly.Infact how to execute a sp_executesql procedure in another dynamic sql statement.ANy suggestions will be appreciated.


CREATE PROCEDURE [dbo].[sp_try]

@TempTable varchar(25)


AS

DECLARE @SQL nvarchar(MAX)

DECLARE @SQLINSERT nvarchar(MAX)



BEGIN


--create temp table

SELECT @Sql= N'CREATE TABLE ' + QUOTENAME(@TempTable) +

'(

ContactName varchar (40) NOT NULL ,

ContactId varchar (30) NOT NULL ,

ContactrMessage varchar (100) NOT NULL,



)'

EXEC sp_executesql @Sql, N'@TempTable varchar(25)', @TempTable = @TempTable


SELECT @sql= 'Select * from table'




SELECT @sqlinsert = 'INSERT INTO ' + quotename( @TempTable )

SELECT @sqlinsert = @sqlinsert + EXEC sp_executesql @sql, N'@Condition varchar(max)', @Condition=@Condition

EXEC sp_executesql @SQLINSERT, N'@TempTable varchar(25)', @TempTable = @TempTable

View 8 Replies View Related

Not Getting Back Rows From SQL

Feb 13, 2008

Hi,
I am trying to connect to a SQL DB and pull data from it. But i never get back any rows. I have run the query, it works fine and i get back rows. But htis does not seem to work. HELP ?
Attaching my code 
TryDim ConnStr As String = System.Configuration.ConfigurationManager.ConnectionStrings("Engineering_Health_SiteConnectionString").ToStringURLConn = New Data.SqlClient.SqlConnection(ConnStr)
URLConn.Open()
 Dim param1 As New Data.SqlClient.SqlParameter("@FG", Data.SqlDbType.NVarChar)
Dim param2 As New Data.SqlClient.SqlParameter("@SS", Data.SqlDbType.NVarChar)
Dim query As String = "Engineering_Health_Site.dbo.usp_GetSiteURL"Dim comm As New Data.SqlClient.SqlCommand(query)
comm.Connection = URLConn
comm.CommandType = Data.CommandType.StoredProcedureparam1.Value = Me.DropDownList1.SelectedValue.Trim
comm.Parameters.Add(param1)param2.Value = Me.DropDownList2.SelectedValue.Trim
comm.Parameters.Add(param2)Dim adap As New Data.SqlClient.SqlDataAdapter(comm)Dim dt As New Data.DataTable
adap.Fill(dt)For Each row As Data.DataRow In dt.Rows
Response.Write(row(1).ToString)
NextCatch ex As Exception
Response.Write(ex.Message)
Finally
URLConn.Close()
End Try

View 4 Replies View Related

CTE Error: Incorrect Syntax Near The Keyword 'with'. If This Statement Is A Common Table Expression Or An Xmlnamespaces Clause,

Aug 3, 2006

I am having this error when using execute query for CTE

Help will be appriciated

View 9 Replies View Related

Getting The Right Distinct Rows

Jan 21, 2008

I have a database for a CMS I have made, which has a column called ‘tag’ everytime a page is updated it inserts another row in to the table with the same tag but with a updated date. i use this method so i have a version history
What I want to get out is rows that have distinct tag columns and is also the newest row associated with that ‘tag’.

View 3 Replies View Related

Selecting Distinct Rows

Jun 11, 2008

Hi,
I want to select the 8 most saled products from large orders table... the problem is that when i use the "distinct" sentence (something like this- "SELECT TOP 8 distinct id, products, productid FROM tbl_orders ORDER BY id") I get back the distinct of any columns.... (and any ID is distinct, of course), but if i don't include the id's in the distinct sentence, i can't order by id's.
 can i get the last orders, only by distinct product, and not by distinct id, and order them by the id's?
  

View 17 Replies View Related

Select Distinct Rows

Mar 19, 2004

Hi,

I'm having a little bit of trouble trying to figure out how to do this query, right now I have:

SELECT I.AppItemId, P.ProductID, P.PartNum, P.Relist, I.AppUserId
FROM ProductsToRelist I join Products P on P.ProductID = I.AppSKU
WHERE P.Relist = 1 and I.AppStatus = 5 and Not I.AppItemId is Null

and it returns something like this:

AppItemId ProductID PartNum Relist AppUserId
2786 -32730 SELECT_OOS11
2787 -32729 SELECT12
2788 -32727 SELECT_OOS11
4269 -30987 SELECT_OOS12
1665 -30987 SELECT_OOS11
2433 -30987 SELECT_OOS11
4272 -30984 SELECT11
2436 -30984 SELECT11
2793 -32708 SELECT11


But I only it want it to return 1 record for each ProductID like so:

AppItemId ProductID PartNum Relist AppUserId
2786 -32730 SELECT_OOS11
2787 -32729 SELECT12
2788 -32727 SELECT_OOS11
4269 -30987 SELECT_OOS12
4272 -30984 SELECT11
2793 -32708 SELECT11


ProductID is the primary key for the Products table, and a product can be in the ProductsToRelist table many times but each row would have a unique AppItemId. I know that I need to use Distinct or a different kind of join, but I'm not sure which. How would you suggest to do this?

Thanks

View 4 Replies View Related

Select Distinct For Different Rows

Jun 5, 2006

I have the following tablecolumns:  [col1], [col2],[col3] and [NAME].I want to select the name column for each row where [col1]='07'.The problem is that there are several rows where [col1] contains '07' and also the name is the same. [col2] and [col3] contain different data for these double rows...however, I cant use the [col1] and [col2] values in my query because I dont know what values they contain beforehand.So now, when I execute my query and add the DISTINCT key I still get all the double rows!I hope this explains my problem, help is really appreciated...ow, btw: deleting the double rows is not an option....

View 4 Replies View Related

Selecting Distinct Rows ??

Oct 27, 1998

View 3 Replies View Related

Move Next And Back Through Rows Using Datareader

Sep 16, 2007

I have multiple dropdownlists each one filled with values from a specific column in the table. Also I have multiple textboxes corresponding to dropdownlists. For example, when I select an item from dropdownlistA, all the textboxes are filled with the first row values that contains that selected item and gives the number of rows containing this value……. In addition, I have 2 buttons one is Move Forward Button and the other is Move Previous…I am using a Record Set and don’t know how to move next and back throughout the selected rows…could you help me please? I am using a vb codebehind… Thanks

View 4 Replies View Related

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

Distinct Random Rows Using NewID()

May 26, 2008

I have 2 tables, Artists and Artworks.
I have a query:

SELECT TOP (4) dbo.Artists.ArtistID, dbo.Artists.FirstName + ' ' + dbo.Artists.LastName AS FullName, dbo.Artworks.ArtworkName, dbo.Artworks.Image
FROM dbo.Artists INNER JOIN
dbo.Artworks ON dbo.Artists.ArtistID = dbo.Artworks.ArtistID
ORDER BY NEWID()

This query returns random images, but the artists are sometimes repeated.
I would like to have DISTINCT Random Artists returned, each with a random image. I tried various subqueries, but I just get error messages.
Any help would be appreciated.
Thnks,

Paolo

View 8 Replies View Related







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