Distinct one field but wanted to display all the columns

Question

Anyone can help me the easiest way to distinct one particular field/column but displaying all fields/columns? Please see attached image the data-source, I've tried to query on my own but it displays all the 16 records I'm looking for 6 records only.

USE DBASE;

WITH t1 as (SELECT DISTINCT STATE
FROM DSOURCE),
t2 as (SELECT *
FROM DSOURCE)
SELECT
  *
FROM
  t1
  LEFT JOIN t2 ON t1.STATE=t2.STATE

Why dont you try using group by rather than distinct? Unless we're not understanding you correctly? — Doug Coats, Jan 07 '20 at 17:45
You should always put the sample data in text format and if possible in the form of DDL and DML so it can be used by someone who want to help you. Image data can't be copied or edited. — Suraj Kumar, Jan 07 '20 at 17:46
what happens to CITY 2 for LA? Any particular set of data you are interested in returning for each distinct state? — Alex, Jan 07 '20 at 17:46
Possible duplicate of [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — GSerg, Jan 07 '20 at 17:48

score 0 · Answer 1 · answered Jan 07 '20 at 17:46

0

You want row_number() :

select d.*
from (select d.*, row_number() over (partition by d.state order by d.f) as seq
      from dsource d
     ) d
where d.seq = 1;

answered Jan 07 '20 at 17:46

Yogesh Sharma

49,870
5
26
52

Kamil + Yogesh. Thank you, it solves my problem :) – Mitchel Araw Jan 07 '20 at 17:54

score 0 · Answer 2 · answered Jan 07 '20 at 17:46

0

row_number() is your saviour here:

;WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY STATE ORDER BY B,C,D,E,F) Corr
    FROM dsource
)
SELECT *
FROM CTE
WHERE Corr = 1

answered Jan 07 '20 at 17:46

Kamil Gosciminski

16,547
8
49
72

score 0 · Answer 3 · edited Jan 07 '20 at 17:55

You clearly want the first row from each `state's data. However, your dataset doesn't have a clear indicator of what is "first". So, you need to take one of two approaches.

If your data actually has an IDENTITY column, you can approach it with a query like this:

SELECT * FROM DSOURCE d WHERE ID In ( SELECT MIN(ID) FROM DSOURCE ds GROUP BY State )

If not, you will need to use the row_number() functionality as shown above. @yogesh-sharma has the best example of using the this.

Distinct one field but wanted to display all the columns

3 Answers3