w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
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

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

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

Hibernate HQL Count Distinct not working?
This is how to do in Hibernate Criteria Criteria crit = session.createCriteria(Rating.class) .add(Restrictions.like("item", item) .addOrder(Order.asc("user")) .setProjection( Projections.distinct(Projections.projectionList() .add(Projections.property("user"), "user"))) .setResultTransformer(Transformers.aliasToBean(Rating.class));

Categories : Java

heroku console distinct not working
It seems you haven't set up the database tables correctly on Heroku. Did you do something like run heroku run db:migrate? Why don't you investigate? Have you tried heroku pg:psql? See here. Also, you really shouldn't be using SQLite - it's not the same database - and SQL is not standard across databases. You'll run into a really subtle database bug at some point.

Categories : Ruby On Rails

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

codeigniter select distinct function not working
In Codeigniter, distinct does not work the way you expect it by field name. If you look at the manual - there is no argument for distinct. If you look at the code, it only takes a boolean, which defaults to true. It just adds the DISTINCT keyword to the query after the SELECT keyword. That's it. In your case, I think it would be better to use a GROUP BY as in $this->db->group_by('opinions.author_id'); Hopefully the order by would work as per your need in this instance by ordering before the grouping. Cheers! EDIT - update after OP comments I know the ordering can be messed up - I sort of mentioned it :) Anyway, I might be assuming some of your table structure here, but this would force the GROUP BY to pick the rows on the top. I assume that the date is on the opinions table and

Categories : PHP

Distinct not working with lambda Duplicate drop down list values
you can use GroupBy public IList<ModelSQL.puzzlecontent> GetID(int id) { //for grouping by multiple properties you can use Groupby like // .GroupBy(i=>new { i.WordPuzzleID,i.SecondProperty}) return context.puzzlecontents .Where(i => i.WordPuzzleID == id) .GroupBy(i=>i.WordPuzzleID) .Select(g=>g.First()).ToList(); } or for LINQ to Objects You can use DistinctBy of MoreLinq public IList<ModelSQL.puzzlecontent> GetID(int id) { return context.puzzlecontents.Where(i => i.WordPuzzleID == id).DistinctBy(i=>i.WordPuzzleID ).ToList(); }

Categories : C#

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

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

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#

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

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

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

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

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

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

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

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

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 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

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 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#

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

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

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

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

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

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#

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

select distinct from elasticsearch
As at present ElasticSearch does not provide a group_by equivalent, here's my attempt to do it manually. While the ES community is working for a direct solution to this problem (probably a plugin) here's a basic attempt which works for my needs. Assumptions. I'm looking for relevant content I've assumed that first 300 docs are relevant, so I consider restricting my research to this selection, regardless many or some of these are from the same few authors. for my needs I didn't "really" needed full pagination, it was enough a "show more" button updated through ajax. Drawbacks results are not precise as we take 300 docs per time we don't know how many unique docs will come out (possibly could be 300 docs from the same author!). You should understand if it fits your average number of d

Categories : Elasticsearch



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