.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SELECT DISTINCT and INNER JOIN causing failure

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server

edit: SQL Server 2008

INSERT CompanySerialNumbers
SELECT DISTINCT h.TagName, h.Value, Entry = '5', MIN(h.DateTime)
FROM LineNumber L
INNER REMOTE JOIN INSQL.RunTime.dbo.StringHistory h ON L.Tagname = h.Tagname
WHERE L.Line = 'L155' AND h.Value != 'NULL' AND NULLIF (h.Value,') !- 'NULL'
GROUP BY h.TagName, Value

So the goal of this is to, first, compare the input Line against a database (LineNumber) containing all Tagnames and which Line they relate to.  Then look through the StringHistory database for all those Tagnames, and return only the distinct rows in the select statement.  The error(s) I am getting is/are:

OLE DB provider "INSQL" for linked server "INSQL" returned message "History queries must contain at least one valid tagname".

Cannot execute the query "SELECT tbl11007.DateTime Co11015.Tagname etc" against OLE DB provider "INSQL" for linked server "INSQL".


I have a similar query tested and working, but isn't doing a SELECT DISTINCT, just a SELECT.  I cannot, for the life of me, figure out what the problem is.

View Complete Post

More Related Resource Links

select max record to join another table sybase

select a.pono,(select (user) from user where userid=a.userid having date=max(date)) as user from a inner join b on a.no=b.no  in the result , i have selected the same id and retrieve two records every thing are same except the date how can i select the record out of two record which date is max date as the where Clasuse to select correct user poid    date                name 1        12/08/2010      Mary 1        20/08/2010      Peter   now i would like to select name which id=1 and date is max and then use the name to join another table because name is foreign key  

select distinct best row based on a condition

Hi all . The application I'm working on only allowed that one user had one usergroup. I create a User_UserGroup table to allow a user to have n usergroups. My problem now is the following: User 'ablanco' belongs to UserGroup 'comercial' and 'financeira' If i execute this commands i get the following results: SELECT * FROM [T_APP_ModulePermition] WHERE [UserGroup] = 'comercial' http://img26.imageshack.us/i/49234843.png ----------------------------------------------------------------------------------- SELECT * FROM [T_APP_ModulePermition] WHERE [UserGroup] = 'financeira' http://img259.imageshack.us/i/32526938.png ----------------------------------------------------------------------------------- I now need to get distinct rows that belong to ablanco's Usergroups but if on one group the module is Enable=0 and on the other the same module is Enable=1 it should return the ont that has Enable = 1 The attempt i made without taking in consideration this 'Enable' part was the following: SELECT MP.* FROM [T_APP_ModulePermition] MP     INNER JOIN T_APP_UserGroup_Detail UGD ON MP.UserGroup=UGD.UserGroup     INNER JOIN T_APP_User U ON UGD.UserId = U.UserId WHERE U.UserId='ablanco' But since i cant use DISTINCT.* i get all the 14 rows. If someone can help me on this i would be thankful.

Get Distinct value from a Group-Join syntax Linq

Hello,I have this group-join syntax but I couldn't get the distinct value from the relational tables below.Table_1key_1 t1_value1     Food2     Sports3     Leisure 4     Trip5     ZooTable_2key_2   key_1  t2_boolean15        1          True16        1          True17        1          True18        2          True19        2          True20        2          Falsedesire result:FoodSports            Dim c = (From t1 In dbContext_Table_1 _                                     Group Join GroupTable In _db.Table_2 On GroupTable.key_1 Equals t1.key_1 Into Group 

Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks,  Sambath

Cannot get distinct records from a multiple inner join request


Hello everybody,

This is my first post here because this request is giving me serious headache.

What I'm trying to do : This is a small search engine. I ask SQL Server 2008 to return the 20 records I need in a specific page from a larger set of 1000 records. Then I want it to return only distinct records on the key t_ye.numenr (VID).

This returns doubled or tripled records. What am I doing wrong ?



declare @explicitMaximum int <br/>
declare @chosenPage int <br/>
declare @recordsPerPage int <br/>
set @explicitMaximum = 1000 <br/>
set @chosenPage = 1 <br/>
set @recordsPerPage =20; <br/>
WITH X AS (<br/>
SELECT DISTINCT TOP(@explicitMaximum) t_ye.numenr AS VID, <br/>

ORDER BY items must appear in the select list if SELECT DISTINCT is specified


Hi, i am using a query to get DISTINCT hotels from my Database table sorted by Price and it returns me same hotel times of record found by Price if i use this Query

SELECT distinct(hotelname), availstatus, hoteladdress, hotelphonenumber, hotelpostalcode, destination, starrating, imageurl, hotelcode, TempSession, HotelType, MinimumNightRate from HproTemp where TempSession = '12-53-37' ORDER BY MinimumNightRate

and i get the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified" When i remove MinimumNightRate from SELECT LIST.

Can anyone have any idea how may i resolve it..

LINQ select distinct



I have a select like this one:

var x = from t in dc.table
where t.sourceId = 5
order by t.itemId descending
select new { t.itemId, t.sourceId, t.title }

I would select only the records with a unique title and let the other fields like sourceId to have duplicates. How can I do this please ?

ORDER BY items must appear in the select list if SELECT DISTINCT is specified


why am i getting the error message when both ORDER BY items appear in the SELECT query?



	CONVERT(VARCHAR(8),BI.DateFrom ,3) as FFromDate,
	CONVERT(VARCHAR(8),B.CreatedDate ,3) as FBookingDate,
	CAST(FLOOR( CAST( B.CreatedDate AS FLOAT ) ) AS DATETIME) as BookingDate,
	(C.Forename + CASE 

Select Distinct Problem.



I have a user defined data table like this.





/****** Object:  UserDefinedTableType [dbo].[udtt_AssemblyItem]    Script Date: 10/05/2010 11:47:57 ******/

CREATE TYPE [dbo].[udtt_AssemblyItem] AS TABLE(

[Id] [int] NOT NULL,

[SystemTypeDescription] [varchar](100) NOT NULL,

[Type] [varchar](100) NOT NULL,

[SubType] [varchar](100) NOT NULL,

[Depth] [decimal](18, 3) NOT NULL,

[Height] [decimal](18, 3) NOT NULL,

[Width] [decimal](18, 3) NOT NULL,

[MaterialCost] [decimal](18, 3) NOT NULL



Now I add some records to it like this.
declare @p1 dbo.udtt_AssemblyItem
insert into @p1 values(0,'SL 1000','Partition','',1.800,1.800,0.300,402.560)
insert into @p1 values(0,'SL 1000','Nib','150mm',0,1.800,0.150,70.840)
insert into @p1 values(0,'SL1010','Partition','',1.500,1.800,0.300,197.810)
insert into @p1 values(0,'SL1010','Nib','150mm',0,1.800,0.150,55.180)
insert into @p1 values(0,'CL 1000','Partition','',1.500,1.800,0.300,368.090)
insert into @p1 values(0,'CL 1000','Nib','150mm',0,1.800,0.150,70.840)
insert into @p1 values(0,'CB 2000','Partition','',1.500,1.800,0.

Stored Procedure Metadata failure- Dummy select not working


I have a procedure that involves creation of temp table, inserting data into it and use this data to populate another table variable whose output forms the dataset for the OLE DB Source.

Here is the general outlay of the procedure (just a sample data) -


IF 1=0


AD scavenging removed SQL's Virtual Name removed causing connectivity failure


Got into a production down scenario when AD scavenging removed the SQL Server's Virual Server Name got removed from DNS. This made the TCP connetivity failures (both windows and SQL, so this is not SSPI context issue) when the client's tried to connect using the Name. The connectivity succeeded on IP address and we have got Alias as a remedy for now.

We are asking the AD guys to add the record into Active Directory. But the AD guys are asking for an official documentation that says that there is no straight method from inside SQL to add the name to DNS (just like what the cluster setup does) and we have add it manually to DNS. Does anyone know of an "official microsoft" documentation around this?

performance in inner join with sub-select


may i expect to have diff performance (reads, duration, ...) for:

select (cols)

from table1 as t1 inner join (select col1, col2 from table2) as t2

on t2.col1 = t1.col1


select (cols)

from table1 as t1 inner join table2 as t2

on t2.col1 = t1.col1

??? (consider i have indexes in table1 and table2 on col1)

i mean... does it matter if i join full table with full table or sub-selects (which reduce the number of columns)?

SharePoint Desiger 2010 Workflow causing Gantt view failure?


I would love to know if anyone seen the following and found a workaround?

  1. Create an Issue Tracker list and a Tasks list
  2. Add a very simple workflow to the Issue Tracker list using SPD 2010 to:
    1. Create an Item in the Tasks list
    2. Update the Issue Tracker list item (e.g. to set it Issue Status value to Resolved)
  3. On the Issue Tracker list try to now view the list using a Gantt view (you will have to create one)

At this point we are seeing errors rendering the Gantt view and the ULS logs seem to indicate that the error is from some code that is trying to parse a multi-value column, and as best we can make out, the column it seems to be failing on is the Property Bag (MetaInfo) column. The peculiar thing is that the Task list's Gantt view does render (with the same type of values in the MetaInfo column).

Weird one, and I would greatly appreciate it if anyone has a solution they can share!


how do i select ' Country ' + '- '+ DISTINCT ColumnName from Table1?


Hi Frdz, how do i select 'Country' + '- '+ DISTINCT ColumnName from Table1? which doesnt work. i want result like Country - USA. it works when i do select  DISTINCT ColumnName + '-'+ 'Country' from Table1


Select Distinct with Count


This is something I can easily do in SQL, but I'm pretty new to LINQ and haven't been able to find an answer online.

I need a LINQ query that returns two values; the first a distinct value and the second the count of the first value. This is filtered by a parameter. Here is my table:















East Sussex

In SQL I use the following:

FROM My_Table

WHERE State = @State

If @State is equal to Hampshire I get the following result





I have managed to create a query that brings back a distinct City;

var recordset = (from s in MyTable where s.State == state select new { s.City,&nb

Select Distinct Using Linq

We have a list and when want to filter the list based on property to get the unique List.

AJAX GridView Checkbox Select All in ASP.Net

In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend