w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
How can I SELECT rows from a table when I MAX(ColA) and GROUP BY ColB
Try SELECT s.*, q.avg_score FROM scores s JOIN ( SELECT player, MAX((scoreA + scoreB)/2) AS avg_score FROM scores GROUP BY player ) q ON s.player = q.player AND (s.scoreA + s.scoreB)/2 = q.avg_score ORDER BY q.avg_score DESC, s.time ASC Sample output: | ID | PLAYER | TIME | SCOREA | SCOREB | AVG_SCORE | ---------------------------------------------------- | 5 | John | 35 | 90 | 90 | 90 | | 2 | Bob | 22 | 75 | 85 | 80 | | 4 | Ted | 39 | 60 | 90 | 75 | Here is SQLFiddle demo

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

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

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

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

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#

Select distinct values of distinct group
You can use the following code to get your desired array with only 1 SQL executed $sql = "SELECT title, group_concat(year) as years FROM table_name group by title" $rs = mysql_query($sql); $arr = array(); while($row = mysql_fetch_assoc($rs)) { $arr[$row['title']] = explode(',',$row['years']); }

Categories : PHP

IQueryable.Distinct() vs List.Distinct()
Obviously you cannot have exact duplicate rows (including the primary key) in your table. Probably what you mean is rows with some equal fields (excluding primary key). Calling Distinct on IQueryable, generates a SQL DISTINCT operator on the resulting query, which compares every field of your table against each other. Because you cannot have exact duplicate rows in the table, it returns all the rows. On the other hand, calling Distinct on a List<User> will use Equals method of the User object to compare objects in memory (after fetching all the rows from database). The final result depends on the implementation of Equals method, which could check only some fields for equal values.

Categories : C#

Distinct value of a column and do a count on each distinct value
Considering the following example: To get the unique values, and the occurrences of each unique value, you can do the following: The image is tiny, so here is are the formulas to type-in and drag down: FORMULAS 1. Type in the first cell of column B: =IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$8),0,0),0))),"",INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$8),0,0),0))) Drag the formula down the column B. You will get a list of unique values. 2. Type in the first cell of column C: =COUNTIF($A$2:$A$8,B2) Drag the formula down the column C. You will get the number of occurrences of each unique value. RESULT This is what you will get:

Categories : Excel

Join Distinct Id on non-distinct id (MySql)
SELECT t.ID, t.val_string, t.val_int, t.val_datetime FROM table1 AS t LEFT JOIN (subquery) AS v_table ON t.ID = v_table.ID Sample fiddle

Categories : Mysql

select distinct out of distinct
The easiest way is to do aggreagtion: select etar.EmplKey, min(emp.EmplFullName) FROM EmployeeTarget etar inner join DimEmployee emp on emp.emplkey = etar.emplkey inner join dimbranch br on br.BranchId = etar.BranchId where etar.BranchId = 8 group by etar.EmplKey

Categories : SQL

Making a list distinct in c# of a distinct list
You need to Trim() first before do Distinct() var list = new[] {" string", "string "}; var distinctList = list.Select(s => s.Trim()).Distinct(); If you also need to ignore case-sensitive: var list = new[] {" String", "string "}; var distinctList = list.Select(s => s.Trim()) .Distinct(StringComparer.InvariantCultureIgnoreCase); If your list contains null, presumably you ignore it, you can easily filter first: var list = new[] {" String", "string ", null}; var distinctList = list.Where(s => !string.IsNullOrEmpty(s)) .Select(s => s.Trim()) .Distinct(StringComparer.InvariantCultureIgnoreCase); In case you need to keep null on the result, create an extension method to customize Trim(): public static

Categories : C#

How to map over a value iff it's distinct from None?
If you don't tell us what v is and what you want to do, this is the best I can come up with. If v is iterable: result = [f(x) for x in v if x != None] If v is a single value then your if condition sound OK to me.

Categories : Python

Sql Sum of Distinct
Store the value in a different table for different days. Otherwise it will always take approximately the time you said. It may be optimized a little but it wont come down much from the 5-6 minutes. I am assuming you need something faster than that. You may try using something like query cache if you are not comfortable with storing the number in your database. Bottom line : you can't speed up sql builtin functions. Hope that helps :)

Categories : SQL

