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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select with join

Author  Topic 

alex weber
Starting Member

4 Posts

Posted - 2010-06-21 : 05:53:24
hi all,
http://www.baiaopharma.com/
i have a doubt regarding a select operation performed on two tables with join statement.
www.zimmerfinder.co.il/t/186-/
The table structure is as follows

www.zimmerfinder.co.il/
table name- application
---------------------- ------------------------------------------
appid appname version
------------------------------------------------------------------
1 Test 10
2 Test 11
3 Sample 5 www.forexsa.org/

table name- app_users
-----------------------

app_users_id user_id appid version date_downloaded





Now i want a grid which should show data like this

AppName LastDownloadedVersion Date_Downloaded
----------------------------------------------------------
http://preisvergleich-kredite.de/


Here the value for appname should be created by appname+'V'+latest version of that appname(eg. Application name- Test Ltest version- 11 thus appname becomes 'TestV11')

Could anybody help me to solve this problem..
Thanx in advance..

Devart
Posting Yak Master

102 Posts

Posted - 2010-06-21 : 06:42:27
Hi,for MS SQL Server 2005 or higher try it:

select appname,version,date_download
from
(select
row_number() over(partition by au.appid order by au.date_download desc) as last_id,
a.appname+'V'+au.version as appname,
au.version,
au.date_download
from app_users au
inner join [application] a
on au.appid=a.appid
) s
where
last_id=1

Devart,
Tools for SQL Server
http://www.devart.com/dbforge/sql
Go to Top of Page
   

- Advertisement -