distinct

SQL DISTINCT Value Question

How can I filter my results in a Query? example I have 5 Records John,Smith,apple Jane,Doe,apple Fred,James,apple Bill,evans,orange Willma,Jones,grape Now I want a query that would bring me back 3 records with the DISTINCT FRUIT, BUT... and here is the tricky part, I still want the columns for First Name , Last Name. PS ...

Workaround for missing group_concat on MySQL 3.23

I developed a script on MySQL 5.1, not realising the client is stuck with MySQL 3.23. It makes significant use of GROUP_CONCAT() and particularly GROUP_CONCAT(DISTINCT), to reduce a column of values down to a succinct, comma-separated list. So, are there any workarounds? The best I can think of is doing a separate query to just get the ...

How to SELECT DISTINCT Info with TOP 1 Info and an Order By FROM the Top 1 Info

I have 2 tables, that look like: CustomerInfo(CustomterID, CustomerName) CustomerReviews(ReviewID, CustomerID, Review, Score) I want to search reviews for a string and return CustomerInfo.CustomerID and CustomerInfo.CustomerName. However, I only want to show distinct CustomerID and CustomerName along with just one of their CustomerRevi...

How to make a "distinct" join with MySQL

I have two MySQL tables (product and price history) that I would like to join: Product Table: Id = int Name = varchar Manufacturer = varchar UPC = varchar Date_added = datetime Price_h table: Id = int Product_id = int Price = int Date = datetime I can perform a simple Left Join: SELECT Product.UPC, Product.Name, Price_h.Price, Price_...

Using SELECT DISTINCT in MYSQL

Been doing a lot of searching and haven't really found an answer to my MYSQL issue. SELECT DISTINCT name, type, state, country FROM table Results in 1,795 records SELECT DISTINCT name FROM table Results in 1,504 records For each duplicate "name"... "type", "state", "country" aren't matching in each record. Trying to figure out ...

Select distinct rows from MongoDB

How do you select distinct records in MongoDB? This is a pretty basic db functionality I believe but I can't seem to find this anywhere else. Suppose I have a table as follows -------------------------- | Name | Age | -------------------------- |John | 12 | |Ben | 14 | |Robert | 14 |...

how to ignore a column for select distict in postgresql?

Hallo everybody! i have a SQL (see above) and i would like to know how i can make sure that i don't get doubles concerning the name only. if a name appears in the first Select it's the master and should be ignored in the following selects. "SELECT name, id, 'place' AS tablename FROM places WHERE lower(name) LIKE '".strtolower($needle)....

How to Select only one unique record from the Table using Distinct

my table have several records which has the same MemberID. i want to result out only one record. select DISTINCT(MemberID) from AnnualFees; then result will come. but i want to show the other column data also but when i do this select DISTINCT(MemberID),StartingDate,ExpiryDate,Amount from AnnualFees; all the details including s...

SQL query, distinct rows needed

Hey guys, I have the following table structured like this: So basically as you can see, the department goes through name changes every couple of years. Look at number 16 for example. I want a select query that will only get the name when the date is the greatest. How do I do that? ...

SQL Count distinct times with a 30 minute difference

I'm trying to find a SQL query that will count the number of distinct start times that are at least 30 minutes different. I have a number of employees that are paid a credit when they start work on at least three distinct times in a week, where the start time is at least 30 minutes different from the other start times. For example: se...

How to fetch distinct values with arel/relational algebra

I'm doing my best to bend my brain around arel and the relational algebra behind it, but how to represent a SELECT DISTINCT is consistently eluding my comprehension. Can anyone explain how to arel: SELECT DISTINCT title FROM posts; Many thanks! ...

Using Linq to return each item only once.

Here's what I'm trying to do. Have a use right a sequence of numbers delimited by spaces. After I save those numbers, I want to return a string of all the numbers only once, even if a number has appeared n number of times in the sequence. string[] tempNumbers = textValue.Split(' '); IEnumerable<string> distinctNumbers = tempNumbers.Whe...

TSQL Average Column By Distinct Entries In Another Column

I start with a table looking like this... +-------------------------+ | procName | TimeEnded | +-------------------------+ | A | 04:00:00.000 | | B | 04:01:00.000 | | C | 04:03:00.000 | | A | 04:06:00.000 | | B | 04:10:00.000 | | ... | ... | Run a query to generate a RunTime column,...

How to get the distinct field value in a multiple field in multiple table?

Hi friends, i have a two table x and y. In that x table has x1 as country, x2 as country, y table has y1 as country, y2 has country. For this data, how can i get the distinct country values in this two table with this four country field? Before that i used a single country in a single table like this, $query="select distinct(`x1`) f...

Getting sum() on a different distinct row MySQL

Hello, I was looking on different questions on this issue, but couldn't find an answer for my problem. This is my query: SELECT SUM( lead_value ) AS lead_value_sum, count( DISTINCT phone ) AS SUM, referer FROM leads t1 INNER JOIN leads_people_details t2 ON t1.lead_id = t2.lead_id INNER JOIN user_to_leads t3 ON t1.lead_id = t3.lead_id ...

LINQ - SELECT DISTINCT with NOT IN

I'm have a SQL statement which I am trying to transform in a LINQ statement... SELECT DISTINCT mc.* FROM ManufractorCategories mc WHERE mc.Active = 'true' AND mc.Folder = 'false' AND (mc.Id not in (SELECT Category_id FROM Manufractor_Category WHERE Manufractor_id = 3)); That's my last, not working LINQ ...

Removing duplicate byte[]s from a collection.

This will probably be an extremely simple question. I'm simply trying to remove duplicate byte[]s from a collection. Since the default behaviour is to compare references, I tought that creating an IEqualityComparer would work, but it doesn't. I've tried using a HashSet and LINQ's Distinct(). Sample code: using System; using System.Co...

Hql statement not returning distinct values when specified.

Hello experts! As the title states I have some issues with my hql statement. I have two entities: SecondEntity: public class SecondEntity implements Serializable { @Id @GeneratedValue private Long id; @ManyToOne @JoinColumn(name = "firstEntityId") private FirstEntity firstEntity; @NotNull(message = "the b...

how to exclude rows that have duplicates in one field

Hello I have a very simple task, but I cannot find any solution. I have two tables, 'articles' and 'categories' My article table look like this: id | cat_id | title | content 1 1 Blah Content 1 2 1 Blah2 Content 2 3 2 Blah3 Content 3 My categories table look like this: id | title 1 Catego...

distinct() function (not select qualifier) in postgres

I just came across a SQL query, specifically against a Postgres database, that uses a function named "distinct". Namely: select distinct(pattern) as pattern, style, ... etc ... from styleview where ... etc ... Note this is NOT the ordinary DISTINCT qualifier on a SELECT -- at least it's not the normal syntax for the DISTINCT qualifier...