| Author |
 Topic  |
|
|
dayve
Forum Moderator
USA
5820 Posts |
 Posted - 13 March 2004 : 12:32:06
|
MS SQL does not support SELECT LAST(Record) as MS ACCESS does. I have a feature on my forum that uses a Winamp Plugin to post data to my database which is then used by my members for signatures and for displaying a collection of all submissions here:
http://www.burningsoulsforum.com/winamp_list_display.asp
I then wanted to incorporate a small statistical output of the Top 25 Winamp Posters in my forum statistics here:
http://www.burningsoulsforum.com/statistics_member.asp#wposters
I would like to add a field to show the Last Song they listened too. I was able to do a count and max on the date, but as I noted earlier I am having difficulty getting the last song. I feel that I will have to use a nested select statement here. Currently I am using a single table with the following fields:
winID, winName, winDate, winSong
SELECT winName, Count(winID) AS totalSongs, Max(winDate) AS lastPlayed FROM FORUM_WINAMP GROUP BY winName ORDER BY Count(winID) DESC;
Input is greatly welcomed from all  . On a side note, I was able to accomplish my goal using Access, but again that is because Access supports LAST operator.
|
 |
Edited by - ruirib on 13 March 2004 15:04:55 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 13 March 2004 : 15:03:51
|
LAST is just TOP 1 with the ordering inverted. I didn't understand what query you are wanting to use, so I hope the previous suggestion can help. If you want further help from me, please make it more clear the meaning of your table contents and fields.
P.S.: This is not a Snitz related SQL Server issue, so I'm moving this to the proper forum. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 13 March 2004 15:04:40 |
 |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 13 March 2004 : 18:05:00
|
ruirib, I don't think LAST is TOP 1 with the ordering reversed because of the grouping and ordering I am doing right now will prevent me from using that approach.
since I incorporated this into a snitz feature I thought I posted it in the appropriate area. there is more to this mod that is integrated into my forum. see here for more info and then let me know if you still don't think it is snitz related:
http://www.burningsoulsforum.com/topic.asp?TOPIC_ID=8741
anyway, do you understand what I am trying to accomplish and why Top 1 with reverse ordering will not work? |
|
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 13 March 2004 : 18:50:14
|
Here is an example result using Access linked to my MS SQL tables:
and this was the query I used to accomplish it:
SELECT dbo_FORUM_WINAMP.winName,
Count(dbo_FORUM_WINAMP.winID) AS totalSongs,
Max(dbo_FORUM_WINAMP.winDate) AS lastPlayed,
Last(dbo_FORUM_WINAMP.winSong) AS lastSong
FROM dbo_FORUM_WINAMP
GROUP BY dbo_FORUM_WINAMP.winName
ORDER BY Count(dbo_FORUM_WINAMP.winID) DESC;
I'd like to figure out a way to do this with MS SQL.
|
|
Edited by - dayve on 13 March 2004 18:51:31 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 13 March 2004 : 20:17:40
|
| Ok, dayve, I see your point, regarding LAST as being TOP 1. You can't use it in this case because of the group by. You can do this with a correlated subquery, at least, but to do that I need to know what field can I use to get the last record. Is it winID? |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 13 March 2004 : 20:25:15
|
Admiting the answer to my previous question is winID, I think this can do it:
SELECT WA.winName,
Count(WA.winID) AS totalSongs,
Max(WA.winDate) AS lastPlayed,
(SELECT TOP 1 winSong FROM dboFORUM_WINAMP WL WHERE WL.winName=WA.winName ORDER BY winID DESC) As lastSong
FROM dbo_FORUM_WINAMP As WA
GROUP BY WA.winName
ORDER BY Count(WA.winID) DESC;
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 13 March 2004 : 20:39:30
|
| I forgot to reply about the MS SQL forum being the right one to post the question or not. I see it's mod related, so looks like the forum I moved it to is not the adequate one. Being mod related, I guess one of the mod forums would be the best. I always thought of the help forums, exception made to the Mod Implementation one, to be related to the base Snitz code. That's why I moved the post, no disrespect intended :). |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 13 March 2004 : 20:45:30
|
Thanks ruirib, that did it. Although it really increased the time to produce a result (to be expected). However, I want to thank you for your help.
For the record, I did not feel disrespected at all  . |
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
dayve
Forum Moderator
USA
5820 Posts |
|
| |
Topic |
|
Topic Locked
