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

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

select distinct best row based on a condition

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
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.

View Complete Post

More Related Resource Links

On New Item form, select item on choice dropdown based on query result.


I posted this on Experts Exchange awhile back with no answer, so here's hoping someone here has an idea :)

So I've mostly figured this problem out, but I need some help in getting over the hump... note that I'm using JavaScript to do this (SPServices) on SharePoint 07'.

I'm on the New Items page of a list. Based on a user's selection in a "Teacher" dropdown, I'm doing the following:

1.) Get the value of the Teacher field and several other fields (School and Visit Date)
2.) Run a CAML query based on those fields to find the last list item entered for that Teacher

So here's where I'm stuck...

I have 10 choice fields, all with choices 0, 4, 7, and 10. Depending on what they were set to in the last entered item (which I queried), I want to set the selected item on each of those dropdowns to equal that. So "Score 1" was set to "4" on the la

condition Select query

Hi All.Greetings.I have 1 table.Table 1ID | Name  | Sex       |  DateofBirth1   | abc    | Female  | 19802   |abc2   | Female  |20083   |abc3   |Male       |19854   |abc4   |Male      | 2009I want to see if age is greater then 3 year it will adult girl or adult boy. and If age is less then 3 year then it will be child girl or child boy.I have Output like thenName | Sex         |abc    | Adult girl |abc2  | child girl  |abc4  | child boy | etc.....How to write condition query to get my result.Thanks in Advance.

Execute SSIS Package PART based on Some Condition

Hello, Is it possible to execute part of the Package based some condition? (Like If var1 = true, then execute this block). If Yes, what control flow I should use. Please advse. I will have a Package Variable and if that variable is True then some part should execute. REst all should execute every time the package is called. Like if I will have "2 Sequence Container" in my control flow, the first one should execute every time, but 2nd one only if the variable value = True. Thanks, Prabhat

Receive/Delete Multiple FTP Files based on condition using SCRIPT(VB) TASK

I am trying to receive and delete multiple FTP files from a remote FTP server using Script task Below is the code   Dim FTPConnMgr As ConnectionManager FTPConnMgr = Dts.Connections("FTP Connection Manager") Dim FTPClientConn As FtpClientConnection = New FtpClientConnection(FTPConnMgr.AcquireConnection(Nothing)) Dim FileTimeStampNew As String = "20100913021807" Dim remoteFileNames(0) As String remoteFileNames(0) = Dts.Variables("FtpRemoteDirectory").Value & "*" & FileTimeStampNew & ".*" 'Below hardcoded FileName works good. But the problem is there are lots of file in the FTP folder that I dont want to receive 'remoteFileNames(0) = Dts.Variables("FtpRemoteDirectory").Value & "Company_alpha_Full_20100913021807.xml" Dim localPath As String = Dts.Variables("FtpLocalDirectory").Value FTPClientConn.Connect() FTPClientConn.ReceiveFiles(remoteFileNames, localPath, False, False) FTPClientConn.Close() Dts.TaskResult = ScriptResults.Success End Sub End Class If i specifically mention the RemotefileNames this works fine but, when I say *.* it executes succefully but doesn't receives the file. Please advice how to receive multiple file based on a variable BR, AWM

Select nodes based on element innertext

This should be easy...hoping someone could have a look & see what wrong with my code. I'm simply trying to loop through an xml document & grab multiple nodes if a specific element contains specific text. So if my xml looks like this: <Settings><Document>        <FriendlyName>The Big One</FriendlyName>        <ID>8ae3428b-f1f2-4b94-a65c-2c8e24f660c8</ID>        <Post>True</Post>        <Department>Collections</Department>        <Category>Forms</Category>        <Audience></Audience>        <Link Type="Page" Target=""><![CDATA[]]></Link>        <Keywords></Keywords>    </Document></Settings> So my requirement is: For each element, If FriendlyName contains "Big", get the inntertext of FriendlyName, Department, Category, Link and pipe them together like this: "The Big One | Collections | Forms | Link"   It looks like I'm having problems with the If statement, if I remove it I'll get every

Is it possible to not allow a listviewitem to not be selectable based on a state condition?

I have a listview that is binded to a collection. The listview contain items with different states and would like to know how to not allow an item with a specific state to not be selectable. Does anyone know how I can achieve that? I do know that I can add some kind of datatrigger, multitrigger in xaml but just don't know how to implement it. Any help is appreciated. Thanks in advance.

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

Creating a workflow condition based on document type

Hello! I'm creating a workflow for a Document Library. I only want the action in the workflow to be executed on the condition that the document is of a certain document type. Any suggestions how I should do that? Thanks!

How can we insert csv file data into Sql server table based on some condition ?


Hello All experts there,


I need to insert some csv file data into sql server 2008. but there are multiple tables created in sql server and I need to check some conditions before data insertion and based on the condition value, sql script should insert the data in to specific table. in other words I need to insert data from one csv file to different tables based on some condition.

is it possible? if yes then can anyone please give me an idea on how can we do that?


Thanks in advance


Nipendra Garg

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 ?

select record based on / match selected date

with the sql statement below, i try to select the records based on the selected date, it return no rows from adapter filled datatable. 
however, when i run the query in sql query in sql server studio management, it returns records. 
date format = 09/28/2010

string[] splitDT = txtSelectedDate.Text.Split('/');

            string strRearrangeDT = splitDT[0] + "/" + splitDT[1] + "/" + splitDT[2];

using (SqlConnection sqlconn = new SqlConnection(connstring))
                DataTable dt = new DataTable();
                string strSQL = "SELECT userclaim_details3.id, userclaim_details3.userclaimid, userclaim_details3.userid, userclaim_details3.childcategorytableid, userclaim_details3.statusid, " +
                         "userclaim_details3.amount AS amountname, userclaim_details3.remark AS remarkname, userclaim_details3.datecreated AS datecreatedname, " +
                         "userclaim_details3.datemodified, approvaltable.id AS Expr1, approvaltable.typename AS statusname, usertable2.id AS Expr2, usertable2.name AS uname, " +
                         "childcategorytable.id AS Expr3, childcategorytable.categor

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.

How to create a condition and policy to deny access based on host name



With SQL Server 2008 Enterprise, can I prevent an Active Directory Account Login ( domain\user ) from connecting if its coming from a host_name?

What I really need to do is this:  


On SQL1, login mydomain\user1 is only allowed to login from servers APP2, APP3, and APP4.


If mydomain\user1, though it has a login on SQL1 attempts to connection any other hostname besides APP2, APP3 or APP4 I want to deny it access to SQL1.


Can anyone point me in the right direction for the correct condition(s) for a policy that would work?



Erik A. Grob, MCITP, MCDBA

UPDATE query based on the SELECT query



Can you please advise how can i achive following UPDATE query based on the SELECT query. You can see my comments in Green

UPDATE [Table_POforecast]
   SET [ForecastAmount] = [ForecastAmount]* Coeff --- Coeff value to be from SELECT Statement
 WHERE [PO_No] = [PO_No] --- PO_No to match from SELECT statement

--- SELECT Statement
SELECT     PO_No, (NewBalance / TotalForecastAmount) AS Coeff
FROM       View_Forecast
WHERE     (OutstandingForecast < 0)



SELECT DISTINCT and INNER JOIN causing failure


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.

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