Using a sum with a distinct in SQL
Please Generalize the Question and then ask, If i understood your problem Here is solution: General Query : select sum(a.amountColumn) from your_table group by agrrColumnName; If i change your query : Select distinct B.Proj_Nbr,c.proj_cc,h.proj_cc, h.Proj_Hier, B.Proj_Nm, D.Fscl_Per, sum(A.Amount) from acct_bal a inner join dim_proj b on a.dim_proj_id = b.dim_proj_id inner join essbase_fcs.projects_hier_map c on c.proj_nbr = b.proj_nbr inner join dim_per_mo d on d.dim_per_mo_id = a.dim_per_mo_id Inner Join Dim_Acct F On A.Dim_Acct_Id = F.Dim_Acct_Id Inner Join Dim_Org G On A.Dim_Org_Id = G.Dim_Org_Id inner join essbase_fcs.projects_hier_map h on h.proj_cc = g.cost_ctr inner join dim_org g1 on c.proj_cc = g1.cost_ctr Where F.Fin_Lee_N

Categories : SQL

SQL get distinct first name and last name
You have duplicates because you are including id in the select: select distinct FirstName, LastName from table; If you need an id for a name, then use group by: select min(id) as id, FirstName, LastName from table group by FirstName, LastName; EDIT: If you are still getting duplicates with these queries, then you have characters in the names that are throwing things off. I would start by trimming leading and trailing spaces to see if this fixes the duplicates problem: select min(id) as id, trim(FirstName) as FirstName, trim(LastName) as LastName from table group by trim(FirstName), trim(LastName);

Categories : Mysql

How do I specify DISTINCT when using JPA 2?
That can be done by using distinct method, which can be found from CriteriaQuery itself: query.distinct(true);

Categories : Hibernate

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

Distinct with a group by
You are on the right track using a GROUP BY Following should return the results as per your question SELECT MIN(id) AS id, val1, val2 FROM YourTable GROUP BY val1, val2 Breakdown Use an aggregate function on the column you don't wish to group on. In our example this is the MIN aggregate function on the id column. This returns the lowest id for each group GROUP BY all the columns you want to get a distinct value of

Categories : Mysql

Get the row with max(column) for distinct key
You can use the ranking function ROW_NUMBER() with PARTITION BY code ORDER BY month DESC to do this: WITH CTE AS ( SELECT code, amount, month, ROW_NUMBER() OVER(PARTITION BY code ORDER BY month DESC) AS RN FROM Tablename ) SELECT code, amount, month FROM CTE WHERE RN = 1; This will give you the maximum month for each code. SQL Fiddle Demo

Categories : SQL

