Query using MAX give me to many results..

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 2000 Forums

SQL Server Development (2000)

Query using MAX give me to many results..

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-09-26 : 04:38:08

Hi

I have this query...

SELECT DISTINCT dbo.ARTIKEL.ARTIKELNR, MAX(dbo.MASKINTYPER.StartYear) AS StartYear
FROM dbo.AVSNITTIMASKINTYP INNER JOIN
dbo.AVSNITTSRAD ON dbo.AVSNITTIMASKINTYP.AVSNITTSID = dbo.AVSNITTSRAD.AVSNITTSID INNER JOIN
dbo.ARTIKEL ON dbo.AVSNITTSRAD.ARTIKELNR = dbo.ARTIKEL.ARTIKELNR INNER JOIN
dbo.MASKINTYPER ON dbo.AVSNITTIMASKINTYP.MASKINTYP = dbo.MASKINTYPER.MASKINTYP
GROUP BY dbo.AVSNITTIMASKINTYP.MASKINTYP, dbo.ARTIKEL.ARTIKELNR
HAVING (dbo.ARTIKEL.ARTIKELNR = '123456')

This query give me this result...

123456 NULL
123456 2001
123456 2003

But what I really would like is ...

123456 2003

How must I change the query to get this result?

Best Regards

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-26 : 04:45:47


SELECT DISTINCT dbo.ARTIKEL.ARTIKELNR, MAX(dbo.MASKINTYPER.StartYear) AS StartYear
FROM dbo.AVSNITTIMASKINTYP INNER JOIN
dbo.AVSNITTSRAD ON dbo.AVSNITTIMASKINTYP.AVSNITTSID = dbo.AVSNITTSRAD.AVSNITTSID INNER JOIN
dbo.ARTIKEL ON dbo.AVSNITTSRAD.ARTIKELNR = dbo.ARTIKEL.ARTIKELNR INNER JOIN
dbo.MASKINTYPER ON dbo.AVSNITTIMASKINTYP.MASKINTYP = dbo.MASKINTYPER.MASKINTYP
GROUP BY dbo.AVSNITTIMASKINTYP.MASKINTYP, dbo.ARTIKEL.ARTIKELNR
HAVING (dbo.ARTIKEL.ARTIKELNR = '123456') And 
dbo.MASKINTYPER.StartYear = MAX(dbo.MASKINTYPER.StartYear)

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-26 : 04:46:15

That's because why you group by BOTH MaskinTyp and Artikelnr.
Remove MaskinTyp from GROUP BY.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-26 : 04:47:26

[code]SELECT dbo.ARTIKEL.ARTIKELNR,
MAX(dbo.MASKINTYPER.StartYear) AS StartYear
FROM dbo.AVSNITTIMASKINTYP
INNER JOIN dbo.AVSNITTSRAD ON dbo.AVSNITTIMASKINTYP.AVSNITTSID = dbo.AVSNITTSRAD.AVSNITTSID
INNER JOIN dbo.ARTIKEL ON dbo.AVSNITTSRAD.ARTIKELNR = dbo.ARTIKEL.ARTIKELNR
INNER JOIN dbo.MASKINTYPER ON dbo.AVSNITTIMASKINTYP.MASKINTYP = dbo.MASKINTYPER.MASKINTYP
WHERE dbo.ARTIKEL.ARTIKELNR = '123456'
GROUP BY ~~dbo.AVSNITTIMASKINTYP.MASKINTYP,~~
dbo.ARTIKEL.ARTIKELNR[/code]

Peter Larsson
Helsingborg, Sweden

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-09-26 : 04:48:35

Hi Peter

Yes I just discovered that, but you bet me to it... Thanks!

Regards

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-09-26 : 04:56:37

Peter

There are many Null values in the startyear column, is it possible to only show records that have a value in startyear but still use the MAX option in the query. Here's what I now have...

SELECT DISTINCT
dbo.ARTIKEL.ARTIKELNR, MIN(DISTINCT dbo.MASKINTYPER.StartYear) AS StartYear, MAX(DISTINCT dbo.MASKINTYPER.EndYear) AS EndYear
FROM dbo.AVSNITTIMASKINTYP INNER JOIN
dbo.AVSNITTSRAD ON dbo.AVSNITTIMASKINTYP.AVSNITTSID = dbo.AVSNITTSRAD.AVSNITTSID INNER JOIN
dbo.ARTIKEL ON dbo.AVSNITTSRAD.ARTIKELNR = dbo.ARTIKEL.ARTIKELNR INNER JOIN
dbo.MASKINTYPER ON dbo.AVSNITTIMASKINTYP.MASKINTYP = dbo.MASKINTYPER.MASKINTYP
GROUP BY dbo.ARTIKEL.ARTIKELNR

Best Regards

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-09-26 : 05:47:53

no need to reply, I solved it...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-26 : 06:11:28

Not like this?

SELECT		dbo.ARTIKEL.ARTIKELNR,
		MIN(CASE WHEN dbo.MASKINTYPER.StartYear IS NULL THEN 9999 ELSE dbo.MASKINTYPER.StartYear END) StartYear,
		MAX(CASE WHEN dbo.MASKINTYPER.EndYear IS NULL THEN 0 ELSE dbo.MASKINTYPER.EndYear END) EndYear
FROM		dbo.MASKINTYPER
INNER JOIN	dbo.AVSNITTIMASKINTYP ON dbo.AVSNITTIMASKINTYP.MASKINTYP = dbo.MASKINTYPER.MASKINTYP
INNER JOIN	dbo.AVSNITTSRAD ON dbo.AVSNITTSRAD.AVSNITTSID = dbo.AVSNITTIMASKINTYP.AVSNITTSID
INNER JOIN	dbo.ARTIKEL ON dbo.ARTIKEL.ARTIKELNR = dbo.AVSNITTSRAD.ARTIKELNR
GROUP BY	dbo.ARTIKEL.ARTIKELNR

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-26 : 06:12:18

I don't understand why you use both DISTINCT and GROUP BY?

Peter Larsson
Helsingborg, Sweden

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-09-26 : 06:52:14

Hi Peter

I now use this...

SELECT dbo.ARTIKEL.ARTIKELNR, MIN(DISTINCT dbo.MASKINTYPER.StartYear) AS StartYear, ISNULL(MAX(DISTINCT dbo.MASKINTYPER.EndYear), 0)
AS EndYear
FROM dbo.AVSNITTIMASKINTYP INNER JOIN
dbo.AVSNITTSRAD ON dbo.AVSNITTIMASKINTYP.AVSNITTSID = dbo.AVSNITTSRAD.AVSNITTSID INNER JOIN
dbo.ARTIKEL ON dbo.AVSNITTSRAD.ARTIKELNR = dbo.ARTIKEL.ARTIKELNR INNER JOIN
dbo.MASKINTYPER ON dbo.AVSNITTIMASKINTYP.MASKINTYP = dbo.MASKINTYPER.MASKINTYP
GROUP BY dbo.ARTIKEL.ARTIKELNR
HAVING (NOT (MIN(DISTINCT dbo.MASKINTYPER.StartYear) IS NULL))

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-26 : 06:59:29

Try to run both your solution above and my suggestion above, to see which gives greater performance.

Peter Larsson
Helsingborg, Sweden

magmo
Aged Yak Warrior

558 Posts

Posted - 2006-09-26 : 07:19:18

Hi Peter

Your query retrieved all rows in the database so I modified your query so it didn't displayed any null values from the startyear column, after I did that the query took aproximatly 3 sec to run, my query also took about 3 sec to run. Your initial query that retrieved all rows took about 9 sec to run

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources