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 Table1WHERE Position < 10GROUP BY ID[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-10-09 : 06:29:31
|
[code]Select * from Table t1Where ID = '1234'and Position = (Select Max(Position) from Table t2 where t2.ID = t1.ID and t2.Position < 10)[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
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 NameFROM TableWHERE ID = '1234'AND position = MAX(position)Thanks for any Help |
|
|
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 AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
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" |
|
|
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? |
|
|
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 IDFROM Table1GROUP BY IDHAVING 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 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-10-09 : 07:22:54
|
orSELECT Top 1 NameFROM TableWHERE ID = '1234'order by position descMadhivananFailing to plan is Planning to fail |
|
|
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... |
|
|
|