Distinct Function C#
Distinct does not do this, as Eric says. Instead, it feeds you distinct elements of a container in an IEnumerable<T>. The exact implementation of Distinct actually varies on the container. Consider the following code snippet: public static class StaticyGoodness { public static void Main() { var someAs = new List<A>(); var someBs = new List<B>(); // get it? DoTheThings(someAs); // Doing things the regular way DoTheThings(someBs); // Doing things the SPECIALIZED way DoTheThings(someBs.OrderBy(b => b.Stuff)); // Doing things the REALLY SPECIALIZED way } private static void DoTheThings<T>(this IEnumerable<T> source) { Console.WriteLine("Doing things the regular w

Categories : C#

Regarding SQL Distinct clause
Pare down your GROUP BYs. It's first going to group by ITEMMAST.ITEM, and then ICLOCATION.LOCATION. Either switch the order or get rid of the ITEM. Also, unrelated, you may want to clean it up a bit by using the IN operator in your WHERE statement. i.e. ICLOCATION.LOCATION IN ('120', '123', '128', '129', 'HD41D', 'HD21H', 'HD91H')

Categories : SQL

Sql Distinct & count
When pid, pname and description are the same (like in your example) then group by will aggregate those results to one row. select t.pid, t.pname, t.description, sum(t.qty) as qty from tbltemptransaction t group by t.pid, t.pname, t.description Edit select t.pid, sum(t.qty) as qty from tbltemptransaction t where pid = @userid group by t.pid @user id is a parameter for session("userid") Final Edit select t.pid, p.pname, p.description, sum(t.qty) as qty from tbltemptransaction t inner join tblproducts p on p.pid = t.pid where t.userid = 3 group by t.pid, p.pname, p.description

Categories : SQL

Best way to get distinct values
Try the distinct query, if your field is indexed you can use this: https://developers.google.com/appengine/docs/python/ndb/queries#projection query_set = cls.query(projection=["field"], distinct=True) set_of_field = [data.field for data in query_set] But if you have a huge list you can either do this in a taskqueue and store the results somewhere or just keep a distinct data in another model.

Categories : Google App Engine

SQL distinct between different columns
Assuming that column1 and column2 are both numeric data types and you are comparing 2 columns you can use the following SELECT DISTINCT LEAST( column1 , column2 ) AS leastColumn, GREATEST( column1 , column2 ) AS greatestColumn FROM yourTable I'm not entirely sure why you would wish to do this though, as if the two columns store the same data then this table is part of an n:m relationship and so this could be further normalised. Additionally this would be come horribly inefficient as the dataset grows but for your specific question this fits the bill.

Categories : Mysql

SQL with two DISTINCT Statement
I think you only need an ON clause on the JOIN, something like this : JOIN CSSC_QUALITY_DATA ON CSSC_MASTER_VIEW."Created_by" = CSSC_QUALITY_DATA."Created_by" Finally to avoid the distinct put everything you need to be distinct in the GROUP BY clause : GROUP BY CSSC_MASTER_VIEW."Created_by", CSSC_QUALITY_DATA."Created_by", In this way you can avoid the use of the distinct and achieve the same desired result with the Group BY .

Categories : SQL

Select 5 of each distinct value
you can use row_number, but you have to specify order or you will get unpredictable resutls with cte as ( select *, row_number() over(partition by a order by b, c) as row_num from table1 ) select a, b, c from cte where row_num <= 5

Categories : SQL

IS NOT DISTINCT FROM versus =
a is not distinct from b amounts to writing: a = b or a is null and b is null Presumably, you've rows where b.datetime and a.time are both null. (I'd add a note on index usage. This might have changed since I last checked, but back in the 9.1 days, is distinct from wasn't expanded, but rather treated as an operator in its own right. This prevented it from using indexes entirely.)

Categories : Postgresql

Using LINQ Distinct in C#
You need to use MoreLINQ's DistinctBy: var s = (from n in _dataBaseProvider.SelectPjdGatewayLineChanged (selectedSourcePlant.LPS_Database_ID) select new PjdGatewayLineChanged { LineId = n.LineId, LpsLineNo = n.LpsLineNo, LineIdChanged = n.LineIdChanged }) .DistinctBy(p => p.LineId);

Categories : C#

Distinct by product id
You can implement an equality comparer. There is a solid example on msdn: http://msdn.microsoft.com/ru-ru/library/bb338049.aspx This will give you more control over which items you consider equal. But involves more coding. If you want to select single and somewhat random item from all the items which share an id, then you'll be better off with spender's solution

Categories : C#

SQL Select Distinct
You can (as you're suspecting) do it using a simple GROUP BY/HAVING and using DISTINCT; SELECT id FROM documents GROUP BY id HAVING COUNT(DocumentNumber) = COUNT(DISTINCT DocumentNumber) An SQLfiddle to test with.

Categories : SQL

getting duplicates after using distinct
About SQL joins, you may have some fun here... It's the kind of thing we learn once, to use during our entire life. About your specific query, test this first example, SELECT mer.store_name, mpr.`merchant_code`, mpr.`terminal_num`, mpr.`rec_fmt`, mpr.`bat_nbr`, mpr.`Card_Type`, mpr.`card_num`, mpr.`transaction_date`, mpr.`settle_date`, mpr.`approval_code`, mpr.`intnl_amt`, mpr.`domestic_amt`, mpr.`transid`, mpr.`upvalue`, mpr.`merchant_trackid`, mpr.`MSF`, mpr.`service_tax`, mpr.`edu_cess`, mpr.`net_amount`, mpr.`debit_credit_type`, mpr.`UDF1`, mpr.`UDF2`, mpr.`UDF3`, mpr.`UDF4`, mpr.`UDF5`, mpr.`seq_num`, mpr.`arn_no` FROM merchantreports mer INNER JOIN storename_tid sid ON (sid.Store_Name = mer.Store_Name) INNER JOIN `mpr_reports`

Categories : Mysql

Trying to sum distinct values SQL
You can do the following: SELECT SUM(distinct [Ship Cost]) . . . But, I don't recommend this. You could have two items with the same cost and only one would be counted. The better way is to select one value for each Tracking #, using the row_number() function: select SUM(case when seqnum = 1 then [Ship Cost] end) from (select t.*, row_number() over (partition by [Order #], [Tracking #] order by (select NULL) ) as seqnum . . . ) t

Categories : SQL

Distinct Counters in Cassandra
A lot would depend on the performance requirements, consistency guarantees, number of systems etc (pretty much a standard). That said I do have a recommendation if willing to have it probabilistic, and you dont need it instantly constantly upto date. HyperLogLog, as you mentioned is a great choice. Each server could keep one in memory that as data is inserted it updates and periodically (ie once per minute) pushes to cassandra... or some other store. Due to the nature of HyperLogLog, when you want to query it you can do a column slice of the servers hyperlogs and combine them. ColumnFamilyHyperLogLogs: { someMetricsCardinalityRow20130828: { Server1: HyperLogLogBlob, Server2: HyperLogLogBlob, Server3: HyperLogLogBlob } } I would recommend clearsprings library thats w

Categories : Cassandra

Making a list distinct in C#
You need to select the distinct property first: Because it's a list inside a list, you can use the SelectMany. The SelectMany will concat the results of subselections. List<A> listOfA = new List<A>(); listOfA.SelectMany(a => a.KeyValuePairs .Where(keyValue => keyValue.Key == "Country") .Select(keyValue => keyValue.Value)) .Distinct(); This should be it. It will select all values where the key is "Country" and concat the lists. Final it will distinct the country's. Given that the property KeyValuePairs of the class A is at least a IEnumerable< KeyValuePair< string, string>>

Categories : C#

Is there a way to set a distinct ID and name for a text_field in Rails 2?
try this: <% form_for @newsavedmap, :html=>{:id=>'createaMap'} do |f| %> <%= f.text_field :newsavedmapname :id=>"savemap_name", :size=>30, :value=>"New Map"%> <%= f.select :startthere, YOUR_COLLECTIONS, {}, {:id=>"startdrop"}%> <%= f.select :waypointsselected, YOUR_COLLECTIONS, {}, {:id=>"waypoints", :class=>"mobile-waypoints-remove", :multiple => true}%> <% end %> make sure YOUR_COLLECTIONS should be an array like ['a', 'b', 'c'] or [['name1', id1],['name2', id2],['name3', id3]]. If you persist the parameter is "newsavedmapname"=>"test Map", try this: <% form_for @newsavedmap, :html=>{:id=>'createaMap'} do |f| %> <%= text_field_tag :newsavedmapname, "New Map", :id=>"savemap_name", :size=>30%> &

Categories : Ruby On Rails

XSL 1.0 distinct values from a template
You're very close with the key approach, the trick is that you need to include the identity of the parent node as part of the grouping key: <xsl:key name="children" match="child" use="concat(generate-id(..), '|', .)" /> <xsl:template name="uniqueChildren"> <xsl:param name="parent" /> <xsl:for-each select="$parent/child[generate-id() = generate-id( key('children', concat(generate-id($parent), '|', .))[1])]"> <xsl:value-of select="." /> </xsl:for-each> </xsl:template> This creates key values of the form "<id-of-parent>|apple", "<id-of-parent>|orange", etc. Edit: in your comment you say "In my actual data, the child nodes aren't a direct child of the parent nodes. There's 2 levels between parent and child, e.g.

Categories : Xml

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

Getting different results from group by and distinct
Your second query is not doing what you expect it to be doing. This is the query: SELECT COUNT(MATNR) AS LESS FROM (SELECT LAB_MTKNR AS MATNR, LAB_STG AS FACH, LAB_STGNR AS STUDIENGANG, LAB_PDATUM FROM FKT_LAB GROUP BY LAB_MTKNR, LAB_STG, LAB_STGNR HAVING LAB_PDATUM = MAX(LAB_PDATUM) ) TEMP2; The problem is the having clause. You are mixing an unaggregated column (LAB_PDATUM) with an aggregated value (MAX(LAB_PDATAUM)). What MySQL does is choose an arbitrary value for the column and compare it to the max. Often, the arbitrary value will not be the maximum value, so the rows get filtered. The reference you give (although an accepted answer) is incorrect. I have put a comment there. If you want the most recent value, here is a relatively easy way: SELECT COUN

Categories : Mysql

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



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