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

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

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

Concatenate single column from multiple rows into single column seperated by ',' in Oracle 10g?
Tim Hall has a page that lists a variety of string aggregation techniques. I personally prefer the user-defined aggregate function but there are a number of other techniques that work as well.

Categories : Oracle

Nested Single Query for concatenation of specific column in multiple row in single column
Your data structure is not normalised. Unless a student can only be on one course ever. This is bad and should be corrected. Apart from that, you are trying to emulate a group concat function. This is one approach. SELECT courses.*, LEFT(students , LEN(students)-1) AS students, case when (select count(*) from @studentcourses where CourseId = courses.CourseId)>=3 then 'y' else 'n' end FROM courses CROSS APPLY ( SELECT lastname + ' ' + firstname + ',' FROM studentcourses WHERE courses.CourseId = studentcourses.CourseId FOR XML PATH('') ) t (students)

Categories : SQL

SUM over column based on distinct row groups selected for another column
Use a correlated subquery: select count(distinct SAMPNAME), (select sum(ss) from (select distinct SAMPNAME, SAMPSIZE as ss from TEST_TABLE as T2 where T2.TC_ID = T1.TC_ID) ), TC_ID from TEST_TABLE as T1 group by TC_ID

Categories : SQL

Select Distinct Column For Each Value In Another Column Then Group By
Is this what you are looking for: SELECT MIN(id), number, package_id, date FROM MyTable GROUP by number, package_id, date It certainly satisfies your expected result set.

Categories : Mysql

how to select a row based on a single distinct value
GROUP BY Name and use any aggregate function with the value MIN or MAX since you don't care about the value of it: SELECT Name, MIN(value) FROM table GROUP BY Name

Categories : Sql Server

Select one value of column, corresponding with distinct value of other column
In your desired output, the ordertouserId = 2 shows the Total_value for voucherId = 3. How did you determine that you don't want to show the Total_value for voucherId = 2? Sorry, that's not an answer, but I don't have enough "Reputation" to post a comment on the question. Baring a better understanding of how you selected Total_value = 500 (rather than Total_value = 300) for ordertouserId = 2, my answer would have to be: The output you desire is not possible to obtain because there is no clear way to determine which voucherId should be used in calculating Total_value where ordertouserId = 2. BTW, I think there's an error in the first output table which shows ordertouserId = 2 with Total_value = 1000. Shouldn't the TotalValue for ordertouserId = 2 be 300 + 500 = 800?

Categories : Mysql

Find all distinct alternate usernames by an ip in a single query
How does this work for you? http://sqlfiddle.com/#!2/133c6/1 SELECT DISTINCT joins2.player_id, ips.ip FROM joins JOIN ips ON ips.ip_id = joins.ip_id JOIN joins AS joins2 ON joins2.ip_id = ips.ip_id AND joins2.player_id != #player_id WHERE joins.player_id = #player_id

Categories : Mysql

How to merge two text (.txt) file into single one and distinct the same content?
You could read both textfiles, save the data as lists of Tuple<int, int, List<double>> (where you have the tuples "unique" in that sense that there are no two tuples sharing the two ints). Then output a textfile into which you print all the Tuples in your format. This accomplishes what I think you want to do. List<string> filelist = new List<string>() { "text1.txt", "text2.txt" }; List<Tuple<int, int, List<double>>> dataList = new List<Tuple<int, int, List<double>>>(); foreach (var file in filelist) { string line; using (TextReader tr = new StreamReader(file)) { tr.ReadLine(); //skip header while ((line = tr.ReadLine()) != null) { var tokens = line.Split(new char[] { ',', ' ' },

Categories : C#

Selecting multiple values of other column basing on distinct values of column on same table
You can use the LISTAGG function as of Oracle 11g R2. e.g. SELECT deptno , LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno;

Categories : SQL

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

Not able to get DISTINCT column values :
I wasn't be able to test it, but apart from possible minor syntax adjustment, this should help you: $visited = Visit::model()->findAllByAttributes( array('to_user_id'=>Yii::app()->user->id), array('distinct' => True) ); According to the doc, findAllByAttribute accept a condition or criteria parameter as second argument. If this is an array (according the doc for find()), it will serve to initialize the various criteria properties of the requests.

Categories : PHP

How to COUNT DISTINCT on more than one column
I don't know if it's the best way, but I normally concatenate the two values, using a delimiter to enforce "distinctness", so they become one expression, which Oracle can handle with COUNT DISTINCT: SELECT "group_id",count(*) , count(distinct "p_id" || '-' || "version") FROM tbl group by "group_id"

Categories : SQL

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;

Categories : Dotnet

MYSQL SELECT DISTINCT value from and column and its corresponding value from another
You can use a GROUP BY query to reduce the result to one row per distinct category: SELECT category, path FROM galleryinfo GROUP BY category The problem with this is that it's arbitrary which path this query returns. In fact, in most SQL implementations, this is an illegal query and results in an error. MySQL is more lenient, but it will return an arbitrary path from those matching the category (in practice, it chooses the path from the first row with respect to physical storage, but this is not documented or guaranteed). You can choose either the MIN(path) or MAX(path), or concatenate all paths per category together into one string with GROUP_CONCAT(path), but that's about it.

Categories : Mysql

Select distinct rows with max value of another column
Yes, you can select the top 1 docname. Replace your field selection list with this: SELECT distinct d.DOCNUM ,h.version ,DOCNAME = (SELECT TOP 1 d2.DOCNAME FROM DOCUMENT d2, DOCHISTORY h2 WHERE d2.DOCNUM = d.DOCNUM and h2.DOCNUM = d.docnum) ,P.FolderName ....(rest of code) In the above output, doc 123 version 9 should appear as Article.

Categories : SQL

SQL: Display Distinct Record From A Column
I think select distinct * from <your table> will work for you In your case with different times, you could use: select distinct LogID, OrderNo, cast(MaxDate as date) as MaxDate, AnotherDate, Status from <your table>

Categories : SQL

indexing distinct values for a column in SQL
See SQL Fiddle -- set up tables create table Authors ( id int(11) not null auto_increment, name varchar(64), primary key(id) ); create table Books ( id int(11) not null auto_increment, name varchar(64), author varchar(64), primary key(id) ); -- populate Authors table, giving each author a unique ID insert into Authors (name) select distinct author from Books; -- Add an author_id column to the Books table alter table Books add author_id int(11) after author; -- Update the Books table, filling in the author_ids update Books b, Authors a set b.author_id = a.id where b.author = a.name; -- If you don't care to retain the author name to author ID mapping, -- you can then drop the Authors table.

Categories : Mysql

SQLalchemy distinct, order_by different column
you never need to "re-loop" - if you mean load the rows into Python, that is. You probably want to produce a subquery and select from that, which you can achieve using query.from_self().order_by(asc(Order.position)). More specific scenarios you can get using subquery(). In this case I can't really tell what you're going for. If a customer has more than one Order with the requested vehicle id and date, you'll get two rows, one for each Order, and each Order row will refer to the Customer. What exactly do you want instead ? Just the first order row within each customer group ? I'd do that like this: highest_order = s.query(Order.customer_id, func.max(Order.position).label('position')). filter(Order.oh_reqdate == date_q). filter(Order.vehicle_

Categories : Python

SQLite - SELECT DISTINCT of one column and get the others
Assuming you are using MySQL (as the question is tagged), the following will return an arbitrary value for the other columns: select * from t group by code; However, the particular values being selected come from indeterminate rows.

Categories : IOS

Select distinct by unique column
Try this query, without unique index/constraint - Query: DECLARE @temp TABLE ( Col1 VARCHAR(10) , Col2 VARCHAR(10) , Col3 VARCHAR(10) , Col4 VARCHAR(10) ) INSERT INTO @temp (Col1, Col2, Col3, Col4) VALUES ('1', '1', '1', '1'), ('11', '2', '3', '44'), ('111', '2', '3', '444'), ('1111', '3', '3', '3') SELECT Col1 = MIN(Col1) , Col2 , Col3 , Col4 = MIN(Col4) FROM @temp GROUP BY Col2 , Col3 Output: Col1 Col2 Col3 Col4 ---------- ---------- ---------- ---------- 1 1 1 1 11 2 3 44 1111 3 3 3

Categories : Sql Server

Update column with distinct values
This will get you the id|name combinations from the source table where the id appears only once: select id, name from source where name is not null and name <> '' -- use trim() too if appropriate group by id having count(name) = 1 and assuming your target table is empty you can just insert those rows. You can then remove those inserted rows from the source table. What remains after that in your source table are the ids that appear there more than once, and names that are null, and they have to be cleaned up first, i.e. reduced to a set of unique (id,name) combinations. How to do that would depend on the varieties of garbage in the source table (e.g. nulls, zero-length strings, leading/trailing spaces, misspellings and corrections, exact duplicates, etc). P.S. substitute th

Categories : Sql Server

SQL/Hive count distinct column
Yes, it is almost correct. But you have one simple mistake. Your column name is wrong inside COUNT. SELECT columnA,columnB,COUNT(DISTINCT columnC) No_of_distinct_colC from table_name group by columnA,columnB

Categories : SQL

SELECT DISTINCT one column but return all
If you are concerned you can use a nested query. Select distinct titles and uses the results in the where clause of the outer query. e.g. SELECT * FROM Customers WHERE title IN (SELECT DISTINCT title FROM titles WHERE title > 'X');

Categories : SQL

Adding a single row with a single column of one datagridview into another datagridview of multiple column
Hope this helps: private void dgv1_CellContentClick(object sender, DataGridViewCellEventArgs e) { if (e.ColumnIndex == 3) { int newRow = dgv2.Rows.Add(); dgv2.Rows[newRow].Cells[0].Value = dgv1.Rows[e.RowIndex].Cells["Stringtext"].Value; } } This should take the value from the StringText cell and place it in the first column of a newly created row in the second grid when the button in cell 4 is clicked in the first grid. EDIT: private void dgv1_CellContentClick(object sender, DataGridViewCellEventArgs e) { if (e.ColumnIndex == 3) { dgv1.Rows[e.RowIndex].Cells["Stringtext"].Value = dgv2.Rows[{y}].Cells[0].Value; } } This one should set the value of the cell "StringText"(which can be changed to whatever yo

Categories : C#

Displaying distinct datetime column in dropdownlist
This is likely happening because you are running the query in another tool that formats text the way you expect. When you run it in your app, it uses the data locale by default (unless otherwise specified) You will get the desired result if you change your select statement to: select distinct DATE_FORMAT(DateTimeColumn,'%Y-%m-%d %H:%i:%S.%f') from tablename1

Categories : C#

Sql Server Xml Value method -- Distinct Values for one column
It is your cross apply with your nodes statement listed twice that is showing this problem. Do what you are doing with the 'nodes' syntax with a 'query' extension instead followed up by a 'value' extension to show what is in the xml directly from extension instead of relying on the nodes with a cross apply. The problem is you are not displaying to the audience where you get that Id from? Are you determining that at run time from the xml itself or joining yet to another table or having another part of the xml not present? What in essence that is happening with the nodes is it is cross applying and saying: "I have two vales in that node heirarchy here they are." Then you are cross applying again a different node and it is returning the same thing twice. You must be careful when using c

Categories : Sql Server

MySQL - distinct on only one column - only display FIRST duplicate row
try this select * from table1 group by account_id demo here this will give you the first of every account_id

Categories : PHP

SQL Select rows based on distinct column value
You've selected the string PostCode and Country not the actual columns. SELECT POSTCODE as 'billing_postcode', COUNTRY as 'Title', billing_country_id FROM [DB].[dbo].[Order] INNER JOIN [DB].[dbo].[Country] ON country_id = billing_country_id GROUP BY billing_country_id From your edit it seems that you need to switch the columns/alias around: SELECT billing_postcode AS 'POSTCODE', Title as 'Country', billing_country_id FROM [DB].[dbo].[Order] INNER JOIN [DB].[dbo].[Country] ON country_id = billing_country_id

Categories : C#

Select Distinct value from column and return all rows
In most databases, you can do: select t.* from (select t.* row_number() over (partition by field order by field) as seqnum from t ) t where seqnum = 1

Categories : SQL

mysql query group by where column distinct
It should look something like this SELECT COUNT(DISTINCT e.IP), count(e.id), count(c.id), e.Nid FROM tbl1 e LEFT JOIN tbl2 c ON e.Uid = c.Uid where e.at > '".$from."' AND e.at < '".$to."' GROUP BY e.Nid

Categories : Mysql

Select distinct column, and a count of the entries
CREATE TABLE tbl (`email` varchar(10)); INSERT INTO tbl (`email`) VALUES ('a@b.com'), ('b@b.com'), ('c@b.com'), ('d@b.com'), ('e@b.com'), ('a@b.com'), ('b@b.com'), ('c@b.com'), ('c@b.com'); SELECT email, COUNT(*) FROM tbl GROUP BY email; Result | EMAIL | COUNT(*) | ---------------------- | a@b.com | 2 | | b@b.com | 2 | | c@b.com | 3 | | d@b.com | 1 | | e@b.com | 1 | See a demo

Categories : Mysql

DISTINCT clause on string column in mysql
You are not getting a duplicate "001 Towing", the distinct is applied to each group generated by the group by clause. Therefore, you get 4 x "001 Towing" one for each "Sp_Id"; if you remove the group by, you will get only one "001 Towing". You made the wrong question to the database, and the database gave you the right answer to your wrong question. If you tell us what the query is supposed to do, we might be able to help you fix it... for your new specification try this query, it should do the trick: SELECT Sp_Name as Sp_Name, CONCAT('SP_', **group_concat ( Sp_Id separator '_ ' )** ) as searchPayerId FROM ServiceProviders WHERE Comp_Id= 3 AND Sp_Id NOT IN (1,2) AND Sp_Name != '' **GROUP BY Sp_Name** BTW you can look in the mySQL manual how the Group By works

Categories : Mysql

Get distinct rows in Gridview based on One column
Distinct accepts a EqualityComparer<T> as it's parameter. You can implement requested functionality using following comparer: public class JobComparer : EqualityComparer<Job> { public override bool Equals(Job x, Job y) { return x.JobID == y.JobID; } public override int GetHashCode(Job obj) { return obj.JobID.GetHashCode(); } } With this comparer, simply use Distinct(new JobComparer()) on your collection.

Categories : C#

Hive - counting distinct CSVs in one column
You can use explode to output separate rows for each category and then count distinct to get the result you are looking for. Something like this. SELECT id, COUNT(DISTINCT(cat)) as numDistCategories FROM ( SELECT id, EXPLODE(listOfcategories) AS cat FROM myTable) a GROUP BY id; Hope that helps.

Categories : Csv

Conversion of pipe delimited single column data to multiple column matrix - R
This works on the supplied data. First read in your data: pipdat <- read.table(stdin(),header=TRUE,stringsAsFactors=FALSE) ID DATA 1 a 2 a|b 3 b|c 4 d|e # leave a blank line at the end so it stops reading Now here goes: nr <- dim(pipdat)[1] chrs <- strsplit(pipdat[,2],"[|]") af <- unique(unlist(chrs)) whichlet <- function(a,fac) as.numeric(fac %in% a) matrix(unlist(lapply(chrs,whichlet,af)), byrow=TRUE,nr=nr,dimnames=list(ID=1:nr,af)) (That can be done in fewer lines, but it's handy to see what some of those steps do) It produces: ID a b c d e 1 1 0 0 0 0 2 1 1 0 0 0 3 0 1 1 0 0 4 0 0 0 1 1 I guessed from your post that you wanted ID as row names; if you need it to be a column of data that last line needs to be different. I'd have used s

Categories : R

Query To Return Multiple Values From A Single Column Based On Value Of Another Column
You can join to the same table twice. select startDt.EntryDate as StartDate, endDt.EntryDate as EndDate from table startDt inner join table endDt on startDt.id = endDt.id where startDt.ChecklistDay = 1 and endDt.CheckListDay = 10

Categories : SQL

VBA - Find all values on column B for article on column A and place them to a single row
Here is a recipe without using VBA build aggregated TIMES for each article ARTICLE TIME SAME TIMES article 1 1s =if(A3=A2;"YES";"") =B2 article 1 2s =if(A3=A2;"YES";"") = if(A3=A2;Concatenate(B3;" ";D2);B3) article 1 3s =if(A4=A3;"YES";"") = if(A4=A3;Concatenate(B4;" ";D3);B4) article 2 1s (extend the formulas down to the end of range) Create a copy, replacing the formulas in SAME and TIMES by the result select columns C and D menu -> copy select columns C and D agin menu -> paste special ; values only Sort the list by "SAME" (this will put all "YES" in a single block) Remove all SAME="YES" columns Sort back to ascending list of articles Remove "TIME" and "YES" columns Done.

Categories : Excel



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