distinct

LINQ Select Distinct with Anonymous Types

So I have a collection of objects. The exact type isn't important. From it I want to extract all the unique pairs of a pair of particular properties, thusly: myObjectCollection.Select(item=>new { Alpha = item.propOne, Bravo = item...

Do you know of anyway to get a distinct result set without using the ResultTransformer when using SetFirstResult and SetMaxResults?

When i use SetFirstResult and SetMaxResult and if the query has joins the result have duplicate results instead of unique. Then i use all type of Distinct helpers for criteria api. But it doesnt filter the whole result set it just filters the paged result. How can i overcome this issue ? Thanks ...

Select distinct from multiple fields using sql

I have 5 columns corresponding to answers in a trivia game database - right, wrong1, wrong2, wrong3, wrong4 I want to return all possible answers without duplicates. I was hoping to accomplish this without using a temp table. Is it possible to use something similar to this?: select c1, c2, count(*) from t group by c1, c2 But this re...

NHibernate - SELECT DISTINCT equivalent for a page filter - Northwind

Lets say you are working in SQL 2005 with a copy of Northwind database installed. Your working on an ASP.NET application with an Employees "browse" page. At the top of the page you have a "Title" filter where you would like to display these 5 choices in a dropdown: [ALL] Vice President, Sales Sales Representative Sales Manager ...

MYSQL PHP: Check already made query for distinct field values?

I have a page that that lists products returned from a mysql query. The query can very greatly depending on many different things. What I want to do is give the user an option to narrow the current results by series of drop-downs. For example to narrow the product type. But to get the available product types I am currently just checking...

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

My table is: id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 3 | 10 | 03/03/2009 | john | 300 4 | 11 | 03/03/2009 | juliet | 200 6 | 12 | 03/03...

Select unique or distinct values from a list in UNIX shell script

I have a ksh script that returns a long list of values, newline separated, and I want to see only the unique/distinct values. It is possible to do this? For example, say my output is file suffixes in a directory: tar gz java gz java tar class class I want to see a list like: tar gz java class ...

Ejb-QL DISTINCT ON

Is it possible to use PostgreSQL-like DISTINCT ON in EJB-QL query? What i need to do is to fetch from db records that are distinct on 3 of 10 columns. ...

Select from multiple tables where one has distinct values

I have a simple problem here using SQL views. I just can't seem to figure it out at the moment. I have 2 tables, TableA and TableB. I want to retrieve FieldA in TableA and FieldB in TableB. The two tables are linked using an INNER JOIN. I only want rows where TableA.FieldA are distinct. The returned values should be of the top 10 items...

Rails: How do I find() all records unique in certain fields?

I have a list of 'request' objects, each of which has fairly normal activerecord qualities. The requests table is related to the games table with a join table, 'games_requests,' so that a request has a request.games array. The question is, is there a way to do a find for the last n unique requests, where uniqueness is defined by the ga...

Codeigniter: How to do a select (distinct fieldname) MySQL query

I'm trying to retrieve a count of all unique values in a field. Example SQL: SELECT count(distinct accessid) FROM (`accesslog`) WHERE record = '123' How can I do this kind of query in CodeIgniter? I know I can use $this->db->query() and write my own SQL, but I have other requirements that I want to use $this->db->where() for, and if...

Can you create a simple 'EqualityComparer<T>' using a lamba expression

IMPORTANT : THIS IS NOT A LINQ-TO-SQL QUESTION. This is LINQ to objects. Short question: Is there a simple way in LINQ to objects to get a distinct list of objects from a list based on a key property on the objects. Long question: I am trying to do a Distinct() operation on a list of objects that have a key as one of their propertie...

Is there a way to distinct more than 1 field

I need a report that has office, date and order count. I need the total count of orders per month, but only 1 order count per day. e.g. West 1/1/2009 1 order West 1/1/2009 1 order West 1/2/2009 1 order on my report I would see West 1/1/2009 1 order West 1/2/2009 1 order and my total or...

Linq to SQL: DISTINCT with Anonymous Types

Given this code: dgIPs.DataSource = from act in Master.dc.Activities where act.Session.UID == Master.u.ID select new { Address = act.Session.IP.Address, Domain = act.Session.IP.Domain, FirstAccess = act.Session.IP.FirstAccess, LastAccess = act.Session.IP.LastAccess, IsSpider = act.Session.I...

SQL: finding double entries without losing the ID

I have 1 table "Products" that looks like this: ID Product Shop Color 01 Car A Black 02 Car B Black 03 Bike C Red 04 Plane A Silver 05 Car C Black 06 Bike A Red In this example, a Product always has the same color, independent ...

Select distinct row with max date from SQL Server table?

I need to get a set of distinct records for a table along with the max date across all the duplciates. ex: Select distinct a,b,c, Max(OrderDate) as maxDate From ABC Group By a,b,c The issue is I get a record back for each different date. Ex: aaa, bbb, ccc, Jan 1 2009 aaa, bbb, ccc, Jan 28 2009 How can I limit this so I end up w...

Select average value and distinct values SQL

I have a table of locations with latitude, longitude and the U.S. state fields. I would like to select the average latitude and longitude for each state. I am trying the following code but I get a syntax error on distinct. select avg(lat), avg(lon), distinct(state) from tRealtyTrac order by state group by state ...

Duplicate rows in Oracle

How can i prevent duplicate rows being selected in a select query? I have a table with the following fields: name type user1 user2 user3 date My query requires me to select data for a particular user only at a time which is entered by the user at front end.. Say user enters 1, then the select query should retreive data for user1 onl...

How do web analytics platforms (GA for example) calculate unique visitors?

Counting distinct visitors is not an easy task. In web analytics for example, a visitor can visit on Monday and Thursday, but when counting unique visitors over that week, I'd expect to count that visitor only once. count (distinct userid) for 10M visits over a month can't run too fast as aggregations can't be applied (because count di...

How to remove column in DataSet and distinct rows to find only group names?

I have DataSet which has 3 columns. Name - insurance comp. name - treatmentDate Ali Boz SGK 12.04.09 Ali Boz SGK 14.04.09 Ali Boz SGK 16.04.09 Ali Boz SGK 18.04.09 Veli Aş AKBANK ...