Query is not giving me what I want

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

Query is not giving me what I want

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-01-13 : 12:50:41

I am still getting all Clmssn. If I change the group by and only add [clmssn] I get what I want but when I add the other fields I don't.

SELECT DISTINCT [Clmssn], max([app Rcpdt]) as [Received], [Adju Lvl], [Inet Ind]
FROM M5s
group by [clmssn],[Adju Lvl], [Inet Ind]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 12:55:36

seems like this is what you need


SELECT  m.[Clmssn], m.[app Rcpdt], m.[Adju Lvl], m.[Inet Ind]
FROM M5s m
INNER JOIN (SELECT [Clmssn], max([app Rcpdt]) as [Received]
            FROM M5s
            group by [clmssn])m1
ON m1.[Received]=m.[app Rcpdt]
AND m1.[Clmssn]= m.[Clmssn]

SQL Server MVP

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-01-13 : 13:00:23

PERFECT! So I see I needed a subquery to get out the max items. Thanks so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 13:03:37

welcome

SQL Server MVP

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-01-13 : 13:21:54

Okay I'm back

How do I add this to the query below?

WHERE m1[Clmssn] HAVING Count(*)>1

SELECT m.[Clmssn], m.[app Rcpdt], m.[Adju Lvl], m.[Inet Ind]
FROM M5s m
INNER JOIN (SELECT [Clmssn], max([app Rcpdt]) as [Received]
FROM M5s
group by [clmssn])m1
ON m1.[Received]=m.[app Rcpdt]
AND m1.[Clmssn]= m.[Clmssn]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 13:23:45

quote:

SELECT m.[Clmssn], m.[app Rcpdt], m.[Adju Lvl], m.[Inet Ind]
FROM M5s m
INNER JOIN (SELECT [Clmssn], max([app Rcpdt]) as [Received]
FROM M5s
group by [clmssn]
HAVING Count(*)>1)m1
ON m1.[Received]=m.[app Rcpdt]
AND m1.[Clmssn]= m.[Clmssn]

modify like above

SQL Server MVP

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-01-13 : 13:31:55

Okay that didn't give me the right amount of records back. How would I add this where clause?

WHERE (((M5s.[Clmssn]) In (SELECT [Clmssn] FROM [M5s] As Tmp GROUP BY [Clmssn] HAVING Count(*)>1 )))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 13:33:45

[code]SELECT m.[Clmssn], m.[app Rcpdt], m.[Adju Lvl], m.[Inet Ind]
FROM M5s m
INNER JOIN (SELECT [Clmssn], max([app Rcpdt]) as [Received]
FROM M5s
group by [clmssn])m1
ON m1.[Received]=m.[app Rcpdt]
AND m1.[Clmssn]= m.[Clmssn]
WHERE (((M5s.[Clmssn]) In (SELECT [Clmssn] FROM [M5s] As Tmp GROUP BY [Clmssn] HAVING Count(*)>1 )))
[/code]

SQL Server MVP

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-01-13 : 13:36:42

Okay both ways are correct as I got the same number both times. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 13:37:59

cheers

SQL Server MVP

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-13 : 14:26:38

quote:
Originally posted by visakh16

cheers

SQL Server MVP

Hmmm..new signature huh...I like it..May be you also wanna include your name above tht...I feel it looks kinda odd in the reply without your name..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 00:15:53

quote:
Originally posted by vijayisonly

quote:
Originally posted by visakh16

cheers

SQL Server MVP

Hmmm..new signature huh...I like it..May be you also wanna include your name above tht...I feel it looks kinda odd in the reply without your name..

yup will add that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources