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
Go to Top of Page

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.......
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -