SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Group by or Distinct - But several fields
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/11/2014 :  12:37:58  Show Profile  Reply with Quote
Hello everyone,

How can I use a Distinct or Group by statement on 1 field when calling All or at least several ones.

Example:
SELECT id_product, description_fr, DiffMAtrice, id_mark, id_type, NbDiffMatrice, nom_fr, nouveaute
From C_Product_Tempo

And I want Distinct or Group By nom_fr

Thanks in advance

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/11/2014 :  14:21:51  Show Profile  Reply with Quote
The column(s) you GROUP BY control the number of rows. One row per distinct column(s) in your Group By. Any columns SELECTed that are not part of the group by must be aggregated in some way. ie: min, max, avg. If there are several different [id_product] values for one [nom_fr] which one did you want to see?

One option could be to return one row for each [nom_fr] where the row is the first in some definable order. Perhaps something like this - you can change the ORDER BY to whatever makes sense:

select id_product, description_fr, DiffMAtrice, id_mark, id_type, NbDiffMatrice, nom_fr, nouveaute
from   (
       SELECT rn = row_number() over (partition by [nom_fr] order by id_mark)
              , id_product, description_fr, DiffMAtrice, id_mark, id_type, NbDiffMatrice, nom_fr, nouveaute
       From C_Product_Tempo
       ) d
where  rn = 1


Be One with the Optimizer
TG
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/11/2014 :  14:26:43  Show Profile  Reply with Quote
This is perfect! Thank you so much TG
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/11/2014 :  16:46:28  Show Profile  Reply with Quote
If it helps anyone, another solution who works on this example:
SELECT * FROM C_Product_Tempo WHERE id_product IN (
SELECT MIN(id_product) FROM C_Product_Tempo GROUP BY nom_fr
)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/11/2014 :  23:31:04  Show Profile  Reply with Quote
quote:
Originally posted by Lesombrero

If it helps anyone, another solution who works on this example:
SELECT * FROM C_Product_Tempo WHERE id_product IN (
SELECT MIN(id_product) FROM C_Product_Tempo GROUP BY nom_fr
)


much better approach is this

SELECT * FROM C_Product_Tempo pt 
WHERE NOT EXISTS(
  SELECT 1 FROM C_Product_Tempo 
  WHERE nom_fr = pt.nom_fr
  AND id_product < pt.id_product
)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/12/2014 :  00:31:31  Show Profile  Reply with Quote
It works really good too! And just to be clear with, the reason of my problem is just to don't have double nom_fr!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/12/2014 :  00:53:42  Show Profile  Reply with Quote
quote:
Originally posted by Lesombrero

It works really good too! And just to be clear with, the reason of my problem is just to don't have double nom_fr!


ok
EXISTS works better than IN clause mostly as it does a boolean value check

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/12/2014 :  01:32:20  Show Profile  Reply with Quote
I would prefer this one but it doesn't give the same result!
SELECT COUNT(*) FROM C_Product_Tempo
WHERE NOT EXISTS(
SELECT 1 FROM C_Product_Tempo pt
WHERE pt.nom_fr = nom_fr
AND pt.id_product < id_product
)

Because the complete request would be:
SELECT * FROM C_Product_Tempo WHERE EXISTS
(SELECT 1 FROM C_Product_Tempo pt WHERE pt.nom_fr = nom_fr AND pt.id_product < id_product)

AND (id_matrice IS NULL OR DiffMatrice IS NULL) AND masquer = 0
AND (SaleAccess < 2 OR SaleAccess IS NULL)
AND (fourn_no_sale IS NULL OR fourn_no_sale NOT LIKE '%DE%')
AND (id_type = 1015 OR Chemin LIKE '%1015%')
ORDER by id_Product DESC

Result = 0 :-(
With your original I had 105 and that was correct ;-)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/12/2014 :  05:22:36  Show Profile  Reply with Quote
quote:
Originally posted by Lesombrero

I would prefer this one but it doesn't give the same result!
SELECT COUNT(*) FROM C_Product_Tempo
WHERE NOT EXISTS(
SELECT 1 FROM C_Product_Tempo pt
WHERE pt.nom_fr = nom_fr
AND pt.id_product < id_product
)

Because the complete request would be:
SELECT * FROM C_Product_Tempo WHERE EXISTS
(SELECT 1 FROM C_Product_Tempo pt WHERE pt.nom_fr = nom_fr AND pt.id_product < id_product)

AND (id_matrice IS NULL OR DiffMatrice IS NULL) AND masquer = 0
AND (SaleAccess < 2 OR SaleAccess IS NULL)
AND (fourn_no_sale IS NULL OR fourn_no_sale NOT LIKE '%DE%')
AND (id_type = 1015 OR Chemin LIKE '%1015%')
ORDER by id_Product DESC

Result = 0 :-(
With your original I had 105 and that was correct ;-)


thats because you're not checking for conditions inside

try this




;With Temp
AS
(
SELECT * FROM C_Product_Tempo 
WHERE (id_matrice IS NULL OR DiffMatrice IS NULL) AND masquer = 0
AND (SaleAccess < 2 OR SaleAccess IS NULL)
AND (fourn_no_sale IS NULL OR fourn_no_sale NOT LIKE '%DE%') 
AND (id_type = 1015 OR Chemin LIKE '%1015%')
)

