November 10, 2011 at 11:45 am
hi,
I have a table that i need to query to get distinct procduct # > 2009-01-01.
table structure
Product # ProductManager Status date
12345678 AAAA active 2011-01-01
12345678 AAAA active 2011-02-28
12345678 XXXX closed 2011-08-29
12345678 AAAA active 2011-11-11
or product # could look look like this, same table
Product # ProductManager Status date
12345677 AAAB active 2011-01-01
12345677 AAAB active 2011-02-28
12345677 XXXX closed 2011-08-29
i should only be able to pull one record here 12345677. i only want to pull closed status if the max status of that product is closed.
November 10, 2011 at 12:07 pm
apatel 89420 (11/10/2011)
hi,I have a table that i need to query to get distinct procduct # > 2009-01-01.
table structure
Product # ProductManager Status date
12345678 AAAA active 2011-01-01
12345678 AAAA active 2011-02-28
12345678 XXXX closed 2011-08-29
12345678 AAAA active 2011-11-11
or product # could look look like this, same table
Product # ProductManager Status date
12345677 AAAB active 2011-01-01
12345677 AAAB active 2011-02-28
12345677 XXXX closed 2011-08-29
i should only be able to pull one record here 12345677. i only want to pull closed status if the max status of that product is closed.
What information do you need in your result set? Please provide ddl for table, sample data, and expected results.
Thanks,
Jared
Jared
CE - Microsoft
November 10, 2011 at 12:08 pm
Query the table twice. The first time, group by product number and get the max date. Then join to that with a second query of the table, joining on product number and date, and check (Where clause) if the status is closed.
That'll give you what you need. Two simple queries, joined on two columns.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply