group by max date

Question

Post reply

group by max date

andrew reale

SSC Enthusiast

Points: 188
More actions
February 10, 2005 at 4:56 pm

#65983

i have a home page with 4 feature slots
i want to select the feature with the most recent date from my features table for each slot
so
feature_id feature_text,feature_sequence feature_date are my cols
is there a single or coompund slect statement that can grab the most recent date for each sequcne value or do i need to create a temp table or do a loop?
loops are fine but then i get 4 diff result sets.

select max(feature_date),feature_sequence from features group by feature_sequence
gives me maxdate for each position but i need to get at the rest of the data
Best,
andrew reale
minderbinder.net

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Joint to a virtual table based off the same table:

Select feature_id feature_text,feature_sequence, feature_date

From features As f

Inner Join

( -- virtual table

select max(feature_date) As MostRecent, feature_sequence

from features

group by feature_sequence

) vt

On (vt.feature_sequence = f.feature_sequence And

vt.MostRecent = f.feature_date)

TriggerMe SSChampion Points: 11752 More actions · Answer 2

Try this and tell me if it produces what you need!

SELECT feature_id, feature_text, feature_sequence, feature_date

FROM XYZ A

WHERE feature_date = (SELECT MAX(feature_date)

FROM XYZ B

WHERE A.feature_id = B.feature_id)

Kindest Regards,

andrew reale SSC Enthusiast Points: 188 More actions · Answer 3

i was hoping to return a dataset with 4 rows one for each feature position. Do i need to set rowcount?

Best,

andrew reale
minderbinder.net

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

You shouldn't need rowcount.

You didn't explain which data column determines the grouping. In the code I posted above, I assumed feature_sequence contains the number from 1 through 4 which determines which group a feature belongs in.

No-one can really answer this 100% correctly until you flesh out the details of what the data means - what is feature_sequence ? What piece of data determines which of the 4 groups a feature belongs in ?

You initially posted this:

>>grab the most recent date for each sequence value

This implies feature_sequence contains a number from 1 to 4 which determines the slot, in which case the query I posted above should work, because the virtual table grabs 1 row per feature_sequence. So this is not the case ? The query didn't work ?

andrew reale SSC Enthusiast Points: 188 More actions · Answer 5

Sorry for being vague

Feature sequence is indeed a number from 1 to 4 that controls the order in which my 4 features appear on a homepage.

the page always has 4 features on on it. i'd like to have the most recent feature for sequence one followed by the most recent for sequence two etc

the reason is my editors sometime only want to update one of the features so if they leave features 1 3 and 4 but update feature2 then my page would reflect the one change

i could just run 4 diff queries but this seems wasteful

am i still being vague?

Best,

andrew reale
minderbinder.net

PW-201837 SSC-Insane Points: 20805 More actions · Answer 6

Given that info, the query I posted above should work. Did you try it ? Did it not return the correct 4 rows ?

andrew reale SSC Enthusiast Points: 188 More actions · Answer 7

here's the actual query i'm running

i goofed to column names in my initial post but its the same deal really

SELECT feature_id, feature_text, feature_sequence, publish_startdate

FROM homefeatures A

WHERE publish_startdate = (SELECT MAX(publish_startdate)

FROM homefeatures B

WHERE A.feature_id = B.feature_id)

i get all the rows in the table back and they appear to be sorted by date desc

Best,

andrew reale
minderbinder.net

PW-201837 SSC-Insane Points: 20805 More actions · Answer 8

That's not the query I posted. See the very 1st reply, above, using a join to a virtual table.

andrew reale SSC Enthusiast Points: 188 More actions · Answer 9

when i run this

Select feature_id feature_text,feature_sequence, publish_startdate

From homefeatures As f

Inner Join

( -- virtual table

select max(publish_startdate) As MostRecent, feature_sequence

from homefeatures

group by feature_sequence

) vt

On (vt.feature_sequence = f.feature_sequence And

vt.MostRecent = f.publish_startdate)

i get Server: Msg 209, Level 16, State 1, Line 10

Ambiguous column name 'Feature_Sequence'.

Best,

andrew reale
minderbinder.net

PW-201837 SSC-Insane Points: 20805 More actions · Answer 10

>>Ambiguous column name 'Feature_Sequence'.

Right, but it's an easy fix to prefix each occurrence of feature_sequence with the table alias to remove the ambiguity.

Select feature_id, feature_text, f.feature_sequence, publish_startdate

andrew reale SSC Enthusiast Points: 188 More actions · Answer 11

i'm an idiiot. i thought i had tried that and in fact i had but while doing it i screwed up something else.

Thanks a billion for your help and patience.

I think i understnad it conceptually. So i'm joing my actual table to a virtual table of only the highest dates ON the sequence and date?

Best,

andrew reale
minderbinder.net