FAQ Database Discussion Community


Replacing Distinct() with GroupBy(…) in Linq

c#,performance,linq,distinct
To get a performance gain, I am trying to replace the call to Distinct() below with GroupBy(...), however I am getting errors. Any suggestions on how this query should be rewritten to use GroupBy(...)? I am trying to use GroupBy() on the Description field itself. var result= GetResults() .Select(x =>...

Getting Distinct value in Python

python,mysql,list,distinct
I have a list [<Storage {'CaseID': 45L, 'PatientProfile_ID': 3L}>, <Storage {'CaseID': 46L, 'PatientProfile_ID': 2L}>, <Storage {'CaseID': 45L, 'PatientProfile_ID': 3L}>, <Storage {'CaseID': 45L, 'PatientProfile_ID': 3L}>, <Storage {'CaseID': 46L, 'PatientProfile_ID': 2L}>, <Storage {'CaseID': 45L, 'PatientProfile_ID': 3L}>, <Storage {'CaseID': 46L, 'PatientProfile_ID': 2L}>, <Storage {'CaseID': 45L, 'PatientProfile_ID': 3L}>, <Storage {'CaseID': 45L, 'PatientProfile_ID': 3L}>, <Storage...

Oracle Sql : distinct value in a specific field [duplicate]

sql,oracle,distinct
This question already has an answer here: How to select records with maximum values in two columns? 2 answers I have the following table : **Country Name Number** us John 45 us Jeff 35 fr Jean 31 it Luigi 25 fr Maxime 23 ca Justin 23 This table is...

Most efficient way to remove duplicates from a List

c#,list,distinct
Let's say I have a List with duplicate values and I want to remove the duplicates. List<int> myList = new List<int>(Enumerable.Range(0, 10000)); myList.Add(1); myList.Add(2); myList.Add(2); myList.Add(3); I have found 3 approaches to solve this: List<int> Result1 = new HashSet<int>(myList).ToList(); //3700 ticks List<int> Result2 = myList.Distinct().ToList(); //4700 ticks List<int> Result3 =...

Stop returning multiple similar rows on joins

sql,sqlite,select,join,distinct
I have the following scenario in SQLite. TableA ID ----- 1 | 2 | 3 | Table B ID | AID |Tag ---------------- 1 | 1 | Hide 2 | 1 | Show 3 | 2 | Null 4 | 3 | Show Table B has column AID which is...

filling combo box using LINQ query (distinct)

c#,linq,combobox,distinct
I have a combocox the name is "cmbModel" I want to fill the database with two different values in a table. This is what I have done: private void Form1_Load(object sender, EventArgs e) { using (LINQSQLDataContext db = new LINQSQLDataContext()) { cmbModel.DisplayMember = "szModel"; cmbModel.DataSource = db.VehEcus.ToList<VehEcu>(); } } this...

Count distinct values subject to condition in Calculated Field

excel,if-statement,count,pivot,distinct
How can I create a Calculated Field in a PivotTable that will count distinct values of Field1, given that Field2 meets a particular condition? My limitations: Needs to be in a PivotTable Needs to be a Calculated Field The Calculated Field needs to be a unique count "formula", not simply...

Count Distinct Number of Email Addresses in the Results of a Query in MS Access

ms-access,count,distinct
I have a query in MS Access to return multiple columns from multiple tables for a user-specified period of time. This is for a training registration database, and often times, people take multiple classes during a given time period. I need to get a count of the distinct number of...

Howto select (almost) unique values in a specific order

sql,order,distinct
In a trip there are several stops, (a stop = an adress whereone or multiple orders are loaded, or delivered), in a specific order. For example: Trip A Trip_order Action Place Ordernumber 10 Load Paris 394798 20 Load Milan 657748 30 UnLoad Athens 657748 40 Unload Thessaloniki 394798 50 Load...

MySQL Conditional count based on a value in another column

mysql,count,distinct,condition
I have table that looks like this: id rank a 2 a 1 b 4 b 3 c 7 d 1 d 1 e 9 I need to get all the distinct rank values on one column and count of all the unique id's that have reached equal or higher...

MySQL: Join distinct rows of two tables in a certain order?

mysql,join,distinct
I have a list of inventory units and sale transactions that I want to, (1) join by unit SKU, and (2) associate ONE transaction to ONE inventory unit in first-in-first-out order by date. I'm having trouble with the second part. The best I can come up with is: SELECT `units`.`unit_date`,...

MySQL Select unique sorted field values

mysql,select,distinct
I've trying to concatenate the values of 2 GROUP_CONCAT( columns ) from a single table that's been joined twice, then get the unique items from the list. I can do all this outside of my query but if possible it would be nice to just pull the data from the...

SQL DISTINCT id when SUM weight in sub query

sql,sql-server,distinct
I have two types of sub query's in the statement. First of all some sample data. Table CAT ID Weight GROUP 1 1 200 A 1 2 300 B 1 3 250 B 1 1 200 A 1 4 200 A One sub query is a count of distinct IDs...

Count rows on distinct select with multiple columns

hibernate,distinct,hibernate-criteria
I have been searching for some time but not found enough answers to do this myself. I have an MsSQL that looks like this: select count(*) from ( select distinct supplierName, supplierNr from dbo.InvoiceTypeBean ) as a This returns what I want using pure SQL But i need this in...

TaffyDB - combine methods

javascript,distinct,stringify,taffydb
I'm really new to TaffyDB and JavaScript. How can I combine methods in TaffyDB? I'm trying to use distinct () and stringify(), which is not working var xyz = db().distinct("mnths").stringify (); console.log(xyz ); I get an error stating that stringify() is not supported. Thank you....

Distinct() How to find unique elements in list of objects

c#,linq,distinct
There is a very simple class: public class LinkInformation { public LinkInformation(string link, string text, string group) { this.Link = link; this.Text = text; this.Group = group; } public string Link { get; set; } public string Text { get; set; } public string Group { get; set; } public...

SQL Select Distinct from Table

sql,sql-server-2008,distinct
I have the following statement which works just fine: select 'the file type is:', FileType from table1 I would like to use DISTINCT with this statement, something like: select 'the file type is:', distinct FileType from table1 but I get the error: Incorrect syntax near the keyword 'distinct'. I am...

Linq Group by / Distinct with Join table

linq,join,group-by,distinct
i plan to join 2 table, and get the distinct value of language column. How should i achieve that in Linq? I try add 'group' but no luck. Besides, i want to select s value too together with r distinct language value. My code: public ActionResult QuestionLink(int Survey_ID) { var...

SQL server duplicates, results with XY, YX duplicates

sql,duplicates,distinct,multiple-columns
i have table containing some information about some companies. the table contain this columns. Connectionkey|Phonenumber|Lastname|Firstname|Categoryid1|housenumber|Streettype|Street|Zipcode|Locality|Createts|lastuserid I am trying to extract duplicate rows based on some columns SELECT Dup.Connectionkey, Dup.Phonenumber, Dup.Lastname, Dup.Firstname, Dup.Categoryid1, Dup.housenumber, Dup.Streettype, Dup.Street, Dup.Zipcode, Dup.Locality, Dup.Createts, Dup.lastuserid, Dup1.connectionkey, Dup1.Phonenumber, Dup1.Lastname, Dup1.Firstname,...

Fastest way to PostgreSQL Distinct and Format

sql,postgresql,distinct,aggregate,postgresql-performance
I have a 3.5 million rows in table acs_objects and I need to retrieve column creation_date with year only format and distinct. My first attempt : 180~200 Sec (15 Rows Fetched) SELECT DISTINCT to_char(creation_date,'YYYY') FROM acs_objects My second attempt : 35~40 Sec (15 Rows Fetched) SELECT DISTINCT to_char(creation_date,'YYYY') FROM (SELECT...

Distinct() method does not work?

linq,distinct,duplication
I tried to use Distinct() to filter my collection to prevent duplication but my linq query still adds the same values to list. thanks in advance. public ObservableCollection<string> CollectTopicsFromXml() { ObservableCollection<string> oc = new ObservableCollection<string>(); XDocument xDoc = XDocument.Load(path); var topicColl = xDoc.Descendants("topic").Distinct(); foreach (var topic in topicColl) { oc.Add(topic.Value);...

how to count number of distinct values of a field from two collections in mongodb

mongodb,distinct
I have to mongoDB collection A and B. Both of them have the field 'user'. I would like to know the number of distinct users in A, B, and (A + B). Pseudo code would be like the following: A.user.distinct().count() B.user.distinct().count() (A.user union B.user)..distinct().count() Can someone give some suggestions?...

DISTINCT results in mysql

php,mysql,distinct
What i need is to retrieve the uid that does not have zip 17 in his list. ------------- uid | zipcode ------------- 2 | 17 2 | 30 7 | 18 7 | 17 9 | 20 SELECT uid, zip FROM nt_shipping WHERE zip != 17 GROUP BY uid; does...

What is difference between selecting users.* and selecting each individual columns?

ruby-on-rails,postgresql,join,include,distinct
I'm working on studying Ruby on Rails, and have some specific questions about rails active records and their SQL conversions. FYI, I'm using postgresql, and user model has many statuses, and I wanted to order users based on created_at column of statuses. Although I found out the solution, User.includes(:statuses).order('statuses.created_at desc'),...

SQL Show All column by one column distinct

sql,oracle,select,duplicates,distinct
I have a table with duplicate item's ... I need show the list of all columns without duplicate item's for example i have this table: ID CODE RANK TIME 1 12345 2 10:00 2 12345 2 11:00 3 98765 3 20:00 4 98765 3 22:00 5 66666 2 10:00 6...

Issue in sql server with join query

sql-server,inner-join,distinct
I have four tables for join I am trying to join with views in sql server. i have successfully done join query and retrieving data from multiple table with join query. But I Execute the same query sql server shows the different result every time. SELECT DISTINCT dbo.tbl_verifyFinger2.ID , dbo.tbl_verifyCnicDetails.fID...

Distinct function somehow not giving back all types

distinct,orient-db,result
Feel free to check it on my latest project: http://arda-maps.org:2480 arda arda as login. Now check select distinct(type) from Location Here you get 8 records (River,Lake,Region,City,Island,House,Mountain,Hill). But actually there are way more... To show you that the distinct somehow is not giving back all distinct we search for one specific...

Return only one record of distinct item with multiple unique locations

distinct
I am not sure if this could even be done.. but I really only want an item showing up with 1 location to pull stock from rather than two in the mix causing a double pull of inventory... preferably the location with MIN qty, BUT some items have multiple locations...

Oracle sql distinct query

sql,oracle,distinct
I need to retrieve the distinct values of both the column and row. The oracle sql query in the current setup is given below: select distinct ym.wh_id, ym.trlr_num, ym.arrdte, ri.invnum, ri.supnum from rcvinv ri, yms_ymr ym where ym.trlr_cod='RCV' and ri.trknum = ym.trlr_num and ym.wh_id <=50 and ym.trlr_stat in ('C','CI','R','OR') and...

SQL Server - Count the number of times the contents of a specified field repeat in a table

sql-server,count,distinct
What's the best way to 'SELECT' a 'DISTINCT' list of a field from a table / view (with 'WHERE' criteria) and alongside that count the number of times that that field content repeats in the table / view? In other words, I have an initial view that looks a bit...

Select insert with sequence and distinct

sql,oracle,select,insert,distinct
I'm trying to perform a select insert where I need to use a sequence and a distinct clause. I know we can't use sequences with distinct so, I've nested a subquery inside the FROM, like this: INSERT INTO SchemaName.XXSON_ST_B2B_CTR_PRICING (SEQ_NO,SEEB_KEY,MESSAGE_ID,MESSAGE_TYPE,CREATE_DTIME,CHANGE_DTIME,STATUS) SELECT XXSON.XXSON_B2B_CTR_PRICING_SEQ.NEXTVAL AS SEQ_NO, 'SESS_NO' AS SEEB_KEY, srp.ITEM AS MESSAGE_ID,...

SQL How to select distinct value from multiple fields

sql,ms-access,select,field,distinct
In the database(of tutor profiles), each tutor can teach up to 3 subjects, hence I've created 3 fields--Subject1,Subject2 & Subject3-- for each tutor. In my case, I would like to retrieve each specific subject within the 3 fields and add them to my combo box in my program for a...

Record count after distinct

sql,oracle,count,distinct
I am using following query for the pagination SELECT * FROM ( SELECT a.*, rownum row_num FROM ( select distinct field1, field2, ... COUNT(*) OVER () RESULT_COUNT from table1 where condition1 order by someField desc )a WHERE rownum < :maxRow )WHERE row_num > = :minRow; I am getting result_count=294 (which...

How to use distinct when you select multiple column in SQL

sql,sql-server,sql-server-2008,distinct,common-table-expression
I have use simple inner join statement and getting result into CTE table. I want to select distinct 'ServiceId' from CTE. I have following query SELECT DISTINCT(ServicesId), ServiceNo, ServiceDate, DealerCode FROM CTE_Temp Suppose there are duplicate entries of ServiceId in CTE then I want to select first entry only and...

Spark: How to translate count(distinct(value)) in Dataframe API's

count,apache-spark,distinct,dataframes,apache-spark-sql
I'm trying to compare different ways to aggregate my data. This is my input data with 2 elements (page,visitor): (PAG1,V1) (PAG1,V1) (PAG2,V1) (PAG2,V2) (PAG2,V1) (PAG1,V1) (PAG1,V2) (PAG1,V1) (PAG1,V2) (PAG1,V1) (PAG2,V2) (PAG1,V3) Working with a SQL command into Spark SQL with this code: import sqlContext.implicits._ case class Log(page: String, visitor: String)...

SQL - Select distinct on two column

sql,postgresql,sql-update,duplicates,distinct
I have this table 'words' with more information: +---------+------------+----------- | ID |ID_CATEGORY | ID_THEME | +---------+------------+----------- | 1 | 1 | 1 | 2 | 1 | 1 | 3 | 1 | 1 | 4 | 1 | 2 | 5 | 1 | 2 | 6 | 1...

SQL & SQL Server - Denormalized table, Distinct and Order By Complex Query

sql,sql-server,select,sql-order-by,distinct
I've been working lately in this and I couldn't work it out. I have the following table: ID Language Text ---- -------- -------- 1 spanish Hola 1 english Hello 2 spanish Chau 2 english Goodbye 2 french Au revoir 3 english Thank you I need to get each ID once...

SQL Query Distinct Count Issue

sql-server,count,distinct
I am trying to get the count of models are under each manufacturer, in a table I want to display something like this: Manufacturer Models Honda 7 Ford 12 With models being for example, accord, civic, etc... How exactly could I get that count? In the SQL table it is...

Issue with android spinners - Loading distinct values in spinners

android,spinner,distinct
I'm using two Spinners to show the items I'm getting from the json response. I have 2 problems right now. When u check my logcat u can see there are items repeating (Right side list, u can see so many pan). I want to have 1 item only once in...

How can I modify this SELECT query that return to me a set of rows that contains the same data to obtain a single row?

sql,database,oracle,oracle11g,distinct
I am not so into database and I am working on a project that perform this SELECT query on an Oracle database: SELECT coda.desc_unita1, coda.pk_coda, coda.stato, coda.fk_tipo_doc, coda.descrizione, coda.num_doc, coda.data_in as data_in, coda.indirizzamento, coda.segnalazione, coda.autorizza , coda.fornitore, coda.data_app, coda.motivo_rifiuto, coda.tot_fattura, coda.data_doc, coda.fk_piva_mittente, coda.fk_piva_destinatario FROM (SELECT d.rag_soc1 desc_unita1, a.pk_coda, a.fk_tipo_doc, b.descrizione,...

Mysql query create a view with join and distinct

mysql,view,distinct
i need some helps with a query that i can create correctly for what i want. i'm explaining all: accounts ( id, name, surname,email, telephone ..) with id p.k. boc (ad,username, port..) with ad p.k . Telephone and Username have the same values in their tables i need to create...

How to getting distinct values by linq or lambda?

c#,linq,distinct
I have a list of items, and i try to getting unique items by distinct keys. The class: class TempClass { public string One { get; set; } public string Two { get; set; } public string Key { get { return "Key_" + One + "_" + Two; }...

get distinct row and use coalesce if id not exist in other table

mysql,join,max,distinct,coalesce
I have two tables: Job_Order -id -creation_date -assigned_to -job_ordertype -client Job_Order_Stage -id -stage -date -job_order(foreign key to job_order) I want to get all rows in the job_order and set stage to 0 if id not exist in the Job_Order_Stage. And I also want to get only the row with max(stage)...

Using Distinct or Group by in SQL . Which one is better and faster? State it [closed]

sql-server,sql-server-2008,group-by,distinct,database-performance
I'm Just using the table which having more than 200 thousand records each.I need to Fetch Unique records by joining those tables .Which keyword would work faster in this case using Distinct or Group by ?.It should be looked from performance wise better and faster one.Kindly suggest me.

SQL: Combine Duplicate Rows And Case Statement Values in Final Data Output

sql,duplicates,subquery,case,distinct
I'm working on a SQL query that counts duplicate records that are based on a text field I am working with: where datasource = 'Web' or 'Internal'. I am currently using a case statement to count the number of times a record shows that value. My question is how do...

Selecting Distinct rows When there is a Logical OR with two columns

mysql,distinct,logical-or
As the title suggests I have a MySQL query like this: SELECT DISTINCT `friendly_url` FROM `post` WHERE `description` LIKE ? OR `heading` LIKE ? ORDER BY `friendly_url` I have given the string '%' wild card in the parameters so that this works as a Search function. How ever, Say a...

MongoDB - distinct and count, the simplest case

mongodb,count,distinct,mongolab
I have database XXX and collection YYY in MongoDB (using MongoLab). Here is a sample record: { "_id": { "$oid": "551a5asdfsdfsdfs" }, "_class": "com.test.com", "mvid": "d0fffsdfs" } I would like to retrieve all distinct values of the field "mvid" and count them. Here is what found on the internet: db.YYY.aggregate([{...

SELECT DISTINCT, trying to pull from a join, need unique results

mysql,select,distinct
Two tables are in question; leadsand contactAttempts. I'm trying to pull distinct leads that were contacted in 2012, with specific keywords from a text column on a separate table. The problem is that I keep getting the same lead ids over and over, and the results are so massive that...

Postgresql select distinct

postgresql,distinct
I'm with a problem on postgresql select. This query works fine: SELECT DISTINCT(COLUMN_A || ' - ' || COLUMN_B), COLUMN_A, COLUMN_B FROM TABLE_A But, when I include a new colmun on select like that: SELECT DISTINCT(COLUMN_A || ' - ' || COLUMN_B), COLUMN_A, COLUMN_B, COLUMN_C FROM TABLE_A The number of...

Sum by one field, but only for unique ID

sql,sql-server,distinct,aggregate-functions
I need to sum a sales quantity by month and year, but my database has some duplicate entries. I know this is not ideal, but it is a government database, so I cannot fix it. The database has a large number of records (> 200 million) so I don't want...

LINQ and removing duplicates from an array of objects

linq,distinct
I'm trying to de-dupe an array of objects using two columns where the second column is a Dictionary. Best way to describe this is to show some code: class MyClass { public int ID; public Dictionary<int, int> Dict = new Dictionary<int, int>(); } And now to create some objects: List<MyClass>...

SELECT DISTINCT and CONCAT don't return value correcly

mysql,sql,select,distinct,concat
I have a DataBase with this values: CREATE TABLE test ( Id CHAR (50), Time TIMESTAMP , Value CHAR (50) ); INSERT INTO test VALUES ('JOE', '2015-06-05 15:36:21', "ZONE1"); INSERT INTO test VALUES ('ANNA', '2015-06-05 14:27:35', "ZONE2"); INSERT INTO test VALUES ('JOE','2015-06-05 16:27:36', "ZONE2"); INSERT INTO test VALUES ('ANNA', '2015-06-05...

Find the equal sets of strings and their count (distinct lists)

c#,linq,count,set,distinct
I'm trying to find the distinct sets and their count. Say you have the following model. public class AModel { public IList<string> Tags { get; set; } } And you have a list of the above model, let's say 50.000 instances of AModel with 1...N Tags. I need to find...

SQL returning full objects (*) from distincted columns

sql,oracle,distinct
I am looking for a solution concerning SQL Query for Oracle DB what returns DISTINCT full objects from many columns e.g: SELECT DISTINCT mt.column1, mt.column2, mt.column3 FROM MyTable mt; But I need full objects like SELECT * FROM MyTable Is there any way to obtain such effect using usual SQL...

MySQL, get last active users in batches but prevent duplicate users in the next batch

php,mysql,unique,distinct,packets
I'm trying to extract users contributing to a specific topic in a message board. Each request gets a batch of 10 Unique users. The problem is that if some users where part of a previous batch they can occur in the next batch too. SELECT p.post_id as id, p.author as...

MySQL with a LIKE returning wrong row

php,mysql,distinct,like
I have the following: $q = "w0"; $stmt = $db_found->prepare("SELECT DISTINCT callsign FROM NetLog WHERE callsign LIKE ?"); $stmt->execute(array("%q%")); $result = $stmt->fetchAll(); $print_r($result); It returns: Array ( [0] => Array ( [callsign] => KA0QIG [0] => KA0QIG ) ) So what went wrong? Why am I only getting one return...

SELECT DISTINCT user_id of highest value in support_date WHERE

mysql,sql-order-by,max,distinct,where
tb_supporters: user_id support_date ----------------------------- 1 1301283373 2 1301283743 3 1301799207 1 1403862904 2 1405174895 3 1415266390 As the result of the query, it should output the highest support_date of each unique user_id. My actual query: "SELECT DISTINCT(user_id), support_date FROM tb_supporters WHERE support_date < " . (time() - 60*60*24*7) However, it's...

LINQ Except/Distinct based on few columns only, to not add duplicates

c#,.net,linq,distinct,except
Excuse me for the confusing title. But I couldn't come up with something more simpler. I have got below class. public class Foo { public FooId int { get; set; } public FooField string { get; set; } //some more fields here.... //.... //.... public DateTime CreatedDate { get; set;...

Find distinct values, not distinct counts in elasticsearch

elasticsearch,distinct
Elasticsearch documentation suggests that their piece of code GET /cars/transactions/_search?search_type=count { "aggs" : { "distinct_colors" : { "cardinality" : { "field" : "color" } } } } corresponds to sql query SELECT DISTINCT(color) FROM cars but it actually corresponds to SELECT COUNT(DISTINCT(color)) FROM cars I don't want to know how...

SQL Query to exclude record on certain criteria

sql-server,distinct
I have a situation, where my table have records and I want to extract data from it on the basis of what item I have w.r.t the group and class. Below is a screen shot, First part is the records in table and second one is how I want to...

SQL Server : COUNT (DISTINCT (CASE) )

sql,sql-server,count,case,distinct
I need to count number of currencies and if there are more than two it need to be USD. It can be GBP, or anything else but if it's more than 1 then need to be USD. Well, my query should looks like this, but it didn't work: SELECT name,...

Not getting the correct SUM values in SQL Server 2012 when using a PIVOT

sql-server,sql-server-2012,pivot,distinct,aggregate-functions
I am trying to create a query that will pivot over some rows but will SUM some columns and then group them together. I've used the PIVOT function before but I am running into issues when my result set contains similar values. This is SQL Server 2012. Sample code: CREATE...

SQL Select - Group by along with inner join?

sql-server,select,group-by,inner-join,distinct
I'm using the following query: SELECT TOP 12 INVENTABLE.ITEMNUMBER, INVENTABLE.ITEMNAME1, INVENTABLE.ITEMNAME2, INVENTABLE.W_TILBUD, INVENTABLE.COSTPRICE, INVENTABLE.VENDITEMNUMBER, INVENTABLE.A_PRODUCENT, INVENTABLE.GROUP_, INVENTABLE.A_GROSSISTLAGER, INVENTABLE.SupplementaryUnits FROM INVENTRANS INNER JOIN INVENTABLE ON INVENTABLE.ITEMNUMBER=INVENTRANS.ITEMNUMBER WHERE INVENTRANS.ACCOUNT='xxx' AND INVENTABLE.W_VISFORSIDE = 0 AND INVENTABLE.W_VISWWW = 1 AND INVENTABLE.BLOCKED = 0 ORDER BY INVENTRANS.ROWNUMBER DESC This query selects the 12...

TSQL not returning DISTINCT Values

sql-server,tsql,distinct
I am trying to get only unique primary key values using DISTINCT but cannot figure out why DISTINCT is not working. The TSQL below (Using Windows Azure SQL Server) is trying to identify duplicate addresses by looking at the first string of numeric values in the address field. It is...

Select multiple columns with only one distinct column in SQL Server Compact Edition

sql,sql-server-ce,distinct
I've been looking through the net and all the answers I found was about using nested selects (Like this question). But SQL Server CE doesn't support nested selects. Say I have this table ------------------------------ ID | MyText | someField | ------------------------------ 1 | Tiger | A | 2 | Tiger...

complex mysql query DISTINCT

mysql,sql,select,count,distinct
I want to get count how much 'teams' have 5 or more 'paid=1' players. I tried.. SELECT COUNT (DISTINCT team) FROM players WHERE paid=1 ...but result is 32 where 27 teams have paid 5 players, 5 have paid less than 5. How to edit this query to get only count...

How do I count distinct and total users and logins by date range across two tables?

sql,count,case,distinct
I am trying to count users created in a month, let’s say January 2015. Then I am trying to count users created as at January 31st, 2015. Then I am trying to count distinct user IDs that logged in during a month, which would also be January 2015. Then I...

SQL select distinct by 2 or more columns

sql,sql-server,select,distinct,sql-server-2014
I have a table with a lot of columns and what I need to do is to write select that would take only unique values. The main problem is that I need to check three columns at the same time and if all three columns have same values in their...

SELECT DISTINCT on multiple columns along with other columns [duplicate]

sql,distinct
This question already has an answer here: How do I (or can I) SELECT DISTINCT on multiple columns? 3 answers I want to run query like below select distinct (columnA, columnB, columnC), columnD from MY_TABLE where columnA IS NOT NULL AND columnB IS NOT NULL AND columnC is NOT...

C# Distinct in LINQ query

c#,linq,dictionary,distinct
I've got the problem after changing some code. My idea is like this: I am counting the number of words in document, but just 1 copy of a word for each document, for example: Document 1 = Smith Smith Smith Smith => Smith x1 Document 2 = Smith Alan Alan...

retrieve second 10 UNIQUE rows in SQL

sql,sql-server,distinct,between,rownum
I want to retrieve the second 10 UNIQUE rows in my sql database, for selecting the second 10 rows, I use the next script: SELECT col1, col2 FROM ( SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM MyTable ) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN 10 AND 19...

select distinct xelement based on attribute

xml,linq,lambda,distinct,xelement
<field name="value" value="A" /> <field name="value" value="B" /> <field name="text" value="C" /> <field name="text" value="D" /> <field name="key" value="E" /> When I filter I need to get one element for "value", one element for "text" and one element for "key" (Yes, "key" has only one element and it should...

Use DISTINCT with ORDER BY clause

sql-server,select,sql-order-by,distinct
I want to Alter my following stored procedure ALTER Procedure [dbo].[spEGCRedemptionReportForMHR] @DateTo datetime, @DateFrom datetime, @MerchantID varchar(11), @Pan varchar(16) As Set @DateTo = @DateTo +1 Select Distinct Convert(varchar(50),pt.TransactionDate,103) 'TransactionDate', m.MerchantName1 MerchantName, m.MerchantAddress Location, m.MerchantID, pt.TerminalID, pt.batchnumber 'Batch #', pt.SequenceNumber 'Receipt #', pt.PAN 'Card Number', c.EmbossName 'Card Holder Name', Convert(Decimal(10,2),Case when...

Mysql Select Distinct w TRIM() and Subselect Optimize Performance

mysql,performance,subquery,distinct,trim
I have a table mailing_list with the columns id, optout, email The email has duplicates, but the optout isn't the same even if the email is the same. So we can have data like this... id optout email 1 0 [email protected] 2 1 [email protected] 3 0 [email protected] 4 0 [email protected]

Returning distinct rows using WHERE Exists(SELECT 1 FROM TABLE)

sql,sqlite,distinct
I've got two tables in my SQLite database with a one to many relationship between them: CREATE TABLE Reads ( RowID INTEGER NOT NULL PRIMARY KEY, EventDate INTEGER NOT NULL, Plate TEXT, State TEXT ); CREATE TABLE ExplodedPlates ( Plate TEXT, RowID INTEGER NOT NULL, PRIMARY KEY (RowID, Plate) )...

Shaping dataset in MySQL with a multiple inner join

mysql,sql-server,join,distinct
I am learning MySQL and working with some dummy tables that I set up from an example online. The code to create the tables wasn't given, just the end result of what they should look like, so I did my best to re-create them. I know that their design isn't...

Hibernate criteria. Select all distinct entities

java,hibernate,distinct
How can I select all distinct rows in hibernate with all data? If I use this way only IDs will be selected but I need all rows with their data session.createCriteria(MyClass.class, "c") .setProjection(Projections.distinct(Projections.id())) If I use another approach it will not be performed on sql level but filtered after. That's...

How to get distinct values on GROUP_CONCAT using Google Big Query

distinct,google-bigquery,group-concat
I'm trying to get distinct values when using GROUP_CONCAT in BigQuery. I'll recreate the situation using a simpler, static example: EDIT: I've modified the example to represent better my real situation: 2 columns with group_concat which needs to be distinct: SELECT category, GROUP_CONCAT(id) as ids, GROUP_CONCAT(product) as products FROM (SELECT...

how to sum based on groups in tableau

sql,distinct,tableau
So I have 2 tables joined in Tableau. First table is 3 projects by contract: Project | Contract | Location | Amount project1 | contract1 | texas | 100 project1 | contract2 | texas | 200 project1 | contract3 | texas | 300 project2 | contract1 | new york |...

SQL with Distinct and Date

sql,date,distinct
I have the following SQL Statement: select DISTINCT USERID, NAME, SURNAME, RESIDENCE, HIT_DATE from PAGE_HIT where HIT_DATE < add_months(sysdate, -6); My problem is that I get the records more than once, because HIT_DATE is different. For example the result is: editore, editor, eddy, blabla, 28.01.2013 editore, editor, eddy, blabla, 26.01.2013...

SQL exclude duplicated pairs of columns from a two-column select

sql,select,duplicates,distinct,rows
I don't really know how to describe my problem and I haven't found an answer. I have two tables, rating and reviewer. I want to get for every pair of reviewers their names if they rated the same movie. I have this SQL query: SELECT DISTINCT re1.name, re2.name FROM reviewer...

How to check if there exist only one record for a certain Id

sql,oracle,oracle10g,distinct
How to check if there exist only one record for a certain Id I have two tables called Tbl_Company and Tbl_Employee I am fetching employees as follows- SELECT DISTINCT emp.employee_id FROM Tbl_Company comp , Tbl_Employee emp WHERE emp.company_id = comp.company_id AND emp.company_id = 1234; This query returns exactly one value....

DISTINCT query not returning value 1

php,distinct
I have this query returning rows from table but it returns all values other than 1. The output is for example : 2, 3, 5, 20, 22 etc but the id output 1 is never there but i am expecting it to be. I do not understand why? $query =...

How to get distinct records in sqlserver

sql-server,sql-server-2008,distinct
FUNDFAMILY_ID FUND_NAME REPORTSECTION_ID -------------------------------------------------------------- 172295295 VANGUARD GROWTH FUND (Retail) 126345 172295295 VANGUARD GROWTH FUND (Retail) 126354 -------------------------------------------------------------- But i need to get the results like below: FUNDFAMILY_ID FUND_NAME REPORTSECTION_ID -------------------------------------------------------------- 172295295 VANGUARD GROWTH FUND (Retail) 126345 Inside a stored procedure i have a query like below which gives duplicate rows...

SQL Server - For each distinct company count the number of employees

sql,sql-server,count,distinct
I am trying to create some SQL that will count the number of employees within each company and return only those companies with greater than or equal to n employees. I have the following tables (simplified): CompanyEmployee Table ID Name IsCompany 1 John Joe 0 2 Company Y 1 3...

SELECT DISTINCT rows

php,distinct
I am trying to output each of the rows by the value driver_profileId. Currently this only outputs one value to the page. I am expecting a list of all the unique values in the cell. Why is this not outputting more than one row? $query = "SELECT DISTINCT(driver_profileId) FROM driver_profiles";...