Stack Overflow
Stack Overflow

Questions tagged [distinct]

The DISTINCT keyword is used to remove duplicate values from a result of a SQL or SPARQL query.

The DISTINCT keyword is used to remove duplicate values from a result of a SQL or SPARQL query.

The SQL 92 Standard defines DISTINCT as:

Two values are said to be not distinct if either: both are the null value, or they compare equal... Otherwise they are distinct. Two rows (or partial rows) are distinct if at least one of their pairs of respective values is distinct. Otherwise they are not distinct.

The SPARQL 1.1 Specification describes DISTINCT as:

The DISTINCT solution modifier eliminates duplicate solutions. Only one solution that binds the same variables to the same RDF terms is returned from the query.

5114 questions
88
votes
4 answers

MongoDB distinct aggregation

I'm working on a query to find cities with most zips for each state: db.zips.distinct("state", db.zips.aggregate([ { $group: { _id: { state: "$state", city: "$city" }, numberOfzipcodes: { …
LemonMan
  • 2,963
  • 8
  • 24
  • 34
86
votes
5 answers

SQL distinct for 2 fields in a database

Can you get the distinct combination of 2 different fields in a database table? if so, can you provide the SQL example.
leora
  • 188,729
  • 360
  • 878
  • 1,366
84
votes
6 answers

PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list

ActionView::Template::Error (PG::Error: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list I'm creating an events website and I'm trying to sort the rendered rsvps by the start time of the event. There are a lot of RSVPS…
Andrew
  • 1,167
  • 1
  • 10
  • 16
80
votes
8 answers

How to select records without duplicate on just one field in SQL?

I have a table with 3 columns like this: +------------+---------------+-------+ | Country_id | country_title | State | +------------+---------------+-------+ There are many records in this table. Some of them have state and some other…
Mohammad Saberi
  • 12,864
  • 27
  • 75
  • 127
79
votes
5 answers

Distinct pair of values SQL

Consider create table pairs ( number a, number b ) Where the data is 1,1 1,1 1,1 2,4 2,4 3,2 3,2 5,1 Etc. What query gives me the distinct values the number column b has So I can see 1,1 5,1 2,4 3,2 only I've tried select distinct ( a ) , b…
OscarRyz
  • 196,001
  • 113
  • 385
  • 569
76
votes
10 answers

How to get distinct results in hibernate with joins and row-based limiting (paging)?

I'm trying to implement paging using row-based limiting (for example: setFirstResult(5) and setMaxResults(10)) on a Hibernate Criteria query that has joins to other tables. Understandably, data is getting cut off randomly; and the reason for that is…
Daniel Alexiuc
  • 13,078
  • 9
  • 58
  • 73
76
votes
8 answers

sql query distinct with Row_Number

I am fighting with the distinct keyword in sql. I just want to display all row numbers of unique (distinct) values in a column & so I tried: SELECT DISTINCT id, ROW_NUMBER() OVER (ORDER BY id) AS RowNum FROM table WHERE fid = 64 however the below…
objectWithoutClass
  • 1,631
  • 3
  • 14
  • 15
75
votes
6 answers

Efficient latest record query with Postgresql

I need to do a big query, but I only want the latest records. For a single entry I would probably do something like SELECT * FROM table WHERE id = ? ORDER BY date DESC LIMIT 1; But I need to pull the latest records for a large (thousands of…
Sheldon Ross
  • 5,364
  • 7
  • 31
  • 37
72
votes
9 answers

Delete duplicate rows from a BigQuery table

I have a table with >1M rows of data and 20+ columns. Within my table (tableX) I have identified duplicate records (~80k) in one particular column (troubleColumn). If possible I would like to retain the original table name and remove the duplicate…
TheGoat
  • 2,587
  • 3
  • 25
  • 58
72
votes
7 answers

How does DISTINCT work when using JPA and Hibernate

What column does DISTINCT work with in JPA and is it possible to change it? Here's an example JPA query using DISTINCT: select DISTINCT c from Customer c Which doesn't make a lot of sense - what column is the distinct based on? Is it specified on…
Steve Claridge
  • 10,650
  • 8
  • 33
  • 35
70
votes
4 answers

How to implement IEqualityComparer to return distinct values?

I have a L2E query that returns some data that contains duplicate objects. I need to remove those duplicate objects. Basically I should assume that if their IDs are the same then the objects are duplicate. I've tried q.Distinct(), but that still…
Bogdan Verbenets
  • 25,686
  • 13
  • 66
  • 119
66
votes
3 answers

Get distinct values of Queryset by field

I've got this model: class Visit(models.Model): timestamp = models.DateTimeField(editable=False) ip_address = models.IPAddressField(editable=False) If a user visits multiple times in one day, how can I filter for unique rows based on the…
Scott
  • 3,204
  • 3
  • 31
  • 41
65
votes
2 answers

How can I count how many duplicates there are for each distinct value in sqlite?

I have a table: ref,type 1,red 2,red 3,green 4,blue 5,black 6,black I want the result of a sqlite query to be: red,2 green,1 blue,1 black,2 I think the hardest thing to do is find a question to match my problem? Then I am sure the answer is…
Chris Denman
  • 1,187
  • 3
  • 9
  • 15
63
votes
4 answers

Produce DISTINCT values in STRING_AGG

I'm using the STRING_AGG function in SQL Server 2017. I'd like to create the same effect as COUNT(DISTINCT ). I tried STRING_AGG(DISTINCT ,',') but that is not legal syntax. I'd like to know if there is a T-SQL work-around. Here…
Brent Arias
  • 29,277
  • 40
  • 133
  • 234
61
votes
5 answers

Get last distinct set of records

I have a database table containing the following columns: id code value datetime timestamp In this table the only unique values reside in id i.e. primary key. I want to retrieve the last distinct set of records in this table based on the…
ObiHill
  • 11,448
  • 20
  • 86
  • 135
1 2
3
…
99 100