Where Distinct SQL

Question

I am trying to select g.house_ID as Distinct so that the values returned by the query below will be unique based on g.house_id.

So if house ID has 3 record of the value 1. It will only return the 1st entry of house ID and not 3 rows.

SELECT  r.*, g.*
FROM house r, pic_gallery g 
WHERE r.house_id = 1 AND r.house_id = g.house_id

Add some sample table data and the expected result - as formatted text (not images.) — jarlh, Sep 28 '17 at 08:53
Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh, Sep 28 '17 at 08:54
How is "1st entry" defined? Hint: Tables are *unordered* sets of rows. Also, comma joins were replaced by explicit join syntax in **1992**. It's about time you updated. — Damien_The_Unbeliever, Sep 28 '17 at 08:55
Seek and ye shall find (on google): https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group — Simon, Sep 28 '17 at 09:05
Possible duplicate of [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Simon, Sep 28 '17 at 09:06

score 0 · Answer 1 · answered Sep 28 '17 at 10:44

A simple subselect will help you if you for example want to display a single picture from your gallery (fetch a single column)

SELECT  
  r.*
  , (SELECT TOP(1) PrimaryImage 
     FROM pic_gallery g 
     WHERE r.house_id = 1 AND r.house_id = g.house_id
    )
FROM house r

In your case it seems like you want to join the entire gallery table`s row data If you want to select only one row from your subtable you need to order it in some way so that the database knows what row is the correct one.

SELECT R.*
FROM house R
OUTER APPLY (
    SELECT TOP 1 G.ID,G.Image,G.Owner,G.HouseID
    FROM gallery G
    WHERE G.HouseID = R.HouseID
    ORDER BY G.CreatedDate
) O

Also Partition over may be useful to read about.

Personally i would mark one of the gallery rows as primary and use a simple join like this.

SELECT * From house R 
LEFT OUTER JOIN gallery G ON R.HouseID = G.HouseID 
WHERE G.PrimaryRow = 1

score 0 · Answer 2 · answered Sep 28 '17 at 10:48

0

Try a left join with a distinct.

SELECT DISTINCT g.house_id, r.*, g.*
FROM pic_gallery g 
LEFT JOIN house r, 
ON r.house_id = g.house_id
WHERE r.house_id = 1

answered Sep 28 '17 at 10:48

Daniel

795
1
10
25

Where Distinct SQL

2 Answers2