w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Getting Count Only of Distinct Value Combinations of multiple fields.
The subquery/CTE method is the "right" way to do it. A quick (in terms of typing but not necessarily performance) and dirty way is: select count(distinct customername+'###'+Planet) from #Customer; The '###' is to separate the values so you don't get accidental collisions.

Categories : SQL

How To Select Distinct Row Based On Multiple Fields
WITH CTE AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY Owner ORDER BY Date DESC) AS RN FROM tablename ) SELECT ID, Name, Date, Location, Owner FROM CTE WHERE RN = 1;

Categories : SQL

CakePHP 1.3 Return all fields but with one of the fields as DISTINCT
$this->Car->find('first', array('conditions'=>array('Car.colour'=>'blue'))); This will return the result that you trying to get. I'm afraid this is not what you expected :)

Categories : Cakephp

Get distinct sets of fields from MongoDB
it is not that easy, but you can do it :-). The most challenge is to create the bson query and to use the runCommand syntax from mongodb. Here is some example code and the data you can find in docs.mongodb.org. An example SQL query could look like this: SELECT state, SUM(pop) AS totalPop FROM zips GROUP BY state HAVING pop > (10000) In mongoDB shell you will run something like this. db.zipcodes.aggregate( { $group : { _id : "$state", totalPop : { $sum : "$pop" } } }, { $match : {totalPop : { $gte : 10000 } } } ) You can run the same with the command db.runCommand which has the following default syntax for the aggregation framework: db.runCommand( { aggregate :

Categories : R

Select fields from table with DISTINCT field
Use GROUP BY before the order clause and if you want order results first then group them then use subselect SELECT `user`, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd FROM my_table GROUP BY `user` ORDER BY (parentsEd + sonsEd) DESC SELECT DISTINCT `user`, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd FROM my_table ORDER BY (parentsEd + sonsEd) DESC SELECT a.* FROM ( SELECT DISTINCT `user`, COALESCE(parents, 0) AS parentsEd, COALESCE(sons, 0) AS sonsEd FROM my_table ORDER BY (parentsEd + sonsEd) DESC ) a GROUP BY a.`user`

Categories : Mysql

Selecting records from DB with Zend using distinct but two fields
Something like this? $objSelect = $db->select() ->distinct() ->from('tbl_sc_invites', array('id', 'sender_name')) ->order('sender_name ASC'); Otherwise if you don't care about which id you get back you could use GROUP BY $objSelect = $db->select() ->distinct() ->from('tbl_sc_invites', array('id', 'sender_name')) ->order('sender_name ASC') ->group('sender_name');

Categories : Mysql

MongoDB Aggregation: Counting distinct fields
I figured this out by using the $addToSet and $unwind operators. Mongodb Aggregation count array/set size db.collection.aggregate([ { $group: { _id: { account: '$account' }, vendors: { $addToSet: '$vendor'} } }, { $unwind:"$vendors" }, { $group: { _id: "$_id", vendorCount: { $sum:1} } } ]); Hope it helps someone

Categories : Mongodb

Selecting 2 distinct fields but also return the rest of the columns
what is the reason for not using GROUP BY SELECT * FROM `messages` WHERE `from`='9129771472' GROUP BY `from` OR SELECT * FROM `messages` WHERE `from`='9129771472' GROUP BY `from`,`to`

Categories : PHP

django distinct doesn't return just unique fields
As Rob pointed out, distinct() works differently than you expected. It looks at all the fields to determine uniqueness, not just the ones you specify in values(). If you're using PostgreSQL then you can do what you want by passing arguments to distinct(). From the documentation: You can pass positional arguments (*fields) in order to specify the names of fields to which the DISTINCT should apply. This translates to a SELECT DISTINCT ON SQL query. Here’s the difference. For a normal distinct() call, the database compares each field in each row when determining which rows are distinct. For a distinct() call with specified field names, the database will only compare the specified field names. Getting back to your ultimate goal of finding all conversation partners, I don't

Categories : Django

Rails ActiveRecord, selecting distinct fields from a joined table and returning all the models
Try the following: gifts = Gift.joins(:berry).select(:basket_id, :orange_id, :berry_id, :action_id).uniq.to_a gifts will hold an array of gifts with only the attributes specified in select initializated: puts gifts.first.attributes

Categories : Mysql

jQuery autocomplete ON multiple fields as well as TO multiple fields
Supply a callback function to handle the select event as an init option or bind to the select event. See api documentation on the select event. The select event will pass an event object. The event object has the property named target. You can use this property to determine the which DOM element initiated the event. Something LIKE this will work. $( "input" ).autocomplete({ source: "search.php", select: function(event,ui){ var targetFieldId = event.target.id; //get id of DOM element ie bar2 var relatedField = document.getElementById(targetFieldId+'email'); //get related field relatedField.val(ui.item.value); // assuming second varible is value } });

Categories : Jquery

SQL multiple Select ... where id in and a DISTINCT
You should be using joins and consider creating indexes at least on foreign keys Try the following SELECT DISTINCT TP_Test_Info.id, TP_Test_Info.name FROM TP_Test_Info JOIN TP_Test_Sections ON TP_Test_Sections.test_id = TP_Test_Info.id JOIN TP_Test_Questions ON TP_Test_Questions.section_id = TP_Test_Sections.id JOIN TP_Student_Answers ON TP_Student_Answers.question_id = TP_Test_Questions.id WHERE TP_Student_Answers.student_id = 751

Categories : Mysql

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

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

Distinct count with multiple columns
It sounds like you want to count only the records with minimum Relevancy for each ID. Therefore, you can do (SQL Fiddle example): SELECT Category, COUNT(1) FROM Table1 t1 WHERE NOT EXISTS ( SELECT 1 FROM Table1 t2 WHERE t2.ID = t1.ID AND t2.Relevancy < t1.Relevancy ) GROUP BY Category

Categories : Mysql

How to insert multiple values into a Row if 1 field is distinct
insert into Locals (Street, PC, Locality) select b.Street, b.PC, b.Locality from Locals_bk as b where not exists (select * from Locals as t where t.street = b.street) or insert into Locals (Street, PC, Locality) select b.Street, b.PC, b.Locality from Locals_bk as b where b.street not in (select t.street from Locals as t)

Categories : SQL

Best way to display only distinct value from multiple div tags with same class name
Probably somthing like this? $('.categories').filter(function(){ //use filter on all .categories var txt = $.trim(this.innerHTML); //Get the text of current return ($(this).nextAll().filter(function () { //filter all of the proceeding siblings which has the same text return $.trim(this.innerHTML) === txt }).length); //send true or false (in fact truthy or falsy to ask to hide the current element in question) }).hide(); Fiddle Another derivative and this will hide the first ones while the prev ones will hide the last one. $(function () { $('.categories').each(function(){ var txt = $.trim(this.innerHTML); $(this).nextAll(':visible').filter(function () { return $.trim(this.innerHTML) === txt }).hide(); });

Categories : Javascript

LINQ to SQL - Distinct records across multiple columns
Group results by ProductCode (1) and select from each group item with max Version value (2): results = (from a in context.ALLProducts let prodCode = a.ProductCode let prodDesc = a.ProductDescription where legList.Contains(a.LegislationID) && (a.LanguageID == pLanguage || pLanguage == null) && a.BrandName == pMarket && (prodCode.Contains(pSearch) || prodDesc.Contains(pSearch) || pSearch == string.Empty) && prodCode[0] != 'x' && ((pMarket == "testMarket") ? prodCode.StartsWith("0") : true) group a by a.ProductCode into g // 1 let lastProduct = g.OrderByDescending(x => x.V

Categories : C#

Using distinct in Spring data over multiple columns
@Query return ArrayList of Object(s) instead of specific type of object. so you have to define some thing like @Query("select distinct a.name, a.xAxis, a.yAxis from CollectedData a") List<Object> findAllDistinctData(); then cast according to your requirement, List<Object> cdataList=findAllDistinctData(); for (Object cdata:cdataList) { Object[] obj= (Object[]) cdata; String name = (String)obj[0]; String description = (String)obj[1];; ... }

Categories : Spring

LINQ Distinct on multiple properties both directions
Create an extension method such as this one: public static IEnumerable<TSource> DistinctBy<TSource, TKey>(this IEnumerable<TSource> source, Func<TSource, TKey> keySelector) { var seenKeys = new HashSet<TKey>(); return source.Where(element => seenKeys.Add(keySelector(element))); } Then use it. See how that works for you.

Categories : C#

Count distinct value pairs in multiple columns in SQL
Get all distinct id, name and address columns and count the resulting rows. SELECT COUNT(*) FROM mytable GROUP BY id, name, address

Categories : SQL

mysql COUNT DISTINCT multiple columnes with WHERE
Are you looking for the count per pid? If so, this is a simpler query: SELECT pid, SUM(location like '%a%') as As, SUM(location like '%b%') as Bs, SUM(location like '%c%') as Cs FROM db.t WHERE (date > @dt) GROUP BY pid

Categories : Mysql

Django Filter distinct field from multiple table
You can split input string first: key_skill, title = "python,web developer".split(',') Then filter jobs cross models: jobs = Job.objects.filter(title=title, employerkeyskills__keyskills=key_skill)

Categories : Python

Entity Framework Distinct records from multiple tables
Well this should work without many changes, though i would have wrote it entirely in expressions: var distinctValues = (from a in dataContext.A_Table join b in dataContext.B_Table on a.EmpID equals b.EmpID join c in dataContext.C_Table on b.SomeID equals c.ID where a.IsActive == true && a.ID == id select new NewClass() { ID = c.ID, Name = c.Name }).ToList() .GroupBy(x=>new {ID = x.ID,Name = x.Name}) .Select(x=>new {ID = x.Key.ID,Name = x.Key.Name});

Categories : C#

c# linq select distinct of elements with multiple attribute
I want to return distinct tool and process attribute. It sounds like you want this this: var results = from e in apcxmlstate.Elements("thread") group e by Tuple.Create(e.Attribute("process").Value, e.Attribute("tool").Value) into g select g.First().Attribute("tool").Value; Or in fluent syntax: var results = apcxmlstate .Elements("thread") .GroupBy(e => Tuple.Create(e.Attribute("process").Value, e.Attribute("tool").Value)) .Select(g => g.First().Attribute("tool")); This will return the tool for each distinct tool / process pair—given your example set { "atool", "btool", "atool", "ctool" }. However, if all you want is distinct tool values you can just do this: var results = apcxmlst

Categories : C#

MySQL distinct results on duplicate multiple columns
The following gets the list of env/prop where you have a problem: select env, prop from t group by env, prop having count(distinct value) > 1; This joins back to the original table to get the records with the problem: select t.* from t join (select env, prop from t group by env, prop having count(distinct value) > 1 ) tep on t.env = tep.env and t.prop = tep.prop;

Categories : Mysql

find multiple records on basis of multiple fields in mysql table
If I understood you correctly, this query would work for you: SELECT p1.id, p1.name, p1.sku, p1.programme FROM product p1 JOIN product p2 ON p1.sku = p2.sku AND p1. programme <> p2.programme If not then please add some sample data and expected result. Based on sample data, try this query: SELECT * FROM product WHERE id IN( SELECT p1.id FROM (SELECT MIN(id) as id, sku, programme FROM product GROUP BY sku, programme) p1 JOIN (SELECT MIN(id) as id, sku, programme FROM product GROUP BY sku, programme) p2 ON p1.sku = p2.sku AND p1.programme <> p2.programme )

Categories : Mysql

Can't insert multiple data against an id into multiple fields in a table in mySQL?
It would be really helpful, if you could post your MySQL statement used for inserting the data. The canonical way would be: INSERT INTO `shirts`(`p_id`, `colors`, `random`) VALUES (1, 'red', 400), (1, 'green', 400), (1, 'orange', 400);

Categories : PHP

Count multiple occurrence of multiple fields in a column corresponding to another attribute
You do not need to use UNION for that. You can do this using SUM() function and CASE statement like this: SELECT Name ,SUM(CASE WHEN Fruit = 'Mango' THEN 1 ELSE 0 END) AS MangoCount ,SUM(CASE WHEN Fruit = 'Apple' THEN 1 ELSE 0 END) AS AppleCount ,SUM(CASE WHEN Fruit = 'Banana' THEN 1 ELSE 0 END) AS BananaCount FROM MyTable GROUP BY Name; You can also use this dynamic query: SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(CASE WHEN `Fruit` = ''', `Fruit`, ''' THEN 1 ELSE 0 END) AS `', `Fruit`, '`' ) ) INTO @sql FROM MyTable; SET @sql = CONCAT('SELECT Name, ', @sql,' FROM MyTable GROUP BY Name '); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

Categories : Mysql

What would be the best way to go about writing this in JS/jQuery? (Multiple individual objects with multiple fields)
Honestly, with the amount of data you are talking about and the number of ways it could be modified, you should look into a framework that is more robust and was designed to make data binding much easier. There are many out there, but my personal favorite is AngularJS. http://angularjs.org/ Start looking into this as it may save you a lot of time in developing the app and then of course maintaining it down the road.

Categories : PHP

Mvc .net: Upload Multiple files in multiple fields via one http post
View: <input type="file" name="Photos"> <input type="file" name="Photos"> <input type="file" name="Logos"> <input type="file" name="Logos"> <input type="file" name="Video"> You can populate uploaders with javascript. Controller: [HttpPost] public ActionResult Create( ViewModel model, IEnumerable<HttpPostedFileBase> Photos, IEnumerable<HttpPostedFileBase> Logos, HttpPostedFileBase Video ) { ... } We can also post uploaded files via a View Model, which is much more cleaner. Check this for details.

Categories : Asp Net Mvc

Put unique distinct values into a list from multiple columns of an array
One way to do this is create a function that returns the array of your unique cells and then multiplies them all by matches in your Marks column. Create the unique cells with this array function. Note this function uses the Dictionary object. In the VB Editor, go to Tools > References, and make sure Microsoft Scripting Runtime is selected. Public Function UniqueValues(aRange As Range) Dim DictValues As New Dictionary Dim cll As Variant Dim aryResults() As String For Each cll In aRange If Not DictValues.Exists(cll.Value) Then DictValues.Add cll.Value, "": Next UniqueValues = DictValues.Keys Set DictValues = Nothing End Function Enter in cell H3 and press CTRL SHIFT RETURN (as it's an array function) =TRANSPOSE(uniquevalues(B3:E6)) and drag down to H15 or beyond We have to use

Categories : Excel

Extracting Multiple Similar Rows For Each Distinct Type Of A Column
Basically, you can get the record that is present in all fruits regardless of name via this query, SELECT col2, col3 FROM tableName GROUP BY col2, col3 HAVING COUNT(*) = (SELECT COUNT(DISTINCT col1) FROM tableName) SQLFiddle Demo The total number of record in a group of two columns: col2 and col3 must be equal to the total number of fruits. SELECT COUNT(DISTINCT col1) FROM tableName. So to get all the records that has a combination present in all fruits, we need to JOIN it with the table itself. SELECT a.* FROM tableName a INNER JOIN ( SELECT col2, col3 FROM tableName GROUP BY col2, col3 HAVING COUNT(*) = (SELECT COUNT(DISTINCT col1) FROM tableName) ) b ON a.col2 = b.col2 AND a

Categories : Mysql

SQL Insert rows into table that must have 2 distinct columns but also one non distinct column
You'll have to just replace the PRIZEID value with new ones. Because it sounds like you currently have duplicates on your PROMOTION table First add all the distinct PRIZENAMEs and COSTs to your new PRIZE table: INSERT INTO prize(PRIZEID, COST, PRIZENAME) SELECT DISTINCT (SELECT MAX(PRIZEID)+1 FROM PRIZE), r.COST, r.PRIZENAME FROM PROMOTION r; Then update your PROMOTIONs table with the new PRIZEID UPDATE PROMOTION R SET R.PRIZEID = (SELECT P.PRIZEID FROM PRIZE WHERE P.PRIZENAME=R.PRIZENAME AND P.COST=R.COST); Then, I think from there you can safely delete the columns from your PROMOTIONs table

Categories : SQL

Concatenate multiple fields on multiple rows with commas
I think this will work for you SELECT s.ip, GROUP_CONCAT(i.name) FROM servers AS s JOIN serverInstances AS si ON s.ID = si.sID JOIN Instances AS i ON si.iID = i.ID GROUP BY s.ip

Categories : PHP

java GUI trying to get multiple inputs from multiple text fields
Only call addActionListener(this); for your button, don't listen to actions on your textFields, because your actionPerformed method is being called when actions occur on your textfield (user hits enter). I'm guessing your doing this after the first field and that your action method is getting called by this and then waiting for the socket connection, as that is a blocking call, this would make your GUI unresponsive.

Categories : Java

Distributing k distinct items among r distinct groups without ordering
The solution is Stirling's Number of the Second Kind * r! So that's: 1/r! * (SUMMATION(j=0 --> r), (-1)^(r-j) * C(r,j) * j^k) * r! Which simplifies to: (SUMMATION(j=0 --> r), (-1)^(r-j) * C(r,j) * j^r)

Categories : Misc

Aggregate String[] grabbing first distinct, but I want it to grab the last distinct
Consider using the overload of Select that takes in the index as part of the transformation (documented here). If you want the last distinct element of each distinct element, you might try to use GroupBy order each IGrouping accordingly, and grabbing the correct element of each group. repeat = domain + dir.Split('/') .Select((word, index) => Tuple.Create(word, index)) .Where(x => keep.Contains(x.Item1)) .GroupBy(x => x.Item1) .Select(g => g.OrderByDescending(x => x.Item2).First()) .OrderBy(x => x.Item2) .Select(x => x.Item1) .Aggregate((gi, j) => gi + "/" + j) + repeat.Substring(lastSlash); Here, we couple each word with its index (using the Select overload mentioned above). Then we filter down to the words of interest. Instead of using Distinct, we now use

Categories : C#

MSQL select regardless of distinct or not distinct (count greater than zero)
In the data in the question, there is no repeating id in table A. The query should be returning only one row from A, then. However, your query has an error. The second join should be back to the first table, not the second: SELECT a.col1, b.col1, c.col2 FROM a LEFT JOIN b ON b.id = a.id LEFT JOIN c ON c.id = a.id WHERE a.id = 1 You had c.id = b.id and b.id could be NULL if there is no match in that table. Also, from your description, I wonder if a union would be the best approach: select a.col1, 'a' from a where id = 1 union all select b.col1, 'b' from b where id = 1 union all select c.col2, 'c' from c where id = 1

Categories : Mysql

Javascript multiple alerts for multiple fields
Instead of returning false in each if statement, create a var to handle the error validation setting it to false in each failed if statement. Only at the end of your function should you then return true or false by checking that var.

Categories : Javascript



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