SELECT *
FROM Temp t
WHERE NOT EXISTS(
  SELECT 1 FROM Temp  pt
  WHERE pt.nom_fr = nom_fr
  AND pt.id_product < id_product
)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/12/2014 :  06:39:41  Show Profile  Reply with Quote
This is the result I have for the nom_fr column! I don't want any double!
Pole Line 24
Pole Line 24
Pole Line 24
Pole Line 24
Pole Line 24
Pole Line 24
Pole Line 01
Pole Line 01
Pole Line 01
Pole Line 01
Pole Line 01
Pole Line 01
Pole Line 02
Pole Line 02
Pole Line 02
Pole Line 02
Pole Line 02
Pole Line 02

Result should be
Pole Line 24
Pole Line 01
Pole Line 02

Edited by - Lesombrero on 02/12/2014 06:41:18
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/12/2014 :  07:36:16  Show Profile  Reply with Quote
use DISTINCT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/12/2014 :  07:48:53  Show Profile  Reply with Quote
Where am I supposed to place DISTINCT, because we SELECT ALL!?!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/12/2014 :  08:32:08  Show Profile  Reply with Quote
what all fields you want in final output? do you want distinct based on all of them or just a subset of columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/12/2014 :  08:55:42  Show Profile  Reply with Quote
These are the fields I need in final output:
id_product, description_fr, DiffMAtrice, id_mark, id_type, NbDiffMatrice, nom_fr, nouveaute, picture, prix_ttc, prix_promo_ttc, ref, TxtMarque, TxtTitle_fr, TxtType_fr
I want DISTINCT on nom_fr
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/13/2014 :  07:52:09  Show Profile  Reply with Quote
quote:
Originally posted by Lesombrero

These are the fields I need in final output:
id_product, description_fr, DiffMAtrice, id_mark, id_type, NbDiffMatrice, nom_fr, nouveaute, picture, prix_ttc, prix_promo_ttc, ref, TxtMarque, TxtTitle_fr, TxtType_fr
I want DISTINCT on nom_fr


can product id repeat for same nom_fr value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/13/2014 :  08:08:23  Show Profile  Reply with Quote
No. id_Product is int unique and primary key
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/13/2014 :  08:20:15  Show Profile  Reply with Quote
quote:
Originally posted by Lesombrero

No. id_Product is int unique and primary key


then my earlier suggestion should work for you

;With Temp
AS
(
SELECT * FROM C_Product_Tempo 
WHERE (id_matrice IS NULL OR DiffMatrice IS NULL) AND masquer = 0
AND (SaleAccess < 2 OR SaleAccess IS NULL)
AND (fourn_no_sale IS NULL OR fourn_no_sale NOT LIKE '%DE%') 
AND (id_type = 1015 OR Chemin LIKE '%1015%')
)

SELECT *
FROM Temp t
WHERE NOT EXISTS(
  SELECT 1 FROM Temp  pt
  WHERE pt.nom_fr = nom_fr
  AND pt.id_product < id_product
)





if this is not working give some sample data and explain what you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/13/2014 :  08:39:58  Show Profile  Reply with Quote
The where part is perfect. Just have x time nom_fr in the result and I want just 1
Overview of a part of the results:
ref - nom_fr
PH-RU-5115-3-24-1202 Barre Line 24
PH-RU-5115-3,5-24-1206 Barre Line 24
PH-RU-5115-4-24-1209 Barre Line 24
PH-RU-5120-3-24-1210 Barre Line 24
PH-RU-5120-3,5-24-1211 Barre Line 24
PH-RU-5120-4-24-1212 Barre Line 24
PH-RU-5115-3-01-1213 Barre Line 01
PH-RU-5120-3-01-1229 Barre Line 01
PH-RU-5115-3,5-01-1230 Barre Line 01
PH-RU-5115-4-01-1231 Barre Line 01
PH-RU-5120-3,5-01-1232 Barre Line 01
PH-RU-5120-4-01-1233 Barre Line 01
PH-RU-5115-3-02-1234 Barre Line 02
PH-RU-5120-3-02-1235 Barre Line 02
PH-RU-5115-3,5-02-1236 Barre Line 02
PH-RU-5115-4-02-1237 Barre Line 02
PH-RU-5120-3,5-02-1238 Barre Line 02
PH-RU-5120-4-02-1239 Barre Line 02
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/13/2014 :  12:05:56  Show Profile  Reply with Quote

;With Temp
AS
(
SELECT * FROM C_Product_Tempo 
WHERE (id_matrice IS NULL OR DiffMatrice IS NULL) AND masquer = 0
AND (SaleAccess < 2 OR SaleAccess IS NULL)
AND (fourn_no_sale IS NULL OR fourn_no_sale NOT LIKE '%DE%') 
AND (id_type = 1015 OR Chemin LIKE '%1015%')
)
SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY nom_fr ORDER BY ref DESC) AS Rn
FROM Temp t
WHERE NOT EXISTS(
  SELECT 1 FROM Temp  pt
  WHERE pt.nom_fr = nom_fr
  AND pt.id_product < id_product
)
)t
WHERE Rn = 1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 02/13/2014 :  13:43:04  Show Profile  Reply with Quote
It works. Thank you so much.
We are pretty close from TG's solution up there.
He uses a row number, you use a temp table. The result is the same.
Now question from a beginner to the masters:
Which one is best in term of efficacy, quickness, and consumption?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000