| 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.
|
| 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;
|
| 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 :)
|
| 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 :
|
| 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`
|
| 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');
|
| 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
|
| 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`
|
| 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
|
| 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
|
| 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
}
});
|
| 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
|
| 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
|
| 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;
|
| 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
|
| 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)
|
| 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();
});
|
| 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
|
| 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];;
...
}
|
| 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.
|
| 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
|
| 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
|
| 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)
|
| 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});
|
| 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
|
| 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;
|
| 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
)
|
| 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);
|
| 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;
|
| 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.
|
| 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.
|
| 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
|
| 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
|
| 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
|
| 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
|
| 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.
|
| 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)
|
| 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
|
| 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
|
| 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.
|
