distinct

MySQL Count values based on multiple columns

I have two tables: a 'userlist' that has a 'grade' and 'userID' columns, and a 'login' table that records each time a user is logged in. The login table has a 'userID' column that is associated with the 'userID'. This login table has a row inserted into it each time a user is logged in, so if a user logs in three times, three rows will b...

Preventing duplicate matches in RegEx

The following code string expression = "(\\{[0-9]+\\})"; RegexOptions options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline) | RegexOptions.IgnoreCase); Regex tokenParser = new Regex(expression, options); MatchCollection matches = tokenParser.Matches("The {0} is a {1} and the {2} is also a {1}"); will match and ca...

Why is Select distinct from function returning duplicates?

I tried two different variations on the same thing. The first version selects from freetexttable, the other insets into a temp table and selects from that. I've tried numerous variations on the first version (select several combinations, at both levels of scope, of group by, distinct, and casting [rank] to an integer. Regardless, the ...

Removing duplicates from a list with "priority"

Given a collection of records like this: string ID1; string ID2; string Data1; string Data2; // : string DataN Initially Data1..N are null, and can pretty much be ignored for this question. ID1 & ID2 both uniquely identify the record. All records will have an ID2; some will also have an ID1. Given an ID2, there is a (time-consuming...

Get distinct month and year with SubSonic

I have date type column (MySQL): SELECT invdate FROM invoices; invdate ------- 2009-08-22 2009-07-12 2009-08-23 ------- and I want get month and year like this: SELECT DISTINCT MONTH(invdate) AS invmonth, YEAR(invdate) AS invyear FROM invoices; how to use in C# with SubSonic (SimpleRepository)? TIA ...

Using DISTINCT in a CakePHP find function

Hello, I am writing a CakePHP 1.2 app. I have a list of people that I want the user to be able to filter on different fields. For each filterable field, I have a drop down list. Choose the filter combination, click filter, and the page shows only the records that match. In people_controller, I have this bit of code: $first_names = ...

MySQL: Rewrite MSSQL with correlated subquery in FROM clause

We have a table that contains website page views, like: time | page_id ----------|----------------------------- 1256645862| pageA 1256645889| pageB 1256647199| pageA 1256647198| pageA 1256647300| pageB 1257863235| pageA 1257863236| pageC In our production table, there is currently about 40K rows. We want to generate, for each da...

Getting distinct rows based on a certain field from a database in Django

I need to construct a query in Django, and I'm wondering if this is somehow possible (it may be really obvious but I'm missing it...). I have a normal query Model.objects.filter(x=True)[:5] which can return results like this: FirstName LastName Country Bob Jones UK Bill Thompson UK David Smi...

SQL Select Distinct Values, but order by a different value

Hi everyone, I want to select all distinct order_ids from my table, and order that list by the date column. Using DISTINCT is of course a query-wide parameter, so trying something like this doesn't work: SELECT DISTINCT(orderId, datetime) FROM table ORDER BY datetime DESC This returns all DISTINCT combinations of the orderId and da...

XSLT: select distinct but slightly different to other examples

Hi. I have the following XML: <a> <b> <d>D1 content (can include child nodes)</d> </b> <b> <c>C1 content (can include child nodes)</c> </b> <b> <e>E1 content (can include child nodes)</e> </b> <b> <c>C2 content (can include child nodes)</c> </b> </a> Using XSLT 1.0, I nee...

SQL Query between 3 tables

I have 3 tables (note this may not be the best sql db design) Room: TypeName, RoomNumber RoomType: TypeName, ... Reservation: StartDate, EndDate, TypeName, RoomNumber My input parameters are startdate and enddate. I'd like to know the distinct roomtypes available. From my understanding the solution goes like this: AvailableRoomTypes...

sql join - only select top row from 2nd table...

Bit of an sql noob, have a list in table a of customercodes/phone numbers, and table b has all the call records. I want to select the most recent call from table b for each of the customercodes/phone numbers in table a. So far I have: SELECT A.CustomerCode, A.PhoneNumber, B.StartTime FROM tableA A INNER JOIN t...

Remove dupes from recordset excluding columns from dupe condition.

I'm up against a mssql database, having a SQL query like... SELECT id, type, start, stop, one, two, three, four FROM a UNION ALL SELECT id, type, start, stop, one, two, three, four FROM b UNION ALL SELECT id, type, start, stop, one, two, three, four FROM c ORDER BY type ASC Resulting in... row | id type start stop on...

Select distinct + select top to merge multiple rows

I'm trying to select rows from a table, one row per email address, and return one firstname from the top row in the email list. The query, though, returns multiple email addresses. What am I doing wrong? SELECT DISTINCT email, (SELECT TOP 1 firstname FROM onsite_clients_archive oc WHERE oc.client_id=oca.client_id ...

Getting a distinct value across 2 union sql server tables...

I know this is a silly question, but Im a newb I'm trying to get all distinct values across 2 tables using a union... The idea is to get a count of all unique values in the columnA column without repeats so that I can get a summation of all columns that contain a unique columnA... sounds simple eh? THis is what I tried (sql server expr...

SQL Aggregate function issues

I have a table that has the following data: id status date part_no part_name 1 high 1/2/09 55 screw; 1 medium 1/2/09 55 screw; 2 high 2/2/09 32 plug; 3 low 4/8/09 59 bolt; 4 medium 5/6/09 48 tie; 4 low 5/6/09 48 tie; I want to write a query that will give me one r...

SQL: Select distinct values from 1 column

I want to select distinct values from only one column (the BoekingPlaatsId column) with this query: SELECT MAX(BoekingPlaatsId), BewonerId, Naam, VoorNaam FROM table GROUP BY BewonerId, Naam, VoorNaam How do I do that in SQL Server? ...

SQL Select Distinct with ints and varchars

I have a query which returns a number of ints and varchars. I want the result Distinct but by only one of the ints. SELECT DISTINCT t.TaskID , td.[TestSteps] FROM SOX_Task t snip Is there a simple way to do this? ...

LINQ distinct on class item?

Note this question is similar this one except I'm not working with linq-to-sql, so the "let" is not usable. Basically I have a select of the type ... .Select(c => new SampleClass { Id = c.Location.Name, Name = c.Location.Name }).Distinct().ToList() which used to work when I just had ... .Select(c => c.Location.Name).Distinct().ToLis...

Finding non distinct rows

Assuming the following structure, I need to find if there are cases where there's more than one DESC for each CP4+CP3 combination. I need only to know if they exist. Not where they are. CP4, integer CP3, integer DESC, varchar(50) ...