Distinct only one column in sql

Question

I have a query and i want to have distinct only in one column - InjuryScenario.id and now it calls for an error- How can i do it with this query:

SELECT TOP (10) 
    Cause.Cause_name, 
    City.City_name, 
    distinct InjuryScenario.InjuryScenario_id,
    InjuryScenario.Date, 
    Place.Place_name, 
    Media.Media_name, 
    InjuryScenario.InjuryDesc, 
    Media.MediaType_name 
FROM InjuryScenario 
INNER JOIN Cause 
    ON InjuryScenario.Cause_id = Cause.Cause_id 
INNER JOIN PlaceDet 
    ON InjuryScenario.InjuryScenario_id = PlaceDet.InjuryScenario_id 
INNER JOIN City 
    ON PlaceDet.City_id = City.City_id 
INNER JOIN Place 
    ON InjuryScenario.Place_id = Place.Place_id 
INNER JOIN Media2InjuryScenario 
    ON InjuryScenario.InjuryScenario_id = Media2InjuryScenario.InjuryScenario_id 
INNER JOIN Media 
    ON Media2InjuryScenario.Media_id = Media.Media_id 
ORDER BY InjuryScenario.TimeStamp DESC

Thank you

`DISTINCT` operates across all columns to produce a unique set. You could `GROUP BY` the single column in a subquery (or CTE) and then use that to `JOIN` back to the details you want. — Yuck, Jun 04 '14 at 16:28
`distinct` removes duplicates in a result set. If you don't want a particular column to have duplicate data, you need to filter them out specifically, `distint` is not meant for that. Which DBMS do you use? — Nivas, Jun 04 '14 at 16:30
As @nivas says, it'd be better to change your query to not create duplicate rows in the first place. You can use the duplicate question, but posting some sample data might yield a better way to de-dupe. Specifically, `InjuryScenario` (or things linked to it directly) look to be the likely culprits. If `InjuryScenario.InjuryScenario_id` is supposed to be unique, which of the rows from the other columns should be used? Presumably there are multiple values per that id in `InjuryDesc` `InjuryScenario.Date`... — Clockwork-Muse, Jun 05 '14 at 12:31

EGN · Answer 1 · 2017-03-24T14:17:54.433

SELECT DISTINCT is one keyword, you can't separate it

most likely you have one-to-many relationships between the join tables. Your first step is to find which one and then possibly do a Row_Number() partitioning it over column and then select the one you want

Assuming PlaceDet is the many-to-one to InjuryScenario and you want to only join with first record in PlaceDet (if you order by date), then

Your code will look like something like this

SELECT TOP (10) 
    Cause.Cause_name, 
    City.City_name, 
    InjuryScenario.InjuryScenario_id,
    InjuryScenario.Date, 
    Place.Place_name, 
    Media.Media_name, 
    InjuryScenario.InjuryDesc, 
    Media.MediaType_name 
FROM InjuryScenario 
INNER JOIN Cause ON InjuryScenario.Cause_id = Cause.Cause_id 
INNER JOIN (SELECT InjuryScenario_id, ROW_NUMBER() OVER(PARTITION BY InjuryScenario_id ORDER BY InjuryDate ASC) AS RowRank FROM PlaceDet) PlaceDet ON (InjuryScenario.InjuryScenario_id = PlaceDet.InjuryScenario_id AND PlaceDet.RowRank = 1)
INNER JOIN City ON PlaceDet.City_id = City.City_id 
INNER JOIN Place ON InjuryScenario.Place_id = Place.Place_id 
INNER JOIN Media2InjuryScenario ON InjuryScenario.InjuryScenario_id = Media2InjuryScenario.InjuryScenario_id 
INNER JOIN Media  ON Media2InjuryScenario.Media_id = Media.Media_id 
ORDER BY InjuryScenario.TimeStamp DESC

Distinct only one column in sql

1 Answers1