Are you trying to be distinct by more than one field? If so, just use an anonymous type and the Distinct operator and it should be okay: var query = doc.Elements(“whatever”) .Select(element => new { id = (int) element.Attribute(“id”), category = (int) element.Attribute(“cat”) }) .Distinct(); If you’re trying to get a distinct set of values … Read more
How about something like: SELECT mt.* FROM MyTable mt INNER JOIN ( SELECT id, MIN(record_date) AS MinDate FROM MyTable GROUP BY id ) t ON mt.id = t.id AND mt.record_date = t.MinDate This gets the minimum date per ID, and then gets the values based on those values. The only time you would have duplicates … Read more
SELECT name,COUNT(*) as count FROM tablename GROUP BY name ORDER BY count DESC;
MusiGenesis‘ response is functionally the correct one with regard to your question as stated; the SQL Server is smart enough to realize that if you are using “Group By” and not using any aggregate functions, then what you actually mean is “Distinct” – and therefore it generates an execution plan as if you’d simply used … Read more
You’re looking for a group by: select * from table group by field1 Which can occasionally be written with a distinct on statement: select distinct on field1 * from table On most platforms, however, neither of the above will work because the behavior on the other columns is unspecified. (The first works in MySQL, if … Read more
;with cteRowNumber as ( select COMPUTERNAME, SERIALNUMBER, USERNAME, LASTIP, LASTUPDATE, SOURCE, row_number() over(partition by COMPUTERNAME order by LASTUPDATE desc) as RowNum from YourTable ) select COMPUTERNAME, SERIALNUMBER, USERNAME, LASTIP, LASTUPDATE, SOURCE from cteRowNumber where RowNum = 1
SELECT DISTINCT a,b,c FROM t is roughly equivalent to: SELECT a,b,c FROM t GROUP BY a,b,c It’s a good idea to get used to the GROUP BY syntax, as it’s more powerful. For your query, I’d do it like this: UPDATE sales SET status=”ACTIVE” WHERE id IN ( SELECT id FROM sales S INNER JOIN … Read more
What if I want to obtain a distinct list based on one or more properties? Simple! You want to group them and pick a winner out of the group. List<Person> distinctPeople = allPeople .GroupBy(p => p.PersonId) .Select(g => g.First()) .ToList(); If you want to define groups on multiple properties, here’s how: List<Person> distinctPeople = allPeople … Read more
You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields: SELECT tt.* FROM topten tt INNER JOIN (SELECT home, MAX(datetime) AS MaxDateTime FROM topten GROUP BY home) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime
So your problem, freely translated is: I have a sequential, numeric array containing values from 1 through 30. I want to select 6 unique values from this array from random positions. The accepted solution to this is to properly (e.g. Fisher-Yates) shuffle the array and simply take the number of required elements from the front. … Read more