Grouping question - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Grouping question

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-17 : 05:15:21

I have this query

SELECTvA.Manufacturer, A.Model, A.Derivative, A.LOCatCode, B.CH AS [2YearPrice], C.CH AS [3YearPrice]
FROM dbo.tblNewMatrixStd AS A INNER JOIN
dbo.tblNewMatrixFigures AS B ON A.VehicleRef = B.VehicleRef AND B.Term = 2 AND B.MilesPA = 10000 AND B.Maintained = 0 INNER JOIN
dbo.tblNewMatrixFigures AS C ON A.VehicleRef = C.VehicleRef AND C.Term = 3 AND C.MilesPA = 10000 AND C.Maintained = 0
ORDER BY A.LOCatCode DESC

In produces these results

KIA SEDONA ESTATE 2.7 V6 GS 5dr 6 269.9900 239.9900
KIA SEDONA DIESEL ESTATE 2.9 CRDi GS 5dr 6 294.9900 256.9900
KIA SEDONA DIESEL ESTATE 2.9 CRDi GS 5dr Auto 6 329.9900 286.9900
KIA SEDONA DIESEL ESTATE 2.9 CRDi LS 5dr 6 327.9900 287.9900
KIA SEDONA DIESEL ESTATE 2.9 CRDi LS 5dr Auto 6 364.9900 316.9900
KIA SEDONA DIESEL ESTATE 2.9 CRDi TS 5dr 6 375.9900 324.9900
KIA SEDONA DIESEL ESTATE 2.9 CRDi TS 5dr Auto 6 409.9900 353.9900
NISSAN NOTE HATCHBACK 1.4 S 5dr 6 145.9900 139.9900
NISSAN NOTE HATCHBACK 1.4 SE 5dr 6 173.9900 162.9900
NISSAN NOTE DIESEL HATCHBACK 1.5 dCi S 5dr 6 168.9900 159.9900
NISSAN NOTE DIESEL HATCHBACK 1.5 dCi SE 5dr 6 196.9900 179.9900
NISSAN NOTE HATCHBACK 1.6 S 5dr 6 163.9900 156.9900
NISSAN NOTE HATCHBACK 1.6 SE 5dr 6 193.9900 176.9900
NISSAN NOTE HATCHBACK 1.6 SE 5dr Auto 6 215.9900 194.9900
KIA CARENS DIESEL ESTATE 2.0 CRDI GS 5dr 6 254.9900 219.9900
KIA CARENS DIESEL ESTATE 2.0 CRDI GS 5dr [7 Seat] 6 259.9900 229.9900
KIA CARENS DIESEL ESTATE 2.0 CRDI GS 5dr Auto [7 Seat] 6 294.9900 254.9900
KIA CARENS DIESEL ESTATE 2.0 CRDI LS 5dr [7 Seat] 6 291.9900 254.9900
KIA CARENS DIESEL ESTATE 2.0 CRDI LS 5dr Auto [7 Seat] 6 324.9900 279.9900
KIA CARENS DIESEL ESTATE 2.0 CRDI GS 5dr 6 249.9900 212.9900
KIA CARENS ESTATE 2.0 S 5dr 6 224.9900 194.9900

I need to group by model, so in this case

SEDONA ESTATE
SEDONA DIESEL ESTATE
NOTE HATCHBACK
NOTE DIESEL HATCHBACK
CARENS DIESEL ESTATE
CARENS ESTATE

I also need to retain the cheapest price for each grouping

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-17 : 05:18:44

what is the expected result ?

Can you please also format the result you posted above using the [code] tag ? Can't identify which data is from which column

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-18 : 03:45:43

What do you mean by grouping?
It seems you want to suppress if the value is duplicated?

Madhivanan

Failing to plan is Planning to fail

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-07-23 : 07:42:03

Hope this comes out correctly in the code tags.


AUDI	A3 HATCHBACK SPECIAL EDITIONS	1.6 Special Edition 3dr	4	229.9900	219.9900
AUDI	A3 SPORTBACK SPECIAL EDITIONS	1.6 Special Edition 5dr	4	227.9900	220.9900
AUDI	A3 HATCHBACK SPECIAL EDITIONS	1.9 TDi Special Edition 3dr	4	261.9900	237.9900
AUDI	A3 SPORTBACK SPECIAL EDITIONS	1.9 TDi Special Edition 5dr	4	255.9900	239.9900
FIAT	PANDA HATCHBACK	1.1 Active 5dr	1	138.9900	117.9900
FIAT	PANDA HATCHBACK	1.2 Dynamic 5dr	1	143.9900	122.9900
FIAT	PANDA HATCHBACK	1.2 Dynamic 5dr [AC]	1	148.9900	128.9900
FIAT	PANDA HATCHBACK	1.2 Eleganza 5dr	1	158.9900	137.9900
FIAT	PANDA HATCHBACK	1.4 16v 100HP 5dr	1	178.9900	154.9900
FIAT	GRANDE PUNTO HATCHBACK	1.2 Active 5dr	1	164.9900	147.9900
FIAT	GRANDE PUNTO HATCHBACK	1.2 Active 3dr [AC]	1	160.9900	137.9900
FIAT	GRANDE PUNTO HATCHBACK	1.2 Dynamic 3dr	1	164.9900	148.9900
FIAT	GRANDE PUNTO HATCHBACK	1.2 Dynamic 5dr	1	174.9900	154.9900
FIAT	GRANDE PUNTO DIESEL HATCHBACK	1.3 Multijet Active 5dr	1	176.9900	154.9900
FIAT	GRANDE PUNTO DIESEL HATCHBACK	1.3 Multijet Active 3dr	1	170.9900	147.9900
FIAT	GRANDE PUNTO DIESEL HATCHBACK	1.3 Multijet Active 3dr [AC]	1	178.9900	154.9900
FIAT	GRANDE PUNTO DIESEL HATCHBACK	1.3 Multijet Active 5dr [AC]	1	185.9900	160.9900
FIAT	GRANDE PUNTO HATCHBACK	1.4 16V Active Sport 3dr	1	172.9900	153.9900
FIAT	GRANDE PUNTO HATCHBACK	1.4 16V Active Sport 5dr	1	179.9900	160.9900
FIAT	GRANDE PUNTO HATCHBACK	1.4 16V Dynamic Sport 3dr	1	184.9900	163.9900
FIAT	GRANDE PUNTO HATCHBACK	1.4 16V Dynamic Sport 5dr	1	191.9900	169.9900
FIAT	GRANDE PUNTO HATCHBACK	1.4 16v Sporting 3dr	1	191.9900	169.9900

General Anders
Starting Member

3 Posts

Posted - 2007-07-23 : 19:46:37

It looks like you are trying to use parameters for the WHERE clause at the same place as your ON clause for the join parameters. Perhaps you need to separate out the WHERE information

AND B.Term = 2 AND B.MilesPA = 10000 AND B.Maintained = 0
AND C.Term = 3 AND C.MilesPA = 10000 AND C.Maintained = 0

I'm somewhat new to SQL so correct me if I'm wrong, but this information should be in the WHERE.

WHERE (B.Term = 2 AND B.MilesPA = 10000 AND B.Maintained = 0) AND (C.Term = 3 AND C.MilesPA = 10000 AND C.Maintained = 0)

General Anders
Starting Member

3 Posts

Posted - 2007-07-23 : 19:49:48

quote:
Originally posted by madhivanan

What do you mean by grouping?
It seems you want to suppress if the value is duplicated?

Madhivanan

Failing to plan is Planning to fail

Yeah- it sounds like you mean to order by. Group by will attempt to remove duplicate entries, so you may just need to do multiple order by I.E.

order by manufacturer, make, model

will first order by manufacturer, then 2nd priority order by make, and third priority order by model.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources