distinct

Delete SQL looping

I want to keep only 1000 entries for each clientid. The code below does what I want but does not loop through the clientid, instead keeping 1000 total of any of the clients. Is there a way to do this in sql? I was told i need a cursor, but I am hoping not. SQL 2005 DECLARE @ids TABLE ( id int ) DECLARE @clients TABLE ( clientid varc...

How to select distinct values from datatable?

How to select distinct values from datatable in C#? For a retrieved data from database, I need to get its distinct value in C#? ...

Select top distinct results ordered by frequency

My table has two columns: id and name. The data looks like this: id | name ---------- 1 Jeff 2 Sam 3 Carl 4 Sam 5 Carl 6 Jeff 7 Dave 8 Jeff What I want to obtain is this: name | frequency ---------------- Jeff 3 Carl 2 Sam 2 Dave 1 Essentially, I need an SQL query that counts the unique ...

LINQ - writing a query with distinct and orderby

Hi, I'm quite new to LINQ. Suppose that I had the following table: Incident ID DeviceID Time Info 1 1 5/2/2009 d 2 2 5/3/2009 c 3 2 5/4/2009 b 4 1 5/5/2009 a In LINQ, how could I write a query that finds the most recent and distinct (on Device ID) set of incidents? The result I'd like i...

What is the difference between group by, distinct, Union for selecting distinct values for multiple columns?

Hi gurus, This question explained about a way of getting distinct combination of multiple columns. But I want to know the difference between the methods of DISTINCT, UNION, GROUP BY keyword method for this purpose. I am getting different results when using them. My queries are like this Query 1. select column1, column2, column3 from ta...

MYSQL SELECT DISTINCT is not distinct in the PHP, but is distinct in the database

Hiya, I have another problem with sql queries running as I expect in PHPMyadmin, but when I add the sql to my PHP script, it does not work as expected. This is the formula of my sql: select DISTINCT table1.id AS ID, table1.title AS Title, table1.startdate AS StartDate, table1.enddate AS EndDate, ...

MySQL query for ALL search terms in index

I have a table of employees and their schedule, like so: Emp_Name | Date -------- ----- Smith | 08-01-2009 Jones | 08-01-2009 Goodman | 08-02-2009 Smith | 08-02-2009 Jones | 08-02-2009 Goodman | 08-03-2009 How would I write a query so that the results were only employee names of employees working on 08-02-2009 and 08-03...

IEqualityComparer GethashCode method

Hi, My datatable contains the following columns... resultsView.Columns.Add("Fam.", typeof(string)); resultsView.Columns.Add("Component", typeof(int)); resultsView.Columns.Add("Description", typeof(string)); resultsView.Columns.Add("Quantity", typeof(double)); resultsView.Columns.Add("Unit", typeof(string)); resu...

Sql distinct selective row after a join question.

Let's say i have 2 tables Customer and Books. Table Books can have multiple rows that pertain to a row in Customer. Ex: customer John Doe has an id of 1000. Table Books has 2 rows with a member_id column with 1000 (John Doe). These 2 rows are identical EXCEPT one of the fields (book title) is empty. The other row has a value for a tit...

Get count of distinct groups in a single SQL query in Firebird 1.5?

I ran across the following in a stored procedure: for select 1 from scan_queue where ( date_time_locked is null ) and ( scan_seqno >= :varMinScanSeqno ) and ( scan_seqno <= :varMaxScanSeqno ) group by loan_id, collateral_id, insurance_id into varNotUsed do varItemsToScan = varItemsToS...

How to quickly select DISTINCT dates from a Date/Time field, SQL Server

I am wondering if there is a good-performing query to select distinct dates (ignoring times) from a table with a datetime field in SQL Server. My problem isn't getting the server to actually do this (I've seen this question already, and we had something similar already in place using DISTINCT). The problem is whether there is any trick...

Distinct pair of values SQL

Consider create table pairs ( number a, number b ) Where the data is 1,1 1,1 1,1 2,4 2,4 3,2 3,2 5,1 Etc. What query gives me the distinct values the number column b has So I can see 1,1 5,1 2,4 3,2 only I've tried select distinct ( a ) , b from pairs group by b but gives me "not a group by expression" ...

SQL Distinct Count with an exception

I'm trying to count how many distinct value of FLOOR there is but I don't want the value "B" to count towards the total. Here is my current code. It counts how many distinct floors there is but it includes the FLOOR "B" when there is one. SELECT COUNT(DISTINCT FLOOR) as NB_FLOORS FROM TABLE_ID The table looks something like this : ...

Using DISTINCT in JPA

What column does DISTINCT work with in JPA and is it possible to change it? Here's an example JPA query using DISTINCT: select DISTINCT c from Customer c Which doesn't make a lot of sense - what column is the distinct based on? Is it specified on the Entity as an annotation because I couldn't find one? I would like to specify the co...

SQL: count number of distinct values in every column

I need a query that will return a table where each column is the count of distinct values in the columns of another table. I know how to count the distinct values in one column: select count(distinct columnA) from table1; I suppose that I could just make this a really long select clause: select count(distinct columnA), count(distinct...

Select Count Distinct

I would like to count the number of installations of each Member in a table similar to this. But this count distinct drives me nuts... MemberID | InstallDate 1 | Yesterday 2 | Today 1 | Today 3 | Today The above table should produce something like this one.. MemberID | CountNumberOfInstallations 1 | 2 2 | 1 3 | 1 p.s. I know...

How to map a group entity class from a "group name" column in NHibernate?

I have a table in my database looking roughly like this: create table Foo ( Id int identity not null, Name varchar(100) not null, GroupName varchar(100) not null, constraint PK_Foo primary key (Id) ) Now I want to map this table into two entity classes like this: class Foo ...

What indexes can be used to improve this query?

This query selects all the unique visitor sessions in a certain date range: select distinct(accessid) from accesslog where date > '2009-09-01' I have indexes on the following fields: accessid date some other fields Here's what explain looks like: mysql> explain select distinct(accessid) from accesslog where date > '2009-09-01'; ...

PHP MYSQL multiple or single DISTINCT/UNIQUEvalues

Currently I have this query: SELECT column1,column2 FROM table column1 needs to be distinct, column2 does not. SELECT DISTINCT column1, NON-DISTINCT column2 FROM table Now I know that doesn't make sense but I need column1 to be distinct and column2 to be anything. How would I do that. ...

MS ACCESS: How can i count distinct value using access query?

here is the current complex query given below. SELECT DISTINCT Evaluation.ETCode, Training.TTitle, Training.Tcomponent, Training.TImpliment_Partner, Training.TVenue, Training.TStartDate, Training.TEndDate, Evaluation.EDate, Answer.QCode, Answer.Answer, Count(Answer.Answer) AS [Count], Questions.SL, Questions.Question FROM ((Evaluation I...