Select Distinct

Select Distinct

• yaakov

  Please post a table create script, a sample of your data and simulate the results you want. Then we won't have to guess what you want.

  Thanks,

  Louis

  
  
  

• DianeWilson

  Is it 1-to-Manyrelationship between products and stock tables

  
  
  

• Nick__A.

  I understand now, though it is kind of a different thing to do, unless you are looking to get a random value. This gets the minimum value of a set of surrogate keys, which is really meaningless (I would expect)
  
  insert into products(description)
  SELECT 'PROD1'
  UNION ALL
  SELECT 'PROD2'
  UNION ALL
  SELECT 'PROD3'
  
  select * from products
  
  insert into stock (recid,partno, price)
  select 1, 1, 10.0000
  union all
  select 1, 2, 10.0000
  union all
  select 1, 3, 11.0000
  union all
  select 2, 4, 10.0000
  union all
  select 2, 5, 10.0000
  union all
  select 2, 6, 11.0000
  union all
  select 3, 7, 15.0000
  union all
  select 3, 8, 15.0000
  union all
  select 3, 9, 16.0000
  
  
  SELECT Products.recid, description, partno, price
  FROM Products
  JOIN stock
  ON Products.recid = Stock.recid
  --this exists clause is used to get the minimum valued partno.
  WHERE exists ( select *
  from (SELECT Products.recid, min(partno) as partNo
  from Products as p2
  JOIN stock as s2
  ON p2.recid = s2.recid
  group by p2.recid) as GetOne
  where products.recId = GetOne.recId
  and stock.partNo = GetOne.partNo)
  
  
  recid description partno price
  ----------- ----------- ----------- ---------------------
  1 PROD1 1 10.00
  2 PROD2 4 10.00
  3 PROD3 7 15.00
  
  
  One quick note. You need to consider how you name things. using recId as the key for products is way confusing. It took me a few minutes to get that the recId was the key (hopefully you have foreign keys in your actual tables.)
  
  Hope this helps.
  
  
  

• krish_sathish

  Yes it is
  

• Sirkku Willie MSFT

  Thanks .
  

• blabla

  OK. Thanks I didn't understand it meant that if I select a few columns with the DISTINCT clause, it will return a distinct value for each of the columns !

  But how can I get only one column to be distinct (in my case the products.recid)

  
  

• ShawnReed

  Because you must have different description or partno or proice for a Products.recid. In other words all columns must be have unique values for a given products,recid

  
  
  

• DanielMcN

  In order to get a distinct recid, you will need to either just list that one column, or only list columns that have one distinct value for each recid:

  SELECT DISTINCT Products.recid from Products
  JOIN stock ON Products.recid = Stock.recid

  Alternatively, you can use aggregate expressions on the other values in a group by if appropriate to the query:

  SELECT Products.recid, max(description), max(partno), avg(price) from Products
  JOIN stock
  ON Products.recid = Stock.recid GROUP BY Products.recid

  That will give you a distinct list of recid's, with the result of the aggregate expression for every other column in the list.

  HTH

  
  

• GaryStrader

  CREATE TABLE [Products] (
  [recid] [int] IDENTITY (1, 1) NOT NULL ,
  [Description] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  CONSTRAINT [UQ__Products__76CBA758] UNIQUE NONCLUSTERED
  (
  [recid]
  ) ON [PRIMARY]
  ) ON [PRIMARY]
  GO

  CREATE TABLE [Stock] (
  [partno] [int] NULL ,
  [recid] [int] NULL ,
  [Price] [money] NULL ,
  UNIQUE NONCLUSTERED
  (
  [partno]
  ) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  

  when I run this query

  SELECT Products.recid, description, partno, price from Products
  JOIN stock
  ON Products.recid = Stock.recid

  It returns this

  recid description partno price
  ----------- ----------- ----------- ---------------------
  1 PROD1 1 10.0000
  1 PROD1 2 10.0000
  1 PROD1 3 11.0000
  2 PROD2 4 10.0000
  2 PROD2 5 10.0000
  2 PROD2 6 11.0000
  3 PROD3 7 15.0000
  3 PROD3 8 15.0000
  3 PROD3 9 16.0000
  

  Now I need a query as above to return only one partno per recid,

  The result should be like this

  recid description partno price
  ----------- ----------- ----------- ---------------------
  1 PROD1 1 10.0000
  2 PROD2 4 10.0000
  3 PROD3 7 15.0000
  

  I can't get the right query for this.

  Thanks for your time

  
  
• Select Distinct