Can't figure out this Query Please help

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

Transact-SQL (2008)

Can't figure out this Query Please help

Author

Topic

mutlyp
Starting Member

20 Posts

Posted - 2014-04-18 : 14:28:44

I have 3 records:

ID To_LOC CountNum

1890433 Cal 2
1890433 Den 1
1890433 WASTE 3

After I run the query I would like this returned

ID To_LOC CountNum
1890433 WASTE 3

I have tried many different types of queries. This is the last query I tried:

SELECT DISTINCT TOP (100) PERCENT ID, TO_LOC, MAX(DISTINCT CountNum) AS CountNum
FROM Database
GROUP BY ID, TO_LOC
HAVING (ID = '1890433')

But that query gave me back:
ID TO_LOC CountNum
1890433 Cal 2
1890433 Den 1
1890433 WASTE 3

Please tell me what I am doing wrong this is driving me crazy.

Thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-18 : 14:46:58

maybe this?


select id, to_loc, countNum
from  (
       select id, to_loc, countNum, row_number() over (partition by id order by countNum desc) as rn
       ) d
where d.rn = 1

Be One with the Optimizer
TG

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-21 : 01:05:45

[code]
SELECT TOP 1 ID,To_LOC,CountNum FROM @Temp ORDER BY CountNum DESC
[/code]

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-21 : 13:25:45

First off, you shouldn't use HAVING the way you are. HAVING should only be used for aggregated columns. It will work for non-aggregated rows but that's not the way to do it. The secret is this: WHERE filters records before aggregation. HAVING does it after. Yes, the query plan may be the same for simple examples like this but you might as well develop good habits from the start!


SELECT DISTINCT TOP (100) PERCENT ID, TO_LOC, MAX(DISTINCT CountNum) AS CountNum
FROM Database
WHERE(ID = '1890433')
GROUP BY ID, TO_LOC

This way, you can be sure that SQL will only be doing the MAX calculation for three rows you're interested in. Now, I think you want only the row with the highest CountNum. This query will do it efficiently:


SELECT DISTINCT TOP (1) ID, TO_LOC, MAX(DISTINCT CountNum) AS CountNum
FROM Database
WHERE (ID = '1890433')
GROUP BY ID, TO_LOC
ORDER BY  MAX(DISTINCT CountNum) desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources