distinct

problem in using Distinct with Linq

Hi Everyone, Hi have an issue with Linq. I have an array of double values with duplicate entries. I want to extract only distinct values from it. I have the following code which doesn't work correctly. double[] dIds = GetIds(); //dIds has more than 10,000 items var itemIdCollection = from id in dIds.Distinct() selec...

SQL query to get latest record for all distinct items in a table

I have a table of all sales defined like: mysql> describe saledata; +-------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+-------+ | SaleDate | datetime | N...

MySQL Select distinct values across multiple tables..

I have two tables: table_1 uid | xid | name table_2 uid | elements | time | pkey I want to select multiple rows of xid, elements and time where time is the 10 smallest values and uid (or xid) is distinct. uid, xid in in table_1 is unique and the relationship between uid in table_1 and table_2 is one to many.. I tried something like...

Querying Last Entries group by DeviceId

I have the database table logs as the following: I would like to extract the last entry of device, pollDate, status. For eg. deviceId, pollDate, status 1, 2010-95-06 10:53:28, 1 3, 2010-95-06 10:26:28, 1 I tried to run the following query but the distinct only selects the f...

Nhibernate Criteria Query with Join

I am looking to do the following using an NHibernate Criteria Query I have "Product"s which has 0 to Many "Media"s A product can be associated with 1 to Many ProductCategories These use a table in the middled to create the join ProductCategories Id Title ProductsProductCategories ProductCategoryId ProductId Produ...

distinct in Xpath ?

Hi, I have this XML file, from which I'd like to count the number of users referenced in it. But they can appear in more than one category, and I'd like these duplicates not to be taken into account. In the example below, the query should return 3 and not 4. Is there a way in XPath to do so? Users are not sorted at all. <list> <group...

Filtering subsets using Linq

Hi All, Imagine a have a very long enunumeration, too big to reasonably convert to a list. Imagine also that I want to remove duplicates from the list. Lastly imagine that I know that only a small subset of the initial enumeration could possibly contain duplicates. The last point makes the problem practical. Basically I want to filter ...

SQL Server problems when using ODER BY and DISTINCT together

I got two problems, the first problem is my two COUNTS that I start with. GroupID is a string that keep products together (Name_Year together), same product but different size. If I have three reviews in tblReview and they all have the same GroupID I want to return 3. My problem is that if I have three Products with different ProductID b...

Django get_FOO_display and distinct()

I've seen answers to both halves of my question, but I can't work out how to marry the two. I have a book model, and a translatedBook model. The translatedBook has a langage set up as model choices in the usual way: LANGUAGES = ( (u'it', u'Italian'), (u'ja', u'Japanese'), (u'es', u'Spanish'), (u'zh-cn', u'Simplified Chinese'), (u'zh-...

SQL Server Select Distinct

I want to write a query like this: For a table that has these columns: ColA ColB ColC, ColD select first(ColA, ColB, ColC, ColD) distinct(ColB, ColC) from table order by ColD The query is supposed to order the table by ColD, then group the results by the combination of ColB and ColC (they may have different data types) and returns the...

Select distinct values from multiple columns

I had a table (Some_Table) with two columns: A B ------------------- 1 test 2 test 3 test1 4 test1 i would like to return DISTINCT values for column B and it's associated value in column A (first in distinct set), so something like this: A B ----------- 1 test 3 test1 What is the sql? ...

LINQ to Objects .Distinct() not pulling distinct objects

I have two ways that I am doing a fuzzy search for a customer. One is by an abbreviated name and the other is by the customer's full name. When I take these two result sets and then union them together (which I have read several places should remove distinct values) I get duplicates. Thinking that all I need to do is then call the .Disti...

Count problem in SQL when I want results from diffrent tabels

ALTER PROCEDURE GetProducts @CategoryID INT AS SELECT COUNT(tblReview.GroupID) AS ReviewCount, COUNT(tblComment.GroupID) AS CommentCount, Product.GroupID, MAX(Product.ProductID) AS ProductID, AVG(Product.Price) AS Price, MAX (Product.Year) AS Year, MAX (Product.Name) AS Name, AV...

mysql first record retrieval

While very easy to do in Perl or PHP, I cannot figure how to use mysql only to extract the first unique occurence of a record. For example, given the following table: Name Date Time Sale John 2010-09-12 10:22:22 500 Bill 2010-08-12 09:22:37 2000 John 2010-09-13 10:22:22 500 Sue 2010-09-0...

sql select statement based on distinct column values....

select distinct * from Drivers where IsDeleted=0 gives me this, I want select * based on distinct DriverMobNo column ...

how to remove repeated record's from results linq to sql

hi, i want to remove repeated record's from results but distinct don't do this for me! why??? var results = (from words in _Xplorium.Words join wordFiles in _Xplorium.WordFiles on words.WordId equals wordFiles.WordId join files in _Xplorium.Files on wordFiles.FileId equals files.File...

MySQL: Ignore a selected column when using DISTINCT

Let's say: I want to query colA, colB and colC in my table. I want to see DISTINCT values but I don't want colA to be a criteria for distinction. Omitting colA isn't an option. What's the best way to structure that query? ...

mysql conditional query - complicated!

i want to get distinct values for a field, let say: field1... ok this needs a query like: "select distint(field1) from table" however for some records, field1 is empty and there is another column that is an alternative to field1, which is field2. now; for the records where field1 is empty i need to use the value of field2. i think i nee...

Can I use a single MySQL query to select distinct rows and then non-distinct rows if a limit hasn't been reached?

I hope I'm explaining this properly, my knowledge of MySQL is quite limited. Let's say I have a table with rows that have a field called shape. I'd like to select a bunch of rows from a table, but return all of the rows with unique shape field values first. If I have less than a certain number of rows, let's say 7, then I'd like to f...

Problem with Select Distinct in CastleActiveRecord.

I want to execute follow command in ActiveRecord. select distinct(Name) from DbTemplateNw; I tried this code but it's alway return all record in table (there are many duplicate name). public static DbTemplateNw[] GetDistintTemplate() { DetachedCriteria distinctCriteria = DetachedCriteria.For(typeof(DbTemplateNw)) .SetProj...