.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

Distinct or Max - T-SQL QUERY

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

Hi Guys,

I need distinct or max of below two columns. I am using distinct on both columns but still I result are two instead of one. 


FileName                               FilePath

SALES_082753_20100324     D:\SQL01\LandingFiles\SALES_082753_20100324_2010427.csv

SALES_082753_20100324     D:\SQL01\LandingFiles\SALES_082753_20100324_2010427.csv

SALES_082753_20100323     D:\SQL01\LandingFiles\SALES_082753_20100323_2010427.csv

SALES_082753_20100323     D:\SQL01\LandingFiles\PluSALES_082753_20100323_2010427.csv

SALES_082753_20100322     D:\SQL01\LandingFiles\SALES_082753_20100322_2010427.csv

SALES_082753_20100322     D:\SQL01\LandingFiles\SALES_082753_20100322_2010427.csv

SALES_082753_20100317     D:\SQL01\LandingFiles\SALES_082753_20100317_2010427.csv

SALES_082753_20100325     D:\SQL01\LandingFiles\SALES_082753_20100325_2010427.csv

SALES_082753_20100325     D:\SQL01\LandingFiles\SALES_082753_20100325_2010427.csv


Out-Put Required

FileName                       &nbs

View Complete Post

More Related Resource Links

GridView Paging + Unsupported overload used for query operator 'Distinct'


The issue is two fold in my ASP.NET Web Application. I need to use a custom Distinct() via LINQ to SQL, and then assign thevar as a DataSource to a GridView. If I use the custom Distinct that implements IEqualityComparer, I get "Unsupported overload used for query operator 'Distinct'" exception. but if i somehow get past this, i get "The data source does not support server-side data paging" exception on GridView.DataBind(). here is the code:

distinct_employees =
    (from emp in wsq.LIT_Active_Employees
    where (String.IsNullOrEmpty(ssFirstName.Text) ? (emp.FIRST_NAME == null || emp.FIRST_NAME.StartsWith(String.Empty)) : emp.FIRST_NAME.Contains(ssFirstName.Text)) &&
            (String.IsNullOrEmpty(ssLastName.Text) ? (emp.LAST_NAME == null || emp.LAST_NAME.StartsWith(String.Empty)) : emp.LAST_NAME.Contains(ssLastName.Text)) &&a

Please help me to find out distinct value from linq to sql query given.

profileuserDataContext db = new profileuserDataContext(); var Q = (from p in db.edufinals join RM in db.edufinals on p.UserName equals RM.UserName where p.name.StartsWith(srcval) select new { p.name, RM.ProfielID, RM.ProfielPic, RM.city }).OrderBy(x => x.name).Distinct().Take(10);//, p.Key.ProfielID, p.Key.ProfielPic, p.Key.city, p.Key.country foreach (var pg in Q.Distinct()) { Response.Write(pg.name); ..................................... .................................... } how to refine this query to get distinct "name" column? Please help me to find out exact value.

Trying to build query using DISTINCT or GROUP BY...beginner here

Hi all, I have a table with the following format: instanceID    timeStamp  stepID 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:33.807 2 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:34.807 3 ... EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:26.773 2 so I need to build a query which will return 1 instanceID and all its stepIDs in one row. So the results would have to be something like this: instanceID    timeStamp  StepIDs 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1,2,3 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1,2 and if possible I would like to specify something like...bring me the data where 'timeStamp' > 2010-09-07 20:35 ps: I tried using DISCTINCT and GROUP BY but could not reach the desired results. Thank you!JCD

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

Query to return conditional counts both distinct and non distinct.


I have the following table structure:

Table EmailTracking



Table EmailTrackingDetail


The following query returns the default recordset:


SELECT     TOP (200) EmailTracking.EmailAddress, EmailTrackingDetail.TranType, EmailTrackingDetail.TranDate
FROM         EmailTracking INNER JOIN
                      EmailTrackingDetail ON EmailTracking.RecordID = EmailTrackingDetail.EmailTrackingRecordID







2010-09-21 16:59:29.980



2010-09-21 17:19:58.790



2010-09-27 12:00:30.940

Distinct query generated to populate a dimension

When processing a dimension attribute, what elements qualify as necessary for the auto generated distinct count in SQL?    I am assuming is based on the attribute relationships, but how can I control what goes into the distinct count?  (we have an issue in which we only want a distinct could to include certain attributes and not others)

Javier Guillen

Linq Query Returning Distinct


This is my query 

    Dim s_Person = From Person In db.People Join ProspectStatus In db.ProspectStatus

                       On Person.Id Equals ProspectStatus.Prospect_ID

                       Where (Person.Organization_ID = s_Organization_ID)

                       Select Person

I only want to return DISTINCT values of Person.ID.  Right now, it returns the same person multiple times because there are several entries in the status table.

Any ideas?

    Dim s_Person = From Person In db.People Join ProspectStatus In db.ProspectStatus
                       On Person.Id Equals ProspectStatus.Prospect_ID

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

sql query that don't run



I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query


I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:

Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5

How to send an email with information in query string


First I will say I googled (Binged to make Bill happy) for this but could not find anything, perhaps I am not stating what I need correctly;

I have a website that a customer creates orders (for a printing company) and after the order, I would like to email the customer a link to view the status of their order;

I need to send the CustomerID in the link so on the returning page if a CustomerID is detected in the querystring, I "auto login" the client and open the "Dashboard" for the order page for that customer.

Can anyone point me in the right direction as to how to send the link and how to parse the querystring to get the values from the link;

Thank you if you can point me in the right direction.

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Get ServerReport selected Parameter value to use in vb query


I'm using ReportViewer in Asp.net 2.0 to view a SSRS report. I need the value of the selected parameter to use in a vb query. The parameters are populated on the server and its a single selection. Doing searches I've come across  ReportViewer1.ServerReport.GetParameters() but I can't figure out if I can use this to determine which value the user has selected in the parameter dropdownlist. Any help would be appreciated.

Need Help in LINQ query for group By with chunks of record


I am assigning and unique id [strShipperIdSequence] on my List on bases of some properties which are grouped together uniquely.
Now what i needed is that my group should be further break down to some maximum amount of chunks.[Let say 10]
that mean's even i am having Same value in 12 records i should get 2 groups[I of 10 items and other of 2 items]

var uniqueGroups = objMdbContentInfoList.GroupBy(p => new
}).Select(g => g.First()).ToList();
foreach (var objUnique in uniqueGroups)
string strShipperIdSequence = APIGlobalMethods.GetShipperRequestID();
foreach (MdbContentInfo obj in objMdbContentInfoList.FindAll(h => (h.CON_ENTRY_POINT == objUnique.CON_ENTRY_POINT &&
h.APPTType == objUnique.APPTType &&

Query an arraylist of array list using linq


I have an array list called Customer. I t holds a list of array list which has the account details.

proj.customer -> collection of arraylist of type customer

proj.customer.account -> collection of arraylist of type account

Account array list holds type account and it has a poperty account balance. How can I query all accounts which has account balance  >100

I tried the following one

var Accountlist= from Account accountdetails in proj.customer.account

where accountid.balance >100

select accountdetails.id

But it is always throwing an error saying cannot cast type customer to type account

Please help

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