SQL query select only one row from multiple rows

Question

I have this query:

SELECT p.[PostingID]
      ,[EmployerID]
      ,[JobTitle]                  
      ,pin.[IndustryID]         
FROM [Posting] p
INNER JOIN [City] c
  ON p.CityID = c.CityID
LEFT OUTER JOIN PostingIndustry pin
  ON p.PostingID = pin.PostingID
WHERE (c.CityID = @CityId OR @CityId IS NULL) 
  AND (p.StateProvinceID = @StateProvinceId OR @StateProvinceId IS NULL) 
  AND (pin.IndustryID = @IndustryId OR @IndustryId IS NULL) 
  AND 
  (
     (p.[Description] LIKE '%' + @Keyword + '%' OR @Keyword IS NULL) 
     OR (p.[JobTitle] LIKE '%' + @Keyword + '%'  OR @Keyword IS NULL)
  ) 
  AND p.StreetAddress IS NOT NULL 
  AND p.ShowOnMap = 1

Which returns results for all the pin.[IndustryID] if IndustryId is not selected or if all the industries are selected. If only one industry is selected I am getting one result which is good, but when one posting is included in multiple industries then I am getting multiple results as on the image shown below:

enter image description here

So for example when that's happening I want to get only one result for that posting id otherwise I am getting multiple results for one google map marker per the image below:

enter image description here

Is there a way how I can optimize the query above to do what I need?

can you not select the industryID ? Why would the same row have so many same ID's ? — briskovich
– briskovich, Commented Nov 7, 2013 at 23:03
@briskovich because one posting can have multiple industry id's, it means that one job post can be included in more industries. Thx — Laziale
– Laziale, Commented Nov 7, 2013 at 23:04

Filipe Silva · Accepted Answer · 2013-11-07 23:31:35Z

1

What about selecting only the row with the smallest IndustryId:

SELECT [PostingID]
      ,[EmployerID]
      ,[JobTitle]                  
      ,MIN(pin.[IndustryID])         
FROM [Posting] p
INNER JOIN [City] c
  ON p.CityID = c.CityID
LEFT OUTER JOIN PostingIndustry pin
  ON p.PostingID = pin.PostingID
WHERE (c.CityID = @CityId OR @CityId IS NULL) 
  AND (p.StateProvinceID = @StateProvinceId OR @StateProvinceId IS NULL) 
  AND (pin.IndustryID = @IndustryId OR @IndustryId IS NULL) 
  AND 
  (
     (p.[Description] LIKE '%' + @Keyword + '%' OR @Keyword IS NULL) 
     OR (p.[JobTitle] LIKE '%' + @Keyword + '%'  OR @Keyword IS NULL)
  ) 
  AND p.StreetAddress IS NOT NULL 
  AND p.ShowOnMap = 1
GROUP BY [PostingID],[EmployerID],[JobTitle]

Whenever you have only one, it returns that one, when you have more than one it returns only the one with the smallest IndustryId.

edited Nov 7, 2013 at 23:31

answered Nov 7, 2013 at 23:05

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Laziale Over a year ago

top 1 will not work because there might be more results for that state or city but the industry will not be selected. If I do top 1 only one result will be displayed, although the IndustryId is null

Filipe Silva Over a year ago

I updated my answer to do a MIN on industryID and group by the remaining columns that are the same. I think it might give you what you are looking for.

Filipe Silva Over a year ago

Should be the same yes.

James Ferretti · Accepted Answer · 2013-11-07 23:16:23Z

1

Don't select industry ID and use "SELECT DISTINCT..."

answered Nov 7, 2013 at 23:16

James Ferretti

1712 silver badges15 bronze badges

Comments

Mad Dog Tannen · Accepted Answer · 2013-11-08 07:46:41Z

1

Try using a group by clause at the end

Use MAX(IndustryId).

Then also

GROUP BY PostingId,EmployerId,Jobtitle

edited Nov 8, 2013 at 7:46

answered Nov 7, 2013 at 22:59

Mad Dog Tannen

7,2505 gold badges34 silver badges55 bronze badges

2 Comments

Mad Dog Tannen Over a year ago

Add MAX(IndustryCode) in the SELECT. Sorry im on my phone atm cant copy nice cide blocks

Mad Dog Tannen Over a year ago

It should not as Max() will return only the maximum. I see you approved a answer with min and group by.

Collectives™ on Stack Overflow

SQL query select only one row from multiple rows

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related