How to read the maximum date in this SQL query?

Question

Below is the image of the query result. I want to show Tucson/Boulder only once based on maximum 'addressvalidfrom'. How can I create/modify the query?

at least paste your code as a text. show some effort. – Brett Schneider Dec 02 '13 at 16:19 — Brett Schneider, Dec 02 '13 at 16:19

score 1 · Answer 1 · answered Dec 02 '13 at 16:22

use a max for the addressvalidfrom field, and a group by for the other fields.

I can show you if you post the actual query.

http://www.w3schools.com/sql/sql_groupby.asp where the aggregate is your max(addressvalidfrom)

Can you also post what you want to get as a result if possible.

score 1 · Accepted Answer · answered Dec 02 '13 at 16:29

1

If you do not want to use grouping (to persist the rest of the query) you can add a ROW_NUMBER column and filter it where it is 1.

Example

SELECT * FROM
( -- insert your query here with new line below in the select fields
, ROW_NUMBER() OVER (PARTITION BY CUST_RETAIL_CHANNEL_NAME ORDER BY addressvalidfrom DESC) AS Rnk
) D
WHERE D.Rnk=1

answered Dec 02 '13 at 16:29

DarrenMB

2,342
1
21
26

1

Darren is correct, here is good info on row_number if you go that route http://stackoverflow.com/questions/7747327/sql-rank-versus-row-number/7747342#7747342 – Josh Dec 02 '13 at 16:39
1

Hi Darren, it served the purpose. I cant vote for it because i dont have 15 reputation but this is what i required. thank you so much – Jessica Yoskovich Dec 02 '13 at 17:38

How to read the maximum date in this SQL query?

2 Answers2