Hi all, I need to create a query which groups by two columns and returns an additional column based on a condition. For example, say I've got the following columns: ProductCode | SerialNumber | Quantity | DatePurchased | CustomerID and the table contains duplicate combinations of ProductCode and SerialNumber with differing Quanitite...
I have the classic 3 table - entity, tag and entitytag - database structure. In order to find all entities tagged with certain tags I am using the following Linqtosql code: string[] myTags = {"tag1","tag2"}; var query = from m in entity where m.entitytag.Where(c => myTags.Contains(c.tag.TagName)).Count() == myTags.Count() ...
Supposing we have the following records in an SQL Server table. Date 19/5/2009 12:00:00 pm 19/5/2009 12:15:22 pm 20/5/2009 11:38:00 am What is the SQL syntax for getting something like this one? Date Count 19/5/2009 2 20/5/2009 1 ...
I'm new to Linq, what's the syntax for orderby in VB? Dim cxt As New datContext Dim qry = (From lst In cxt.zipcodes _ Select lst.state).Distinct qry = qry.OrderBy() my simple sql statement will be like this: Select distinct state from zipcodes order by State ...
Hi! I'm trying to populate a Drop down list with pharmaceutical companies, like Bayer, Medley etc. And, I'm getting theses names from DB and theses names are repeated in DB, but with different id's. I'm trying to use Linq Distinct(), but I don't want to use the equality comparer. Is there another way? My drop down list must be filled ...
I have a query where I want to get distinct dates, the phone numbers associated with those dates, and a count of the phone numbers per date. For example, I have a database with dates and phone numbers and I want the result to be 9/2005 5554446666 3 9/2005 4445556666 1 10/2005 1112223333 1 11/2005 2223334444 ...
So, i'm building a members rewarding system for my forum and got stuck on this query for selecting "forum topic with most different participants". I already have this query that counts how many posts (replies) each forum topic in last 24 hours has. SELECT poster . * , count( odgovori.id_odgovor ) AS broj, members.username FROM poster I...
I need to select 3 columns from a table, but I need each value from any column to be unique in the resultset for this column. This query: SELECT DISTINCT TOP 10 a, b, c FROM x will return 10 distinct sets. How do I do it? ...
Imagine four lists, all at least have this Id string property, but may have other properties: public class A //or B, C, D { public string Id { get; set; } //..other properties } //so: List<A> a1 = new List<A>(); List<B> a2 = new List<B>(); List<C> a3 = new List<C>(); List<D> a4 = new List<D>(); I want to select all DISTINCT...
Hi folks, I need to change the sum(decode()) expressions that are like SUM(Decode(vcon.WAGON_TYPE_CODE,'MS',1,0)) to something that counts rows with vcon.WAGON-TYPE-CODE = 'MS' but only when wag.ACI-TAG-NO is distinct. So if two columns look like this vcon.WAGON_TYPE_CODE wag.ACI_TAG_NO MS HI1111 SS ...
Given a table A of people, their native language, and other columns C3 .. C10 represented by ... Table A PERSON LANGUAGE ... bob english john english vlad russian olga russian jose spanish How do I construct a query which selects all columns of one row for each distinct language? Desired Result PERSON L...
Hi, I have the following item set from an XML: id category 5 1 5 3 5 4 5 3 5 3 I need a distinct list of these items: 5 1 5 3 5 4 How can I distinct for Category AND Id too in LINQ? ...
Preemptive apologies for the nonsensical table/column names on these queries. If you've ever worked with the DB backend of Remedy, you'll understand. I'm having a problem where a Count Distinct is returning a null value, when I suspect the actual value should be somewhere in the 20's (23, I believe). Below is a series of queries and t...
Hello I have some data like this but more than 1500000 records and more than 700 users: usercolumn , datecolumn\ a1 , 1998/2/11\ a2 , 1998/3/11\ a1 , 1998/2/15\ a4 , 1998/4/14\ a3 , 1999/1/15\ a2 , 1998/11/12\ a2 , ...
I've a table with two columns, say, column1 and column2. Column2 is not unique. For each distinct value of column2, I want a random row, only one row, from the table? i.e. my result set should have as many rows as the number of distinct values of column2. eg: column1 column2 x 1 y 2 z 1 I want the result to...
Hello Fellow Developers: I have an issue that I am sure someone out here have solved easier than what I am thinking to do. I have a list that have name and number. Name is required and could be duplicate and number could only be one, but not required. |name|number| |A |2 | |A | | |B | | |C | | |C | |...
I am having problems running a query without either truncating the note field in NotesTbl or returning repeated entries. UID is not unique for AccessTbl. When I leave out "distinct" notes will return multiple times because I am joining with AccessTbl on a non-distinct condition. When I use distict, the note field is trunctated because...
Hi, thats my code: ##### models.py ##### class SinglePoint(models.Model): attributes = models.TextField(blank=True) name = models.CharField(max_length=100) geom = models.PointField() #Kartenposition objects = models.GeoManager() class Connection(models.Model): name = models.CharField(max_length=100) #points = m...
| one | two | ------------- | A | 1 | | A | 2 | | B | 1 | | B | 3 | | C | 1 | | C | 4 | I would like to get no repeats in any column in a query, so the standard SELECT DISTINCT one,two FROM table; or SELECT * FROM table GROUP BY one,two; doesn't quite work, because it looks for distinct in all rows, which in th...
I'm trying to loop over distinct values over a dictionary list: So I have a dictionary of key value pairs . How do I get just the distinct values of string keys from the dictionary list? ...