The following expression will select only distinct companies and return the first occurence with its id. partnerService.SelectPartners().GroupBy(p => p.Name).Select(g => g.First());
Link to Working Examples Solution 0 This can be accompished using pivot tables. Solution 1 Use the unique formula to get all the distinct values. Then use countif to get the count of each value. See the working example link at the top to see exactly how this is implemented. Unique Values Count =UNIQUE(A3:A8) =COUNTIF(A3:A8;B3) … Read more
Here’s one option using a subquery with DISTINCT: SELECT COUNT(*) gender_count, SUM(IF(gender=”male”,1,0)) male_count, SUM(IF(gender=”female”,1,0)) female_count FROM ( SELECT DISTINCT tel, gender FROM example_dataset ) t SQL Fiddle Demo This will also work if you don’t want to use a subquery: SELECT COUNT(DISTINCT tel) gender_count, COUNT(DISTINCT CASE WHEN gender=”male” THEN tel END) male_count, COUNT(DISTINCT CASE WHEN … Read more
Update 1 Better you use this against above. SELECT id, col2, col3, col4 FROM yourtable GROUP BY col2, col3; Demo The reason I am saying is because using CONCAT, I am not getting desired result in this case. First query is returning me 5 rows however CONCAT is returning me 4 rows which is INCORRECT. … Read more
Using a subquery, you can get the highest id for each name, then select the rest of the rows based on that: SELECT * FROM table WHERE id IN ( SELECT MAX(id) FROM table GROUP BY name ) If you’d prefer, use MIN(id) to get the first record for each name instead of the last. … Read more
I notice this question gets quite a lot of views so I’ll first address a question you didn’t ask! Regarding the title. To achieve a “Sql Union All with “distinct”” then simply replace UNION ALL with UNION. This has the effect of removing duplicates. For your specific question, given the clarification “The first query should … Read more
You can do nice hack when querying contentResolver, use: String selection = Models.SOMETHING + “=” + something + “) GROUP BY (” + Models.TYPE;
What you need is the DataFrame aggregation function countDistinct: import sqlContext.implicits._ import org.apache.spark.sql.functions._ case class Log(page: String, visitor: String) val logs = data.map(p => Log(p._1,p._2)) .toDF() val result = logs.select(“page”,”visitor”) .groupBy(‘page) .agg(‘page, countDistinct(‘visitor)) result.foreach(println)
Use DISTINCT to remove duplicate GROUPING SETS from the GROUP BY clause In a completely silly example using GROUPING SETS() in general (or the special grouping sets ROLLUP() or CUBE() in particular), you could use DISTINCT in order to remove the duplicate values produced by the grouping sets again: SELECT DISTINCT actors FROM (VALUES(‘a’), (‘a’), … Read more
GROUP BY maps groups of rows to one row, per distinct value in specific columns, which don’t even necessarily have to be in the select-list. SELECT b, c, d FROM table1 GROUP BY a; This query is legal SQL (correction: only in MySQL; actually it’s not standard SQL and not supported by other brands). MySQL … Read more