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.
| Author |
Topic |
|
parallon
Starting Member
25 Posts |
 Posted - 2007-03-20 : 14:59:42
|
Hello all. I have the following statement which I am having problems with:SELECT Max(odThis) as ThisOD, Max(odLast) as LastOD, id FROM tblEqOdometer WHERE EqNum = '" & EqNum & "' group by Id The thing is that I need the ID for another process, but using the Group By Id it is not giving me the Max(odThis) that I need, it seems like it is giving the first record that it comes across. Is there a way to either get rid of the Group By or do an Order By the Id? I really don't need them grouped, but it is forcing me to since I used the Sum() function.Thanks,Parallon |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-03-20 : 15:33:20
|
| Please don't concatenate together sql strings like that.... read:http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspxIt is very, very important to follow best practices and to use parameters; even better to use stored procedures which makes things even easier. I know it seems like more work at first to do it this way, but if you read my article you'll see it is actually *easier* to use parameters rather than concatenating together sql statements like you are doing.As for your sql, you'll need to give us more specific info as to what you need, maybe with some sample data to explain the issue you are having.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
parallon
Starting Member
25 Posts |
Posted - 2007-03-20 : 17:05:19
|
| Thank you for your reply. Here is some sample data. What I need the system to do is to find the Highest odometer reading in the [COLOR="Blue"]'OdThis' [/COLOR]column and use that line item. The reason I need the ID is so when the user Submits the report, I can change the value of the [COLOR="Blue"]'Submitted' [/COLOR]column for the appropriate ID.So, with this query, I am getting a result of 1000 instead of 2500.[CODE]ID EqNum WeekStart WeekEnd OdLast OdThis Submitted178 TRK-0001 3/7/2007 3/13/2007 0 1000 No180 TRK-0001 3/14/2007 3/20/2007 1000 1500 No182 TRK-0001 3/21/2007 3/27/2007 1500 2100 No183 TRK-0001 3/28/2007 4/3/2007 2100 2500 No[/CODE]Thanks again,Parallon |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-20 : 17:13:45
|
| select t1.* from table1 as t1where t1.odthis = (select max(t2.odthis) from table1 as t2 where t2.eqnum = t1.eqnum)select t.* from table1 as tinner join (select max(odthis) as y, eqnum from table1 group by eqnum) as x on x.eqnum = t.eqnum and x.y = t.odthisPeter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|
|
Aggregate Function problem...