select latest value

[christer99]

How do I select the latest quote value for each opportunity group (GROUPOPID)? The statement below just list all the quotes and not the latest for each groupopid. If I do MAX(QUOTE) I just sent get the maximum value and not the latest quote (which might be less than the maximum). I appreciate your help. MMID is the ID.

sELECT TOP 100 PERCENT OPMarketing.BCOID, MAX(OPMarketing.MID) AS MMID, (OPMarketing_1.QUOTE) as bb
FROM OPMarketing INNER JOIN
OPMarketing OPMarketing_1 ON OPMarketing.MID = OPMarketing_1.MID
WHERE (OPMarketing.GROUPOPID=213) AND (OPMarketing.QUOTE > 0)
GROUP BY OPMarketing.BCOID, OPMarketing_1.QUOTE
ORDER BY mmid DESC

 

[bborissov]

How do you know what is the last quote?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

What is QUOTE?

Do you have a date column anywhere you can use to identify which is the latest one, or are you just using ID?

Basically what you want to do is create a subquery that contains the PK values for all of the latest quotes, then join to that subquery to get the entire row associated with each PK. Make sense?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[christer99]

The date column is opmarketing.MODIFYDATE Do you have an example?

 

[monksnake]

This should get you what you want, if I understood your fields correctly:

SELECT A.BCOID, A.MID, A.QUOTE 
FROM OPMarketing A
WHERE A.MID = (SELECT MAX(MID) FROM OPMarketing WHERE QUOTE = A.QUOTE) AND A.GROUPOPID=213 AND A.QUOTE > 0 
GROUP BY OPMarketing.BCOID, OPMarketing_1.QUOTE
ORDER BY mmid DESC

<.

 

[r937]

SELECT BCOID
     , MID   
     , QUOTE 
  FROM OPMarketing as T
 WHERE GROUPOPID = 213
   AND QUOTE > 0 
   AND MID =
       ( select max(MID)
           from OPMarketing 
          where GROUPOPID = 213
            AND QUOTE > 0 
            AND BCOID = T.BCOID )
ORDER 
    BY MID DESC

r937.com | rudy.ca

 

[monksnake]

Let me redo the bottom:

SELECT A.BCOID, A.MID, A.QUOTE 
FROM OPMarketing A
WHERE A.MID = (SELECT MAX(MID) FROM OPMarketing WHERE QUOTE = A.QUOTE) AND A.GROUPOPID=213 AND A.QUOTE > 0 
GROUP BY A.BCOID, A.QUOTE
ORDER BY A.MID DESC

<.

 

[r937]

monk, why the GROUP BY?

and you need to repeat the WHERE conditions inside the subquery



r937.com | rudy.ca

 

[AlexCuse]

This should work, going by the date column you specified to identify the maximums). Not tested though:

[COLOR=blue]sELECT[/color] TOP 100 PERCENT       OPMarketing.BCOID
, (OPMarketing.MID) [COLOR=blue]AS[/color] MMID
,  (OPMarketing.QUOTE) [COLOR=blue]as[/color] bb
[COLOR=blue]FROM[/color]         
OPMarketing [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color]
(
[COLOR=blue]SELECT[/color] BCOID
, MID
, QUOTE
, [COLOR=#FF00FF]MAX[/color](MODIFYDATE) AS MODIFYDATE
[COLOR=blue]from[/color] OPMarketing
[COLOR=blue]group[/color] [COLOR=blue]by[/color] BCOID
, MID
, QUOTE
) A [COLOR=blue]ON[/color] OPMarketing.MID = A.MID
AND OPMarketing.MODIFYDATE = A.MODIFYDATE
[COLOR=blue]WHERE[/color]     (OPMarketing.GROUPOPID=213)  AND (OPMarketing.QUOTE > 0) 
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] mmid [COLOR=#FF00FF]DESC[/color]

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Note - you might need to remove MID from the subquery, if this is your primary key value (because you wouldn't want to group by this to find the max).

If this is the case, you might need to find something else to join on besides just the date. I don't know your data well enough to make a suggestion.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom