w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
   » Home » Distinct Query
mongo query for equivalent SQL query ( GROUPBY COUNT DISTINCT )
You can do this with aggregation framework. But should we assume that your documents are flat - i.e. {A:x, B:y, C:z} or something like that? db.collection.aggregate( {$match:{C:z}}, {$group:{_id:"$A", Bs :{$addToSet:"$B"} }, {$unwind:"$Bs"}, {$group:{_id:"$_id", distinctBcount: {$sum:1} } );

Categories : Mongodb

Query With Distinct Value
SELECT t1.cust_number, t2.bu, t2.dist_channel FROM table1 t1, table2 t2 WHERE t1.cust_number = t2.cust_number AND t1.sale_org = t2.sales_org AND -- your actual criteria go here

Categories : SQL

Query to get distinct values
try this select * from `order` where c1 not in (select c1 from `order` where c2 = 20) group by c1 **NOTE : that order is mysql reserved keyword , so you should escape it by backticks DEMO HERE C1 C2 2 80 3 80 5 80

Categories : Mysql

Distinct query not working
Sounds like logically you really don't want to do the DISTINCT since you are just looking to order by longest posts. Don't you really just want to get the longest posts and sometimes the same user will be in the list multiple times? SELECT Userid, PostID FROM table t ORDER BY `Length` desc LIMIT 10 The reason your distinct isn't working is because you are also returning PostID. If you remove PostID then you will get a distinct UserID. To do what I think you want you will need to do a subquery as mentioned here.

Categories : Mysql

Distinct Clause in an SQL Query
You need a slightly more complicated query: select mgt.* from MediaGenT mgt join (select mg_medianum, max(mg_createdate) as maxcd from MediaGenT group by mg_medianum ) msum on mgt.mg_medianum = msum.mg_medianum and mgt.mg_createdate = msum.maxcd; That is, calculate the most recent date for each media num, and then choose that record. This is standard SQL that should work in any database. EDIT: The above query is calculating the maximum date for each media number and calling it msum.maxcd. The query uses this query to select only the maximum date for each media number (via the join). The mgt.* expression is choosing all the columns from the MediaGenT table.

Categories : SQL

regarding distinct values in sql query
Try with GROUP BY like SELECT Id, Code, newCode, Course, Internship FROM courses WHERE Year ='c' AND (Internship ='IS' OR Internship = '') GROUP BY Code ORDER BY newCode ASC

Categories : PHP

SQL Query Get Rows Where Xml All Distinct
Below is sample code that outputs the raw details in the first query and the distinct values in the second query. The first query will help you validate the results of the second query. The following is a link to a SQL Fiddle for this solution. CREATE TABLE #Temp (XmlString XML); INSERT INTO #Temp (XmlString) VALUES ('<Names> <Name Language="English"> <Value></Value> <FirstName>Hello Goodbye</FirstName> <MiddleName /> <LastName /> <IsPrimary>True</IsPrimary> </Name> <Name Language="English"> <Value></Value> <FirstName>Hello</FirstName> <MiddleName /> <LastName>Goodbye</LastName&g

Categories : SQL

distinct not working in sql query
So your trying to get the last 6 messages of a person if I'm right? SELECT Message.mobileNo, Contact.mobileNo, Contact.workNo, Contact.homeNo, Contact.other, Contact.name, Message.body, Message.idTextMessage FROM cakephp_db.textmessage AS Message LEFT JOIN cakephp_db.contacts AS Contact ON Message.user_id = Contact.user_id AND Message.mobileNo IN (Contact.mobileNo, Contact.workNo, Contact.homeNo, Contact.other) WHERE Message.User_id = 23 GROUP BY Message.mobileNo ORDER BY Message.idTextMessage DESC LIMIT 6

Categories : PHP

Cypher query DISTINCT
Something like this might work, but if you have a lot per category it won't be the best efficiency: START question=node:__types__(className = "com.socialist.server.graph.entities.Question") RETURN question.category, head(collect(question)) LIMIT 5 Also, soon (hopefully by 2.0 release) there will be a good way to get a random item out of a collection, something like this: START question=node:__types__(className = "com.socialist.server.graph.entities.Question") WITH question.category as category, collect(question) as questions RETURN category, questions[rand() * length(questions)] LIMIT 5

Categories : Neo4j

MySQL - query - Mix between DISTINCT and GROUP BY
maybe something like this (you might need to check the date format string) SELECT DATE_FORMAT(timestamp,'%m/%d/%Y %H') timestamp, COUNT(id) FROM tblTemp GROUP BY DATE_FORMAT(timestamp,'%m/%d/%Y %H')

Categories : Mysql

MS SQL query to select distinct product_id
Are you sure you want distinct ? Try this: SELECT fp.fcast_product_id, fp.id, fp.customer_id, pm.product_name FROM f_product AS fp LEFT JOIN product_master AS pm ON fp.customer_id=pm.customer_id and pm.product_id =fcast_product_id WHERE fp.customer_id = '10004' B.t.w. there is no id 2933 in your example

Categories : Sql Server

MySQL Distinct Query Error
SELECT DISTINCT(isbn) AS isbn, price,qty, date,total FROM reserved_dummy The syntax for the SQL DISTINCT clause is: SELECT DISTINCT columns FROM tables Don't use . (dot) operator when aliasing

Categories : PHP

How can I use Distinct operator in C# LINQ query
I want to select unique or distinct EventYear Isn't it just: var eventyears = storeDB.EventMasters .Select(c => c.EventYear) .Distinct() .ToList();

Categories : C#

Can't get distinct result from mysql query?
try with join SELECT distinct(b.`id`),b.`from_name`,b.`posted_on`,b.`subject`, a.`to` FROM `message` as b inner join `message_recepient` as a on (b.`id` = a.`msg_id`) WHERE b.`id` in (15063,15106,15075,15170) or , SELECT b.`id`,b.`from_name`,b.`posted_on`,b.`subject`, a.`to` FROM `message` as b inner join `message_recepient` as a on (b.`id` = a.`msg_id`) WHERE b.`id` in (15063,15106,15075,15170) group by id

Categories : Mysql

selectlist distinct upper mix in query
In you Linq call Distinct like this: Distinct(StringComparer.InvariantCultureIgnoreCase) Instead of using a standard string compare this will use one that ignores the case of the text.

Categories : Asp Net Mvc

Mongoid - limit on distinct query
distinct in MongoDB is a command and commands don't return cursors. Only cursors support limit() whereas command results don't, just like sort() isn't supported here, and I think sort is important enough to run first, otherwise you never know which "first two" distinct items you get There is a way around this, but using the aggregation framework. In plain MongoDB query speak (as you use on the MongoDB shell), you'd use: db.places.aggregate( [ { $match: { 'tags' : { $in: [ 'food' ] } } }, { $group: { '_id': '$name' } }, { $sort: { 'name': 1 } }, { $limit: 2 }, ] ); In Mongoid I suspect you can change the first line to: Place.collection.aggregate( [

Categories : Mongodb

how to query for distinct year in django
When the model stores the models.DateTimeField it's normally in this format "YYYY-MM-DDTHH:MM:SS" and you can use the filter method to query all the the specific records with that year. Here's an example: models.py from django.db import models class Items(models.Model): name = models.CharField(max_length=100) creation_date = models.DateTimeField(auto_now_add=True) views.py from Items.model import Items def items_by_creationdate_view(request): items = Items.objects.filter(creation_date__year=2013) return render("template.html", {"items": items }) template.html {% for x in items %} {{x.name}}<br/> {{x.creation_date }}<br/><br/> {% endfor %}

Categories : Django

Select TOP 20 Distinct Rows in Query
SELECT Users_ID, Users_Transaction, Users_Tran_ID FROM ( SELECT DISTINCT TOP (20) Users_ID, Users_Transaction, Users_Tran_ID, Users_LogDate FROM App_UserLog WHERE (Users_ID = 'ADMIN') AND (Users_Transaction LIKE 'Permit Viewed%') ORDER BY App_UserLog.Users_LogDate DESC ) as a

Categories : SQL

SQL Query Distinct Inner Join Issue
Possibly: select * from (SELECT DISTINCT Name from Names WHERE Haircolor='Red') uniquenames INNER JOIN (SELECT * FROM Names) allnames ON uniquenames.Name = allnames.Name - it depends on what you actually want to see. UPDATE: SQLFiddle for original answer here. However, a better answer (if using MySQL) might be: select * from Names WHERE Haircolor='Red' group by Name (Note that the specific row returned for each Name matching the hair colour is essentially random.) SQLFiddle here. Further Update: For SQLServer, try: with cte as (select n.*, row_number() over (partition by name order by address) r from Names n WHERE Haircolor='Red') select NAME, HAIRCOLOR, ADDRESS, PHONE, EMAIL from cte where r=1 - this will return the first row (by address, alphabetically) for each name - SQL

Categories : SQL

select distinct a.* from table query
If table tbl_applicant exists No, it's only eliminate dupliacte rows. select col1,col2 ... from tbl_applicant group by col1,col2 ... or with alias a select a.col1, a.col2 ... from tbl_applicant a group by a.col1,a.col2 ...

Categories : SQL

Getting distinct values from linq to xml query
I would try something like this var uPlays = playerData.Root.Elements("Player") .GroupBy(c => c.Element("Rank").ToString()) .Select(g => g.Last()); or using your code var uPlays = playerData.Descendants("Player") .Descendants("Rank") .GroupBy(z => z.ToString()) .Select(b => b.Last().Parent);

Categories : Xml

find all using distinct query in Cakephp
i have to use distinct first not after 1st column.. dont know why but it works $this->find('all',array( 'fields'=>array('DISTINCT mobileNo','dateTime'), 'order'=>'Message.idTextMessage DESC', 'conditions' => array('Message.User_id' => $userid)));

Categories : SQL

Query with multiple distinct in a column
You should just use conditional summation: select count(*), sum(case when text = '5' then 1 else 0 end) as Num_5, sum(case when text = '10' then 1 else 0 end) as Num_10, sum(case when text is null then 1 else 0 end) as Num_Null from t_test; This is assuming that a field called text is stored as a character string, so the constants are put in quotes. If it is really a number, first I'd be curious why it is called text. In that case, you can dispense with the single quotes. In your case, the last one doesn't work because count(text) counts non-null values. But the where clause only keeps NULL values. For that one, you should use count(*). The correct query would be: select count(*) from t_test where text is null;

Categories : Dotnet

Query to get count of distinct value in MySQL
select count(distinct column_whose_distinct_values_you_want_to_count) from rbt_activation_details_log where RADL_ACTIVE ='A' group by RADL_REQUEST_MSISDN You're grouping and counting on the same column, so this will always give you 1 as result. EDIT: Then simply omit the group by clause select count(distinct RADL_REQUEST_MSISDN) from rbt_activation_details_log where RADL_ACTIVE ='A'

Categories : Mysql

How to implement SQL query, max(count(distinct attribute_name))
Try SELECT * FROM ( SELECT book_publisher, COUNT(DISTINCT book_author_id) author_count FROM table1 GROUP BY book_publisher ORDER BY author_count DESC ) WHERE rownum = 1 or WITH cte AS ( SELECT book_publisher, COUNT(DISTINCT book_author_id) author_count FROM table1 GROUP BY book_publisher ) SELECT book_publisher FROM cte WHERE author_count = ( SELECT MAX(author_count) FROM cte ) or with analytical functions SELECT book_publisher, author_count FROM ( SELECT book_publisher, COUNT(DISTINCT book_author_id) author_count, DENSE_RANK() OVER (ORDER BY COUNT(DISTINCT book_author_id) DESC) rank FROM table1 GROUP BY book_publisher ) WHERE rank = 1 Here is SQLFiddle demo

Categories : SQL

linq to entities C# Group By / Distinct query
You can use GroupBy and the select the first item in that group to get the effect of a DistinctBy. let mediaCount = g.Where(x=>x.submedia != null) .GroupBy(x=>x.submedia.ID) .Sum(mediaGroup => mediaGroup.FirstOrDefault().submedia.MediaSize)

Categories : C#

DISTINCT is not working in join query Cakephp
DISTINCT in SQL Server applies to the entire row you are returning from your result set. So if your data contains, for example: mobileNo workNo homeNo name ... 1234 1234 5678 Joe ... 1234 867 5309 Stacy ... 1 555 5555 Sentinel ... Your DISTINCT will not remove Joe or Stacy, because the entire rows are not equal. On the other hand, if your data contained: mobileNo workNo homeNo name ... 1234 1234 5678 Joe ... 1234 1234 5678 Joe ... 1234 1234 5678 Joe ... 1234 1234 5678 Joe ... 1234 1234 5678 Joe ... 1234 867 5309 Stacy ... 1 555 5555 Sentinel ... Your query would return only a single entry with the name "Joe", because it would return only

Categories : Cakephp

Count number of Distinct days in query
You can CAST as date SELECT COUNT(DISTINCT CAST(StartDate AS DATE)) FROM Stats WHERE StartDate >= '20130101' AND StartDate < '20140101' Also use an unambiguous date format such as yyyymmdd and >= < not BETWEEN. Your current query would include the 31st December if there was a row with exactly the value 20131231 00:00:00 but not any with different times on that date. I doubt that is intentional.

Categories : Sql Server

mysql query group by where column distinct
It should look something like this SELECT COUNT(DISTINCT e.IP), count(e.id), count(c.id), e.Nid FROM tbl1 e LEFT JOIN tbl2 c ON e.Uid = c.Uid where e.at > '".$from."' AND e.at < '".$to."' GROUP BY e.Nid

Categories : Mysql

How can I get DISTINCT/GROUP BY behavior in UPDATE query
If the data is truly identical, you can simply use "min()" without group by/distinct UPDATE product_stock AS S LEFT JOIN product_purchase_item AS I ON S.product_id=I.product_id AND S.product_size=I.product_size LEFT JOIN product_purchases AS P ON I.product_purchase_item_id=P.product_purchase_item_id SET -- CHANGE HERE ===============================> S.product_size_quantity=S.product_size_quantity+MIN(I.quantity) WHERE S.product_id=? AND S.product_size=?

Categories : Mysql

Oracle/SQL - Need help optimizing COUNT DISTINCT query
Firstly, verify that the columns you use in your WHERE clauses (and from the COUNT DISTINCT) have indexes (or are primary/unique/foreign keys). Secondly, check if you could add other WHERE clauses to replace the DISTINCT in the COUNT, or even replace it with a COUNT(*) which is very optimized. Thirdly, you could try to move your count(distinct) to another join or even a subquery and check if performances changes.

Categories : SQL

Counts rows returned by query that uses distinct
Your query is straight to the point, does the job, and it's simple enough, I'm sure you can bake some unnecessary rocket science into it, but would be overblown imho. Aside from what you have, you can use a group by like below to illustrate what I mean, but you will be basically doing the same thing, getting the uniques and counting them. SELECT COUNT(1) FROM (SELECT a FROM MyTable WHERE a = 'a' GROUP BY a, b, c) Temp

Categories : Sql Server

limit results returned by distinct query
Should this suffice? Event.executeQuery("select distinct e from Event as e inner join e.performances as p where p.dateCreated is not null order by p.dateCreated desc", [max: 5]) If you also need performances corresponding to each event in the same query, you can use inner join fetch.

Categories : Hibernate

Does 'select distinct' returns the first distinct value or last distinct?
I think there's a misunderstanding here: Your query does not return the records, only the distinct column values. Which, in your example, are 'Sam' and 'Tom'. They have no particular order which can safely be expected. It may be the natural order, or the order in which they are processed on the database (completely depending on the database implementation), or semi-random (such as iterating over items in a set). The order may also vary depending on whether the result was retreived from the data or from the cache. If you want a particular order, then specify it as order criterium: select distinct Name from Emp order by Name asc If you want the distinct values and the first record containing it, use group by: select min(ID), Name from Emp group by Name

Categories : SQL

SQL Query with group by clause, but counting two distinct values as if they were the same
If you need A and B, D and E, to count the same, you can build a query like this: SELECT CASE Name WHEN 'B' THEN 'A' WHEN 'E' THEN 'D' ELSE Name END as Name , COUNT(*) FROM table GROUP BY CASE Name WHEN 'B' THEN 'A' WHEN 'E' THEN 'D' ELSE Name END Demo on sqlfiddle.

Categories : SQL

how to query group by and distinct with a limit in MongoDB PHP codeigniter?
First of all using group_by and distinct together doesn't make any sense. Either you are using group_by or distinct. If you want to do some kind of pagination for a grouped query you have to use map and reduce or the aggregation pipeline. In your case the aggregation would look like that. db.users.aggregate( { '$match' => { 'user' => 'ashok' } }, { '$group' => { '_id' => '$details.url' } }, { '$skip' => 0 }, { '$limit' => 10 } ) I am using this aggregation feature to display references at VersionEye. The aggregation feature allows to do grouping and paging on db level, that's why it's much faster then other ORM or pagination solutions.

Categories : PHP

adding DISTINCT keyword to query() with SQLite in Android
Instead of the... public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) ...method you're using, just use the... public Cursor query (boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) ...overload and set distinct to true. The Android docs seem a bit hard to direct link, but the doc page describing both is here.

Categories : Android

aggregate and distinct linq query output to list
Use GroupBy and Sum: var result = db.Table .GroupBy(r => r.Date) .Select(g => new{ Day=g.Key, Hours=g.Sum(r=> r.Hours) }) If Date is actually a DateTime you should use r.Date.Date to remove the time portion.

Categories : C#

Linq query no longer ordered properly after .Distinct() used
Apply ordering after distinct: public JsonResult OverrideSearch(string term) { var drm = db.ZipCodeTerritory .Where(d => d.DrmTerrDesc.Contains(term)) .Select(d => d.DrmTerrDesc) .Distinct() .OrderBy(d => d); return Json(drm, JsonRequestBehavior.AllowGet); } Or with query syntax (I don't like to mix query and method syntax): public JsonResult OverrideSearch(string term) { var drm = from d in db.ZipCodeTerritory where d.DrmTerrDesc.Contains(term) select d.DrmTerrDesc; drm = drm.Distinct(); return Json(drm.OrderBy(d => d), JsonRequestBehavior.AllowGet); } BTW there is a remark on Queryable.Distinct method at MSDN which tells that it usually returns unordered s

Categories : Dotnet

How to select distinct values from query and concatenate into one column
Assuming you mean the possible categories for the matching names:- SELECT `client`.*, Sub1.possible_categories FROM (`client`) CROSS JOIN (SELECT GROUP_CONCAT(DISTINCT client.category) AS possible_categories FROM (`client`) WHERE `name` LIKE '%query%') Sub1 WHERE `name` LIKE '%query%' Note the leading wildcard in the LIKEs will probably make it run slowly.

Categories : PHP



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.