w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Combining two distinct Oracle queries
The UNION should work. Please find below the tables I created and I get the distinct values from both columns. Hope your design is same as below. create table EventDataTable ( ReservationGroupName VARCHAR2(20) ,BookingEventType VARCHAR2(20) ); insert into EventDataTable values ('EMP','S'); insert into EventDataTable values ('ABC','T'); SELECT DISTINCT ReservationGroupName FROM EventDataTable union SELECT DISTINCT BookingEventType FROM EventDataTable; DIS_VAL ABC EMP S T

Categories : SQL

How to avoid DISTINCT as a crutch when doing analytic queries?
If you are grouping by id only select id, max(ts) from x group by id order by id and if the group is composed of id and val select id, val, max(ts) from x group by id, val order by id, val so I would not use put the aggregate in a sub query (could be slower) I would not use window agggregate functions either (because you can do it with plain group by and max) and I would not use distinct, because this means something different (at least for me). If you group on id, and you want one of the values of val, I suggest using the window aggregate functions, because you have to define somehow which val to choose: And this intention belongs into the order by right after partition by. From a maintenance point of view I think that the window aggregate function really

Categories : SQL

Distinct queries in the Java Mongo aggregation framework?
Just extend your aggregation to include building with $addToSet operator: {$group: { _id: "$department", average: { $avg: "$amount" }, buildings: { $addToSet: "$building"} }

Categories : Java

Converting SELECT DISTINCT ON queries from Postgresql to MySQL
you should migrate to PDO or MSYQLI instead of MYSQL as its already deprecated. about your question you can do SELECT DISTINCT col1, col2, col3 or SELECT col1, col2, col3 ........ GROUP BY col1 --//--- or whatever column you want to be distinct

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

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

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#

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

Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active
I am hoping for a better answer than the following. While some of these solutions might "fix" the problem, they don't answer the original question regarding what causes this error. Set PDO::ATTR_EMULATE_PREPARES=>true (I don't wish to do this) Set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (didn't work for me) Use PDOStatement::fetchAll() (not always desirable) Use $stmt->closeCursor() after each $stmt->fetch() (this mostly worked, however, I still had several cases where it didn't) Change PHP MySQL library from php-mysql to php-mysqlnd (probably what I will do if no better answer)

Categories : PHP

Is it possible to combine MySQL queries to multiple tables into a single query based on the results from one of the queries?
You want to look at MySQL Joins. I think this may do something like what you're after, but it will almost definitely need debugging! SELECT DISTINCT s.ownerid, s.message FROM statusupdates s LEFT JOIN friends f ON ($userid = f.requestfrom) LEFT JOIN friends f ON ($userid = f.requestto) ORDER BY s.createddate;

Categories : PHP

externalize hibernate queries or sql queries in properties file in spring
If I understood right, it sounds like regular Spring usage. You may have a class like: class userDao { String findActiveUsers; //... Getters and Setters public List<User> findActiveUsers() { return getCurrentSession().createQuery(findActiveUsers).list(); } } So, your application context would look like: <bean id="userDao" class="my.package.UserDao"> <property name="findActiveUsers" value="FROM User u WHERE u.active=true"/> </bean>

Categories : Java

'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active
Took a bit of fiddling, but I found that when I took the ATTR_EMULATE_PREPARES=false out (the default is to emulate), it worked: <?php $db = new PDO ($cnstring, $user, $pwd); $db->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //$db->setAttribute (PDO::ATTR_EMULATE_PREPARES, false); $db->setAttribute (PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); $st = $db->query ("CREATE TRIGGER `CirclesClosureSync` AFTER INSERT ON Circles FOR EACH ROW BEGIN INSERT INTO CirclesClosure (ancestor, descendant) SELECT ancestor, NEW.ID from CirclesClosure WHERE descendant=NEW.Parent; INSERT INTO CirclesClosure (ancestor, descendant) values (NEW.ID, NEW.ID); END;"); $st->closeCursor(); ?> Hope this helps someone

Categories : PHP

mssql multiple queries insert queries and results
I think that you are talking about an OUTPUT clause from the insert statement. http://msdn.microsoft.com/en-us/library/ms177564.aspx CREATE TABLE #t (id int identity (1, 1), f1 nvarchar(20 ) ) --the table that has the identities CREATE TABLE #ids ( id int ) --the table to store the inserts into table #t INSERT INTO #t ( f1 ) OUTPUT INSERTED.id INTO #ids SELECT N'AAAA' INSERT INTO #t ( f1 ) OUTPUT INSERTED.id INTO #ids SELECT N'BBBB' INSERT INTO #t ( f1 ) OUTPUT INSERTED.id INTO #ids SELECT N'CCCC' SELECT * FROM #t SELECT * FROM #ids Another way is to use @@IDENTITY or SCOPE_IDENTITY() SQL Authority link discussing/comparing them CREATE TABLE #t (id int identity (1, 1), f1 nvarchar(20 ) ) CREATE TABLE #ids ( id int ) INSERT INTO #t ( f1 ) SELECT N'AAAA' INSERT INTO #ids SELECT @@ID

Categories : Sql Server

Generate UPDATE queries from results of SELECT queries
The closest thing in pgAdmin is the query tool (see http://www.pgadmin.org/docs/1.16/query.html). This would not take your select statements and turn them into queries, but you can graphically build queries if you don't want to parse and concatenate. If this is going to be a big, repetitive task, I would look at writing a Perl script to parse a query and rewrite it as needed. This would require some inside knowledge. It isn't clear what you want to do regarding updating the values so you'd have to design your solution around that. More likely you would want to write a functional API (a UDF) to do what you want, and then write calls to that, probably not in a config file directly (since it is not clear you can trust that) but through an interface.

Categories : Postgresql

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

Where should I do database queries and why am I not getting the results of the queries?
That's because the asynchronous nature of Node. Everything that has networking involved (DB queries, webservices, etc) is async. Because of this, you should refactor your selectalldate() method to accept an callback. From that callback you'll be able to render your template successfully with the data fetched from the DB. In the end, it'll be something like this: exports.selectalldate = function(callback) { connection.query('SELECT * FROM date', function (err, rows, fields) { if(rows.length > 0) { for(i = 0; i < rows.length; i ++) { rows[i].date = dateFormat(rows[i].date, "yyyy-mm-dd") } } callback(err, rows); }); } app.get('/dashboard', function(req, res) { db.selectalldate(function(err, datee) { if (err) { // Handle the error in s

Categories : Node Js

Optimizing sub-queries, making two queries become one
The main thing you seem to be checking is the last name with a leading % in the like. This renders the index on that column useless, and your SQL is searching for it twice. I am not 100% sure what you are trying to do. Your SQL appears to get all the members who match on name to the one required, then get the last registration_history record for those. The one you get could be from any one of the matching members, which seems strange unless you only ever expect to get a single member. If this is the case the following minor tidy (removing and IN and changing it to a JOIN) up will possibly slightly improve things. SELECT COALESCE(NULLIF(Registration_History.RegYear, ''), NULLIF(Registration.Year, '')) AS RegYear, COALESCE(NULLIF(Registration_History.RegNumber, ''), NULLIF(Registr

Categories : Mysql

Why would UPDATE with JOIN queries be so much slower than SELECT with JOIN queries?
Let's think about this for a minute. If you are selecting rows of a table (just grabbing them) versus updating every...single....row as you go through them, what would take longer? Reading n number of lines or modifying (updating) n number of lines? Compare it to reading 10 lines of a book versus writing the same 10 lines on a sheet of paper. Which one takes longer? May I also add that, the more rows you are reading vs updating, the bigger the difference will be. Just as there would be more of a difference in reading vs writing lines of a book, the more lines you were reading/writing.

Categories : Mysql

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#

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

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

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

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

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

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

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

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#

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



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