Group by or Distinct - But several fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by or Distinct - But several fields

Author

Topic

Lesombrero
Starting Member

43 Posts

Posted - 2014-02-11 : 12:37:58

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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-11 : 14:21:51

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

Lesombrero
Starting Member

43 Posts

Posted - 2014-02-11 : 14:26:43

This is perfect! Thank you so much TG

Lesombrero
Starting Member

43 Posts

Posted - 2014-02-11 : 16:46:28

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
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-11 : 23:31:04

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

Lesombrero
Starting Member

43 Posts

Posted - 2014-02-12 : 00:31:31

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 00:53:42

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

Lesombrero
Starting Member

43 Posts

Posted - 2014-02-12 : 01:32:20

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 ;-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 05:22:36

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

Lesombrero
Starting Member

43 Posts

Posted - 2014-02-12 : 06:39:41

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 07:36:16

use DISTINCT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Lesombrero
Starting Member

43 Posts

Posted - 2014-02-12 : 07:48:53

Where am I supposed to place DISTINCT, because we SELECT ALL!?!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 08:32:08

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

Lesombrero
Starting Member

43 Posts

Posted - 2014-02-12 : 08:55:42

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-13 : 07:52:09

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

Lesombrero
Starting Member

43 Posts

Posted - 2014-02-13 : 08:08:23

No. id_Product is int unique and primary key

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-13 : 08:20:15

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

Lesombrero
Starting Member

43 Posts

Posted - 2014-02-13 : 08:39:58

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-13 : 12:05:56

[code]
;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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Lesombrero
Starting Member

43 Posts

Posted - 2014-02-13 : 13:43:04

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources