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 |
|
RichardSteele
Posting Yak Master
160 Posts |
 Posted - 2008-04-16 : 07:34:28
|
| How do I pull only the latest shipdate record from a multiple record key?I tried the following, but it doesn't pull only the lastest date record:Select catnum, Max(shipdate) as shipdate from table_a group by catnum, shipdateThanks in advance. |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-04-16 : 07:36:56
|
| don't group by shipdateEm |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-04-16 : 07:37:53
|
| [code]Select * from table_aWhere shipdate = (select Max(shipdate) from table_a)[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-04-16 : 07:43:47
|
| SELECT TOP 1 catnum, ShipDate FROM table_a ORDER BY ShipDate DESC--Lumbago |
|
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2008-04-16 : 07:46:56
|
quote:
Originally posted by elancaster
don't group by shipdateEm
That did it! Thanks! |
|
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2008-04-16 : 07:57:58
|
quote:
Originally posted by elancaster
don't group by shipdateEm
How would I exclude those records that haven't been shipped within the last two years? So for example, catnum="AB001" has been shipped three times, 1/1/1999, 2/15/2001, 7/1/2007. I would then want to exclude that item from the rest of the query items since it was shipped within the last two years. Thus I'm trying to find out which items haven't been shipped for at least two years.Thanks in advance. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-04-16 : 08:07:41
|
| Select catnum, Max(shipdate) as shipdate from table_a group by catnumhaving Max(shipdate) < dateadd(yy, -2, getdate())--Lumbago |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-16 : 08:41:23
|
| Select catnum as shipdate from table_a group by catnum having datediff(year,Max(shipdate),GETDATE()) >2 |
|
|
|
|
|
|
|
|
Query to pull lastest date record