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
 MAX in WHERE

Author  Topic 

golyath
Starting Member

21 Posts

Posted - 2007-10-09 : 06:25:30
Hi,

My Problem:

I have a table that has Position(Number), Id(Number) + more columns.
This is similar to a transaction table so records are added with the same ID and not updated.

I want to be able to select some values for where Position is 5 but Also where the MAX Position for the ID is < 10.

Trying to explain better:
So if i have 3 records for ID '1234' With position 1, 5, 9 respectivly.

I want to be able to select the values at 5 where the record for this ID with the highest position (in this case 9) is less than 10.

Thanks for anyhelp in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 06:28:00
[code]SELECT ID,
MAX(Position)
FROM Table1
WHERE Position < 10
GROUP BY ID[/code]
E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-09 : 06:29:31
[code]Select * from Table t1
Where ID = '1234'
and Position = (Select Max(Position) from Table t2
where t2.ID = t1.ID and t2.Position < 10)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

golyath
Starting Member

21 Posts

Posted - 2007-10-09 : 06:52:04
Thanks for the quick reply,

This has helped me understand it better thank you.

Just for clarification can i use a MAX in a a WHERE clause?

So for example:
Select the 'Name' field for the record with the highest Position for a certain ID.

Basically:

SELECT Name
FROM Table
WHERE ID = '1234'
AND position = MAX(position)

Thanks for any Help
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-09 : 06:54:59
No you can't use aggregate function like MAX() in WHERE clause.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 06:55:35
Try! If you get an error, the query most probably will not work.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

golyath
Starting Member

21 Posts

Posted - 2007-10-09 : 06:58:55
I have tried it but i thought there maybe a slightly different way of producing the way i want.

I was reading around and people were saying about using the HAVING function...

Can i use this instead?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 07:05:21
"Just for clarification can i use a MAX in a a WHERE clause?"

No, but maybe you are thinking of this:

SELECT ID
FROM Table1
GROUP BY ID
HAVING position = MAX(position)

although SQL may barf at this because [position] is not exposed in the select list nor an aggregate, but as a basis for Education it may give you some thoughts!!

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-09 : 07:22:54
or

SELECT Top 1 Name
FROM Table
WHERE ID = '1234'
order by position desc

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

golyath
Starting Member

21 Posts

Posted - 2007-10-09 : 07:40:26
Thanks again for all your help.

I have used the method suggested by madhivanan.

And managed to get it working fine...
Go to Top of Page
   

- Advertisement -