CODEWITHSUNDEEP
sqlsql-server-2008
Leo Loki
Leo Loki

Reputation: 2575

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

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.

Upvotes: 0

Views: 273

Answers (2)

centree
centree

Reputation: 2439

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

Upvotes: 0

Andriy M
Andriy M

Reputation: 77667

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.

Upvotes: 1

Related Questions