distinct

SUBSONIC 3.0.0.3 Subsonic.Query.SqlQuery

New to subsonic and having issues figuring it out. I am simply just trying to do a distinct search and any documentation I find is telling me to use the class/method SubSonic.SqlQuery Though I am finding out that since I am using the newest version, a lot of the documentation I am finding does not apply. For example, I am getting this ...

mysql - query group/concat question??

I have a situation where I return results with mutiple rows. I'm looking for aw way to return a single row, with the multiple items in separate cols within the row. My initial query: SELECT a.name, a.city, a.address, a.abbrv, b.urltype, b.url FROM jos__universityTBL as a LEFT JOIN jos__university_urlTBL as b on b.universityID...

Need to find number of new unique ID numbers in a MySQL table

I have an iPhone app out there that "calls home" to my server every time a user uses it. On my server, I create a row in a MySQL table each time with the unique ID (similar to a serial number) aka UDID for the device, IP address, and other data. Table ClientLog columns: Time, UDID, etc, etc. What I'd like to know is the number of new d...

MYSQL: SELECT Method - but don't show duplicates / GROUP or DISTINCT?

hello, how can I Select and dont'show duplicates? actually it's showing like that: apple | apple | apples | apple this is my code: $search = $_GET['q']; $query = "SELECT * FROM query WHERE searchquery LIKE '%$search%' AND searchquery <> '$search'"; ...

linq distinct field

how set distinct to this query return _dataContext.ProjectPictures.Include("Projects").Include("ProjectPictureTypes").Where(p => p.ProjectPictureTypes.Id == 1).ToList(); i need something like return _dataContext.ProjectPictures.Include("Projects").Include("ProjectPictureTypes").Where(p => p.ProjectPictureTypes.Id == 1).Distinct(p=>p...

How to select DISTINCT rows without having the ORDER BY field selected

So I have two tables students (PK sID) and mentors (PK pID). This query SELECT s.pID FROM students s JOIN mentors m ON s.pID = m.pID WHERE m.tags LIKE '%a%' ORDER BY s.sID DESC; delivers this result pID ------------- 9 9 3 9 3 9 9 9 10 9 3 10 etc... I am trying to get a list of distinct mentor ID's with this ordering so I am looki...

SQL Server: how can I list distinct value of table in a single row, separated by comma

I have the following table: CREATE TABLE TEMP (ID INT, SEGMENT CHAR(1), SEGOFF INT, CHECKED SMALLDATETIME) INSERT INTO TEMP VALUES (1,'A',0,'2009-05-01') INSERT INTO TEMP VALUES (2,'B',1,'2009-05-01') INSERT INTO TEMP VALUES (3,'C',0,'2009-05-01') INSERT INTO TEMP VALUES (4,'A',0,'2009-05-02') INSERT INTO TEMP VALUES (5,'B',2,'2009-05-...

Distinct with Count and SQl Server 2005

Hey everyone, Trying to work on a query that will return the top 3 selling products with the three having a distinct artist. Im getting stuck on getting the unique artist. Simplified Table schema Product ProductID Product Name Artist Name OrderItem ProductID Qty So results would look like this... PID artist ...

Select distinct ... inner join vs. select ... where id in (...)

I'm trying to create a subset of a table (as a materialized view), defined as those records which have a matching record in another materialized view. For example, let's say I have a Users table with user_id and name columns, and a Log table, with entry_id, user_id, activity, and timestamp columns. First I create a materialized view of...

I don't find the sql request

Hi everybody, Here it's my problem I've a list of the following measure : src1 dst2 24th december 2009 src1 dst3 22th december 2009 src1 dst2 18th december 2009 I would like to have just the latest measures with a sql request -> 2 first lines in my case because the pairs(src and dst) aren't the same. I try to use DISTIN...

I DISTINCTly hate MySQL (help building a query)

This is staight forward I believe: I have a table with 30,000 rows. When I SELECT DISTINCT 'location' FROM myTable it returns 21,000 rows, about what I'd expect, but it only returns that one column. What I want is to move those to a new table, but the whole row for each match. My best guess is something like SELECT * from (SELECT DIS...

In SQL, why is "Distinct" not used in a subquery, when looking for some items "not showing up" in the other table?

Usually when looking for some items not showing up in the other table, we can use: select * from gifts where giftID not in (select giftID from sentgifts); or select * from gifts where giftID not in (select distinct giftID from sentgifts); the second line is with "distinct" added, so that the resulting table is smaller, and probably...

Help with MySQL getting count of a column with distinct another column?

Hi I want to get the count of column called 'response'. But even though a user submits many responses I want it to be only considered as one. So basically I want to get the count of 'response' with DISTINCT user_id. How can I do this? Thank you. ...

JPQL / SQL: How to select * from a table with group by on a single column?

I would like to select every column of a table, but want to have distinct values on a single attribute of my rows (City in the example). I don't want extra columns like counts or anything, just a limited number of results, and it seems like it is not possible to directly LIMIT results in a JPQL query. Original table: ID | Name |...

List<T>.Distinct() in C# - multiple criteria for EqualityComparer ?

I have a collection of objects which have several properties in each of them. I often need to get a list of distinct values for many properties in this collection. If I implement IEqualityComparer on this type , it gives me one single criteria for getting the distinct objects in the collection. How do I get to be able to call Distinct o...

MySQL top count({column}) with a limit

I have a table with an ip address column. I would like to find the top five addresses which are listed. Right now I'm planning it out the following: Select all distinct ip addresses Loop through them all saying count(id) where IP='{ip}' and storing the count List the top five counts. Downsides include what if I have 500 ip addresses...

Can "Distinct" key Word be used twice in a single Select Query?

Can "Distinct" key Word be used twice in a single Select Query? like wise: select DISTINCT(trackid), DISTINCT(table_name) from jos_audittrail where live = 0 AND operation = UPDATE Thanks ...

SQL Distinct keyword in assignment statement

I have a query that works: DECLARE @ProductID int SET @ProductID = '1234' SELECT DISTINCT TOP 12 a.ProductID FROM A a WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE b.ProductID = @ProductID) AND a.ProductID != @ProductID It returns a list of 12 product numbers, all unique. I need to store these results in a variable, comm...

Linq Distinct on list

I have a list like this: List people age name 1 bob 1 sam 7 fred 7 tom 8 sally I need to do a linq query on people and get an int of the number distinct ages (3) int distinctAges = people.SomeLinq(); how? how? ...

Is my understanding of "select distinct" correct?

We recently discovered a performance problem with one of our systems and I think I have the fix but I'm not certain my understanding is correct. In simplest form, we have a table blah into which we accumulate various values based on a key field. The basic form is: recdate date rectime time system varchar(20) count integer ac...