Use max but no Group by :)

[sako2]

hi, i have such table
count id text date
1 123 blablaa 01.01.2004
2 123 booboo 12.12.2003
1 321 dddddd 14.01.2003
now i want to show only the max(count) ROW but without th group by the rest where id=123
i thought about
select text, date max(count) where id=123
but as u know if i use max i must group by the rest.

how to solve this?
regards.

 

[nicsin]

You can't! Why don't you want to group by anyway? I don't see a reason to use max() and don't want to group.

 

[sako2]

if i group by the date for example i will get all rows where id=123,
and i dont want that.
i searched the forum and i found sth like TOP 10
thread220-733075 i need ONLY the last ROW in my example the raw with count=2

btw, this count is varialbe so users have 100 count other have 200 count, i cant program this number hard. thats y i need a max.

 

[nicsin]

Will this do what you want?

select * 
from <yourTable> 
having count = (select max(count)from <yourTable>)

 

[goranm]

Hi !
I was just writing a similar approach as "nicsin".

select text, date, count from TABLE where id = '123' and count = (select max(count) from TABLE where id = '123')

/Goran

 

[nicsin]

Sorry you don't need the having keyword. just use where

select * 
from <yourTable> 
where count = (select max(count)from <yourTable>)

 

[nicsin]

yes Goran, I believe that would do the trick.

 

[sako2]

thank you guys. the example helped

i wonder that there is no sth like the Opposite of TOP 1

if i say select TOP 1 count, text from Table where id=123
i will get the ROW where count=1.

sql will be more powerful if it includes sth like TOP max to give the LAST ROW.


thx.

 

[nicsin]

I believe TOP is only useful if you sort the results. This would work as well.

select TOP 1 * 
from yourTable 
order by count

 

[sako2]

hi nicsin,

no Top 1 wont work.
i will give the row with count=1.
i need the count= 2 in the example up

thx

 

[nicsin]

Oops that should be

select TOP 1 * 
from yourTable 
order by count desc

 

[sako2]

but this will work

SELECT TOP 1 ID
FROM Table
WHERE (id = 123)
ORDER BY count DESC

thx again SQL is POWERFUL

 

[nicsin]

Yep, as I corrected in my previous post you have to use the desc keyword because the default is asc.

All the best