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

How to select columns and sum of columns using group by keyword from data table in c#
DtTest .AsEnumerable() .GroupBy ( x=> new { BNO = x.Field<int>("BNO"), INO = x.Field<int>("INO"), Desp = x.Field<string>("Desp"), Rate= x.Field<decimal>("Rate") } ) .Select ( x=> new { x.Key.BNO, x.Key.INO, x.Key.Desp, Qty = x.Sum(z=>z.Field<int>("Qty")), x.Key.Rate, Amount = x.Sum(z=>z.Field<decimal>("Amount")) } );

Categories : C#

DISTINCT keyword IN MYSQL backend in Django?
try shared_file = File.objects.filter(id__in = Share.objects.filter(user_id = log_id) .values_list('file', flat=True)) .annotate(count=Count('share__shared_user_id', distinct=True))

Categories : Mysql

MySQL: get distinct values from two columns and check whether they occur in two other columns of the same table
It is quite easy, you need only to check if a child have became parent... and because each family has two child you need to do it twice (once for child one and another for child two) and you should have the list of all child whom became parents. And after you have them in the list you only need to count them, see no need to complicate what is easy ;) select count (*) from ( select child1 from fam where child1 in (select parent1 from fam union select parent2 from fam) union select child2 from fam where child2 in (select parent1 from fam union select parent2 from fam) );

Categories : Mysql

adding DISTINCT keyword to query() with SQLite in Android
Instead of the... public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) ...method you're using, just use the... public Cursor query (boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) ...overload and set distinct to true. The Android docs seem a bit hard to direct link, but the doc page describing both is here.

Categories : Android

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

CSS equal columns
This fiddlejs may solve your problem : http://jsfiddle.net/Akinaru/kzngh/22/ Removed absolute from li a

Categories : CSS

Distinct count for two columns
You need a GROUP BY like this:- select Category, COUNT(*) as Count from YourTable GROUP BY Category; SQLFIDDLE

Categories : Mysql

Distinct on multi-columns in sql
If you wish to remove rows with all columns duplicated this is solved by simply adding a distinct in your query. select distinct cartlines.id,cartlines.pageId,cartlines.quantity,cartlines.price from orders INNER JOIN cartlines on(cartlines.orderId=orders.id)where userId=5 If however, this makes no difference, it means the other columns have different values, so the combinations of column values creates distinct (unique) rows. As Michael Berkowski stated in comments: DISTINCT - does operate over all columns in the SELECT list, so we need to understand your special case better. In the case that simply adding distinct does not cover you, you need to also remove the columns that are different from row to row, or use aggregate functions to get aggregate values per cartlines. E

Categories : SQL

SQL Server Distinct Columns
If you are using at least SQL-Server 2005 you can use ranking functions like ROW_NUMBER in a cte: WITH CTE AS( SELECT [SERV_ACCT], [ACCOUNT], [INV_DATE], RN = ROW_NUMBER() OVER (PARTITION BY SERV_ACCT ORDER BY INV_DATE DESC) FROM dbo.Table1 ) SELECT [SERV_ACCT], [ACCOUNT], [INV_DATE] FROM CTE WHERE RN = 1 DEMO Ranking Functions (Transact-SQL) OVER Clause (Transact-SQL) WITH common_table_expression (CTE)

Categories : SQL

Difference Between Columns, metadata, keyword , field
You may know that Meta Data is nothing but data about data. In share point context Meta Data can be used as list column also. Consider any excel documents which are classified using Phase , Category, Document Type etc. So when ever you see these columns you can identify what kind of document it is. As per my knowledge key words are nothing but taxonomy. By using these keywords you can classify the items. Site Column is different. Site Column can be used as list column in any list. For eg. Consider the Age. In your site you are using Age column repeatedly in your lists/libraries. So instead of creating Age column in each list you can create only one site column and you can add to required list. In simple words reusable purpose. Fields are nothing but a columns/site columns. These are re

Categories : Sharepoint

4 Equal Columns in one Row not working
OK. Now i have looking at the bhootstrap side and looking specially the example. I'm very sure that the layout i posted do it with col-lg, but now they do it with span. OK i'm check my bootstrap css and look, the css from yesterday have include the col and the css from a moment have the span. FINAL: i took the solution with the col, i have seen there is another col, the name is "col-3" thats define with 25% and thats perfect for my problem.

Categories : Twitter

Compare 2 columns in 2 tables with DISTINCT value
It looks like the USER_ID value is the concatenation of your ORG_ID and something to make it unique. I'm assuming this is from a COTS product and nothing a human would have built. Your desire is to find out how many entries there are by department. In SQL, when you read the word by in a requirement, that implies grouping. The action you want to take is to get a count and the reserved word for that is COUNT. Unless you need something out of the TBL_ORG, I see no need to join to it SELECT LEFT(T.USER_ID, 3) AS USER_CREATED , COUNT(1) AS GroupCount FROM TBL_USER AS T GROUP BY LEFT(T.USER_ID, 3) Anything that isn't in an aggregate (COUNT, SUM, AVG, etc) must be in your GROUP BY. SQLFiddle I updated the fiddle to also show how you could link to TBL_ORG if you need an elemen

Categories : SQL

Distinct Row values as Columns Sql Server
You should be able to use the PIVOT function to get the result: select jobid, hr, manager, employee from yourtable pivot ( max(jobvalue) for jobtype in (hr, manager, employee) ) piv; See SQL Fiddle with Demo. If you want to list the jobvalue under each jobType without showing the jobid, then you could use: select hr, manager, employee from ( select jobtype, jobvalue, row_number() over(partition by jobtype order by jobid) rn from yourtable ) d pivot ( max(jobvalue) for jobtype in (hr, manager, employee) ) piv; See SQL Fiddle with Demo

Categories : Sql Server

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

Select non distinct rows from two columns
You are using SQL Server, so this is easier than in Access: select A, B, C from (select t.*, count(*) over (partition by A, B) as cnt from t ) t where cnt > 1; This use of count(*) is as a window function. It is counting the number of rows with the same value of A and B. The final where just selects the rows that have more than one entry.

Categories : SQL

Entity framework distinct but not on all columns
I'd be concerned with this approach, even if it was possible distinct would then remove one of the items and leave you with random date out of the two, and then your sort would be totally unpredictable.

Categories : C#

Split Array Into 3 Equal Columns With PHP
You can use array_chunk to split an array into pieces $cols = array_chunk($businessnames, ceil(count($businessnames)/3)); foreach ($cols as $businessnames){ echo "<ol class='col'>"; foreach($businessnames as $b) { $theurl = get_term_link($b, 'businessnames'); echo "<li style='width: 31%;'><a href="" . $theurl . $append . "">". $b->name ."</a></li>"; } echo "</ol>"; } Alternatively you can use a pure css solution. echo "<ol style='column-count:3; -o-column-count:3; -moz-column-count:3; -webkit-column-count:3;'>"; foreach($businessnames as $b) { $theurl = get_term_link($b, 'businessnames'); echo "<li style='width: 31%;'><a href="" . $theurl . $append . "">". $b->name ."</a><

Categories : PHP

SQL: Count Records if All Columns Are Equal
To get the total orders with active numbers SELECT COUNT(*) FROM (SELECT * FROM products WHERE OrderNo NOT IN (SELECT * FROM products WHERE Status = 'Inactive' GROUP BY OrderNo)) as tmp_b; OrderNo To get only the order numbers SELECT OrderNo FROM (SELECT * FROM products WHERE OrderNo NOT IN (SELECT * FROM products WHERE Status = 'Inactive' GROUP BY OrderNo)) as tmp_b;

Categories : SQL

getting results of distinct columns in to a single column
Maybe with a subquery which concats all four columns as pairs and a GROUP BY on the result: SELECT X.NAME FROM ( SELECT Plastname + ', ' + Pfirstname AS NAME FROM People UNION ALL SELECT Clastname + ', ' + Cfirstname AS NAME FROM People ) X GROUP BY X.NAME ORDER BY X.NAME DEMO or even easier: replace the UNION ALL with UNION which eliminates duplicates: SELECT Plastname + ', ' + Pfirstname AS NAME FROM People UNION SELECT Clastname + ', ' + Cfirstname AS NAME FROM People DEMO

Categories : SQL

Concatenate columns with distinct values in oracle
I have not tested this but try the following :- with test1 as (select (TL.TankListUS || '||' || TL.TankListCanada || '||' || TL.TankListIndia) as str from TankListTL), test2 as (select regexp_substr(str,'[^|]+',1,rownum) split from test1 connect by level <= length (regexp_replace (str, '[^|]+')) + 1) select listagg(split,'||') within group(order by split) from test2 Do note that listagg was introduced in Oracle 11gR2. Following are some of the string aggregation techniques :- http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php#listagg

Categories : Oracle

Show count of columns distinct values
You need to use the iif function within the two additional columns. Here is some pseudo code to get you started. SELECT County ,sum(iif(TGTE = "L",1,0)) as [L_Count] ,sum(iif(TGTE = "H",1,0)) as [H_Count] FROM MyTable GROUP BY COUNTY;

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

Getting a distinct code-name pair but sorting on other columns
UPDATE Based on your updated requirements for Oracle SELECT CODE, NAME FROM ( SELECT CODE, NAME, ROW_NUMBER() OVER (ORDER BY SCOPE1, SCOPE2, SEQ) rnum FROM Table1 WHERE SCOPE1='Here' OR SCOPE2='Room' ) q GROUP BY CODE, NAME ORDER BY MIN(rnum) Here is SQLFiddle To make it work the same way in SQL Server SELECT CODE, NAME FROM ( SELECT CODE, NAME, ROW_NUMBER() OVER (ORDER BY CASE WHEN SCOPE1 IS NULL THEN 1 ELSE 0 END, SCOPE1, CASE WHEN SCOPE2 IS NULL THEN 2 ELSE 3 END, SCOPE2, SEQ) rnum FROM Table1 WHERE SCOPE1='Here' OR SCOPE2='Room' ) q GROUP BY CODE, NAME ORDER BY MIN(rnum) Here is SQLFiddle Output:

Categories : SQL

Count Number of Distinct Columns in a Row with tSQL
Select Name,Count(*) From (select Name,FieldA from test10 Union select Name,FieldB from test10 union select Name,FieldC from test10 Union select Name,FieldD from test10)as a Group by name

Categories : SQL

Return distinct rows where there is at least 1 discrepancy in certain columns
Join the table to itself to find other rows that match COL_A but have different COL_B or COL_C: select distinct t1.COL_A, t1.COL_B, t1.COL_C from MyTable t1 join MyTable t2 on t1.COL_A = t2.COL_A AND (t1.COL_B != t2.COL_B OR t1.COL_C != t2.COL_C) See this working on SQLFiddle If you want the rows ordered, add an order by at the end. With an index on (COL_A) this would perform very well. With a compound index on (COL_A, COL_B, COL_C) this would perform extremely well.

Categories : SQL

how to select distinct columns in join querys
This selects the distinct values. Is it what you want? SELECT distinct Tracking.GTIM FROM Tracking INNER JOIN Vehicles_Device_Rel ON Tracking.M = Vehicles_Device_Rel.Device_ID WHERE (Vehicles_Device_Rel.VehicalNumber = 'test')

Categories : SQL

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#

Select distinct and group by on subset of columns
You want to use the row_number() function: select t.ID, t.Street, t.City, t.PostalCode, t.ProjectID, t.Date, t.NameId from (select t.*, row_number() over (partition by Street, City, PostalCode, ProjectId order by id ) as seqnum from t ) t where seqnum = 1; This is a window function that assigns a sequential value to rows with the same values in certain columns (defined by the partition by clause). The ordering within these rows is determined by the order by clause. In this case, it starts ordering with the lowest id in the group, so the outer query just selects the first one.

Categories : SQL

Access 2010 - Select Distinct on two out of three columns
Tables and data; you should do this part yourself. create table table1 ( id int primary key, c1 char(6) not null, c2 char(5) not null, c3 char(4) not null ); insert into table1 values (1, 'bacct1', 'user1', 'log1'), (2, 'bacct1', 'user2', 'log2'), (3, 'bacct1', 'user3', 'log3'), (4, 'bacct2', 'user4', 'log4'), (5, 'bacct2', 'user4', 'log5'), (6, 'bacct3', 'user6', 'log6'), (7, 'bacct3', 'user7', 'log6'); It sounds like you're looking for something along these lines. select t1.id, t1.c1, t1.c2, t1.c3 from table1 t1 inner join (select c1, count(c1) from table1 group by c1 having count(c1) > 1) t2 on t1.c1 = t2.c1 inner join (select c2, count(c2) from table1 group by c2 having count(c2) = 1) t3 on t1.c2 = t3.c2 inner join (select

Categories : SQL

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

Find equal columns in different tables in mysql
I don't know how your table looks, so I would just write how I would do it (P.S there can be better ways) 1) select all the values from the first table. 2)make two loops, first to search from second table, and second to search from third table. 3) count number of rows. 4) if number of rows is more than one, you know you have duplicate.

Categories : PHP

Show records from two tables where columns is equal
I have EID's (2,3,4) under my account select distinct B.A, B.B, A.EID from TableB B inner join TableA A on A.EID=B.DEP where A.EID in (2,3,4) SQL FIDDLE

Categories : Mysql

mongoid query collection where two columns are equal to each other
If you provide a string to Mongoid's where(), it assumes you're using JavaScript, and triggers MongoDB's native $where, which is what you need: Parent.where("this.name == this.child_name")

Categories : Ruby On Rails

get the average from two distinct columns joining other tables in MySQL
This is your answer. but you should conside now month is aug not march b/c i used now(). okey. so change your table data accordingly and check the result. select AVG( loc) as loc,AVG(qty) as qty from tbl1 where month NOT IN (concat(UCASE(DATE_FORMAT(date_add(now(), INTERVAL -1 MONTH),'%b')),',', UCASE(DATE_FORMAT(date_add(now(), INTERVAL -2 MONTH),'%b')),',', UCASE(DATE_FORMAT(date_add(now(), INTERVAL -3 MONTH),'%b')))) group by type For DEMO Click here:-http://sqlfiddle.com/#!2/2a15e/15

Categories : Mysql

SELECT rows for each 2 DISTINCT columns in a Table in SQL Server
You can use Pivot functionality for the above mentioned scenario. Please find the implementation below: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Categories : SQL

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

How to write query to retrieve all columns and distinct for one column?
When working back the other way (an Excel Table range => C#) we found that the SpreadsheetGear loads the row of a table array into the header information of DataTable. We couldn't read the cell contents in the first row as table data. To get around this we leave the first row of a table extracted from a spreadsheet as blank then start extracting data from the second row and below. Perhaps your problem is the reverse of this issue - DataTable (and the SpreadsheetGear API) want you to fill the first row with column heading information and then populate data in subsequent rows.

Categories : SQL

How to select last entry for one distinct pairing of two columns in Oracle?
You can do this with the row_number() function: select raw_value, normal_value, timestamp from (select myt.*, ROW_NUMBER() over (partition by id, type order by timestamp desc) as seqnum from mytable myt ) tmp where seqnum = 1 and id = 1 and type = 3; row_number() is an analytic function (aka window function) that assigns sequential numbers to rows. Every group defined by id, type gets its own numbers. The first row is the one with the most recent timestamp (order by timestamp desc). The outer select chooses this row in the where clause. In the case of ties, this version returns only one row. To get all the rows, use rank() instead of row_number().

Categories : SQL

SQL Server show only rows (all columns) with a distinct column name
Use GROUP BY: SELECT RowID, OrderNum, cDescription, Thickness, etcetera ... FROM dbo.TableName GROUP BY RowID, OrderNum, cDescription, Thickness, etcetera ... ( etcetera is a placeholder for the rest of your columns )

Categories : Sql Server



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