Generally, to sort data you need to ues
ORDER BY[
^] clause.
Have a look at example:
DECLARE @apps TABLE(AppNo INT, AppName VARCHAR(30), ModDate DATETIME)
INSERT INTO @apps (AppNo, AppName, ModDate)
SELECT 1 AS AppNo, 'ApplicationNo1' AS ApName, '2010-05-11' AS ModDate
UNION ALL SELECT 2, 'ApplicationNo2', '2010-06-11'
UNION ALL SELECT 1, 'ApplicationNo1', '2010-09-01'
UNION ALL SELECT 3, 'ApplicationNo3', '2010-09-21'
UNION ALL SELECT 1, 'ApplicationNo1', '2011-03-15'
UNION ALL SELECT 2, 'ApplicationNo2', '2011-04-23'
UNION ALL SELECT 3, 'ApplicationNo3', '2012-06-13'
UNION ALL SELECT 2, 'ApplicationNo2', '2012-07-01'
UNION ALL SELECT 1, 'ApplicationNo1', '2012-10-18'
UNION ALL SELECT 1, 'ApplicationNo1', '2013-01-01'
UNION ALL SELECT 2, 'ApplicationNo2', '2013-03-01'
SELECT *
FROM @apps
ORDER BY AppNo, ModDate DESC
SELECT *, ROW_NUMBER() OVER(PARTITION BY AppNo ORDER BY ModDate DESC) AS ModNo
FROM @apps
Results:
1. query:
AppNo AppName ModDate ModNo
1 ApplicationNo1 2013-01-01 00:00:00.000
1 ApplicationNo1 2012-10-18 00:00:00.000
1 ApplicationNo1 2011-03-15 00:00:00.000
1 ApplicationNo1 2010-09-01 00:00:00.000
1 ApplicationNo1 2010-05-11 00:00:00.000
2 ApplicationNo2 2013-03-01 00:00:00.000
2 ApplicationNo2 2012-07-01 00:00:00.000
2 ApplicationNo2 2011-04-23 00:00:00.000
2 ApplicationNo2 2010-06-11 00:00:00.000
3 ApplicationNo3 2012-06-13 00:00:00.000
3 ApplicationNo3 2010-09-21 00:00:00.000
2. query:
AppNo AppName ModDate ModNo
1 ApplicationNo1 2013-01-01 00:00:00.000 1
1 ApplicationNo1 2012-10-18 00:00:00.000 2
1 ApplicationNo1 2011-03-15 00:00:00.000 3
1 ApplicationNo1 2010-09-01 00:00:00.000 4
1 ApplicationNo1 2010-05-11 00:00:00.000 5
2 ApplicationNo2 2013-03-01 00:00:00.000 1
2 ApplicationNo2 2012-07-01 00:00:00.000 2
2 ApplicationNo2 2011-04-23 00:00:00.000 3
2 ApplicationNo2 2010-06-11 00:00:00.000 4
3 ApplicationNo3 2012-06-13 00:00:00.000 1
3 ApplicationNo3 2010-09-21 00:00:00.000 2