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 |
|
msch-prv
Starting Member
11 Posts |
 Posted - 2008-12-07 : 15:00:07
|
| I have a series of items (articles) stored in a SQL Server table. All articles are referenced by a PK, ArtID (int), and a subkey, ArtIdx (string). ArtIdx specifies the category the article belongs to (ie 'AA', 'ABD', 'BCA', etc.). Each article record includes the time it was posted, a title, and so on.Table arrangement (simplified):ArtID ArtIdx ArtAdded ArtTitle ...120 AAC 01/01/2007 Article1 ...121 BD 10/02/2007 Article2 ...122 DAC 20/07/2008 Article3 ...123 BD 18/01/2008 Article4 ...124 AAC 23/05/2008 Article5 ...125 AAB 18/05/2008 Article6 ...Problem. For each main category, ArtCat (ie A, B, D, etc.), I would like to retrieve the descendant that was posted last, ie:ArtCat ArtID ArtIdx ArtAdded ArtTitle ...A 124 AAC 23/05/2008 Article5 ...B 123 BD 18/01/2008 Article4 ...D 122 DAC 20/07/2008 Article3 ...I have been trying with SELECT DISTINCT, subqueries to no avail. I am not a SQL expert, but I believe that probably there is a clever way to achieve this w/o looping over the ArtIdx key set. Any hints would be most welcome. TIA. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-07 : 16:45:23
|
SELECT *FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ArtIdx ORDER BY ArtAdded DESC) AS recID FROM Table1) AS dWHERE recID = 1
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
msch-prv
Starting Member
11 Posts |
Posted - 2008-12-07 : 16:56:25
|
| Peso,Fantastic!! Elegant and concise. Thank you very much. |
|
|
|
|
|
|
|
|
Annoying Selection problem