How get only one result for each company in the table?

Question

I have query but now I need to change the query to the database to get only one result for each company in the table Price.

For this I add row GROUP by p.id_firm and than I get next query:

SELECT TOP 20 
  p.id_price as p_id_price, 
  p.id_service as p_id_service, 
  p.name as p_name, 
  p.name_original as p_name_original, 
  p.id_producer_country as p_id_producer_country, 
  p.id_firm as p_id_firm, 
  f.name as f_name, 
  f.address as f_address, 
  f.phone as f_phone, 
  city.name as city_name, 
  pc.name as pc_name 
FROM Price p 
left join Firm f 
  on f.id_service=p.id_service 
  AND f.id_city=p.id_city 
  AND f.id_firm=p.id_firm 
left join City city 
  on city.id_city = p.id_city 
left join Producer_country pc 
  on pc.id_producer_country = p.id_producer_country 
WHERE p.id_city='73041' 
  AND p.include='1' 
  AND p.blocked='0' 
  AND f.blocked='0' 
  AND ( f.name LIKE 'Окно%' COLLATE SQL_Latin1_General_Cp1251_CI_AS OR p.name LIKE 'Окно%' COLLATE SQL_Latin1_General_Cp1251_CI_AS ) 
GROUP by p.id_firm ORDER BY p.name ASC

But if I use it, I get error:

Msg 8120, Level 16, State 1, Line 2 Column 'Price.id_price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Tell me please the right way to change this query or maybe make other?

Structure all tables you can see here

P.S.: Sorry for the inaccuracy. Search by firm name and the name product . If the search term is contained in the product name or the name of the company, and there are some of the results with the same id_firm, need choose one the most suitable value, that is id_firm in results search should be unique.

Refere this example [enter link description here][1] [1]: http://stackoverflow.com/questions/3680254/t-sql-selecting-column-based-on-maxother-column — SP007, Mar 01 '13 at 19:37
Well, if one firm has multiple people / services / prices, which one do you want? And you can't say "it doesn't matter" because SQL Server needs to know. — Aaron Bertrand, Mar 01 '13 at 19:43
@helloSuresh: In comments, only the `[text](link)` template is supported for link formatting. See [Markdown Editing Help](http://stackoverflow.com/editing-help#comment-formatting) for more info. — Andriy M, Mar 01 '13 at 19:46
I don't see any aggregates in the query, am I missing them? If not, why are you using GROUP? Maybe you just want ORDER BY? — Melanie, Mar 01 '13 at 19:57
@AaronBertrand table prices have products name, id_firm and other inputs. If i make query i get all results, but me need get all results but only 1 result with one id_firm(id_firm in all resuts will be unique). — Leo Loki, Mar 01 '13 at 20:52
@LeoLoki you still need to be *specific*. If a firm has two products, ***WHICH*** product do you want? — Aaron Bertrand, Mar 01 '13 at 20:57
@AaronBertrand sorry for the inaccuracy. Search by firm name and the name product . If the search term is contained in the product name or the name of the company, and there are some of the results with the same id_firm, need choose one the most suitable value, that is id_firm in results search should be unique. — Leo Loki, Mar 01 '13 at 21:13
As an aside note, your `f.blocked='0'` condition in `WHERE` essentially transforms your `left join Firm` into `inner join Firm`, because it filters out join results where there is no match between `Price` and `Firm`. (If, however, `Price` *always* has a match in `Firm` then why do you use `left join` instead of `inner join`?) — Andriy M, Mar 01 '13 at 23:10

score 1 · Accepted Answer · answered Mar 01 '13 at 23:11

Use ROW_NUMBER() instead of grouping.

Number every company's rows separately. When sorting the rows for ROW_NUMBER(), use the p.name LIKE 'Окно%' condition to put rows with matching products before the others.

Here's an example what it might look like:

;
WITH ranked AS (
  SELECT
    p.id_price as p_id_price, 
    p.id_service as p_id_service, 
    p.name as p_name, 
    p.name_original as p_name_original, 
    p.id_producer_country as p_id_producer_country, 
    p.id_firm as p_id_firm, 
    f.name as f_name, 
    f.address as f_address, 
    f.phone as f_phone, 
    city.name as city_name, 
    pc.name as pc_name,
    ROW_NUMBER() OVER (
      PARTITION BY p.id_firm
      ORDER BY
        CASE  -- this criterion puts matching products before non-matching ones
          WHEN p.name LIKE 'Окно%' COLLATE SQL_Latin1_General_Cp1251_CI_AS
          THEN 1 ELSE 2
        END,
        p.id_price  -- you may use any sorting criteria at this point,
                    -- just ensure it makes the results predictable
    ) AS rnk
  FROM Price p 
  left join Firm f 
    on f.id_service=p.id_service 
    AND f.id_city=p.id_city 
    AND f.id_firm=p.id_firm 
  left join City city 
    on city.id_city = p.id_city 
  left join Producer_country pc 
    on pc.id_producer_country = p.id_producer_country 
  WHERE p.id_city='73041' 
    AND p.include='1' 
    AND p.blocked='0' 
    AND f.blocked='0' 
    AND ( f.name LIKE 'Окно%' COLLATE SQL_Latin1_General_Cp1251_CI_AS
       OR p.name LIKE 'Окно%' COLLATE SQL_Latin1_General_Cp1251_CI_AS ) 
)
SELECT TOP 20
  p_id_price, 
  p_id_service, 
  p_name, 
  p_name_original, 
  p_id_producer_country, 
  p_id_firm, 
  f_name, 
  f_address, 
  f_phone, 
  city_name, 
  pc_name
FROM ranked
WHERE rnk = 1
-- the absence of ORDER BY makes your TOP 20 results indeterminate
;

Basically, this ranks every company's rows, then pulls only the rows with the rankings of 1 from all the companies, making it ultimately one row per company.

score 0 · Answer 2 · answered Mar 02 '13 at 01:30

0

You could try Select Distinct for a possible quick fix http://www.w3schools.com/sql/sql_distinct.asp

answered Mar 02 '13 at 01:30

centree

2,399
7
28
32

How get only one result for each company in the table?

2 Answers2