SELECT table1.id AS t1ID, table2.name AS t2Name, table3.name AS t3Name
INTO #Temp
FROM table1
LEFT OUTER JOIN table2 ON table2.id = table1.table2id
LEFT OUTER JOIN table3 ON table3.id = table1.table3id
WHERE table1.userid = @userid
ORDER BY table1.datestamp
SELECT DISTINCT TOP 5 t1ID, t2Name, t3Name
FROM #Temp
DROP TABLE #Temp
SELECT TOP 5
table1.id, table2.name, table3.name
FROM table1
LEFT OUTER JOIN table2 ON table2.id = table1.table2id
LEFT OUTER JOIN table3 ON table3.id = table1.table3id
WHERE table1.userid = @userid
group by table1.id, table2.name, table3.name
ORDER BY table1.id, table2.name, table3.name
SELECT TOP 5 table1.id, table2.name, table3.name
FROM table1
LEFT OUTER JOIN table2 ON table2.id = table1.table2id
LEFT OUTER JOIN table3 ON table3.id = table1.table3id
WHERE table1.userid = @userid
GROUP BY table1.id, table2.name, table3.name
ORDER BY min(table1.datestamp)
SELECT id, t2name, t3name
FROM (
SELECT table1.id, table2.name t2name, table3.name t3name, ROW_NUMBER() over ( PARTITION by table1.id, table2.name, table3.name ORDER BY table1.datestamp) rnum
FROM table1
LEFT OUTER JOIN table2 ON table2.id = table1.table2id
LEFT OUTER JOIN table3 ON table3.id = table1.table3id
WHERE table1.userid = @userid) temp
WHERE rnum <=5
SELECT AgencyID, Rank() over ( PARTITION by AdminUserID ORDER BY DateTimeStamp DESC) rank
INTO #Temp
FROM @t
WHERE AdminUserID = @AdminUserID
ORDER BY DateTimeStamp DESC
SELECT * FROM #Temp
SELECT DISTINCT TOP 5 AgencyID, Rank() over ( ORDER BY rank DESC) num
FROM #Temp
Order by num DESC
DROP TABLE #Temp
SELECT AgencyID, Rank() over ( PARTITION by AdminUserID ORDER BY DateTimeStamp DESC) rank
INTO #Temp
FROM tblRecent
WHERE AdminUserID = @AdminUserID
ORDER BY DateTimeStamp DESC
SELECT DISTINCT TOP 5 AgencyID, sortOrder
FROM (
SELECT AgencyID, Rank() over ( ORDER BY rank DESC) sortOrder, Rank() over (PARTITION by AgencyID ORDER BY rank DESC) num
FROM #Temp
) t
WHERE num=1
Order by sortOrder DESC
DROP TABLE #Temp
create table table1
(
id int,
table2id int,
table3id int,
userid int,
datestamp datetime
)
create table table2
(
id int,
name varchar(20),
)
create table table3
(
id int,
name varchar(20),
)
-- SAMPLE DATA
insert into table2 values(1, 'autoriksha')
insert into table2 values(2, 'boat')
insert into table2 values(3, 'plane')
insert into table2 values(4, 'cycle')
insert into table2 values(5, 'bus')
insert into table2 values(6, 'car')
insert into table3 values(1, 'acdsee')
insert into table3 values(2, 'avant')
insert into table3 values(3, 'bitdefender')
insert into table3 values(4, 'norton')
insert into table3 values(5, 'windows xp')
insert into table3 values(6, 'vista')
insert into table1 values(1, 1, 1, 1, '1/3/2010 6:44 AM')
insert into table1 values(1, 1, 1, 1, '1/3/2010 7:00 AM')
insert into table1 values(1, 2, 3, 1, '1/3/2010 7:46 AM')
insert into table1 values(1, 3, 2, 1, '1/3/2010 8:14 AM')
insert into table1 values(1, 1, 1, 1, '1/3/2010 9:33 AM')
insert into table1 values(1, 3, 2, 1, '1/4/2010 9:01 AM')
insert into table1 values(1, 2, 3, 1, '1/4/2010 10:01 AM')
insert into table1 values(1, 1, 2, 1, '1/4/2010 10:01 AM')
insert into table1 values(1, 1, 2, 1, '1/4/2010 10:41 AM')
insert into table1 values(1, 2, 2, 1, '1/4/2010 10:01 AM')
insert into table1 values(1, 2, 2, 1, '1/5/2010 9:13 AM')
insert into table1 values(1, 3, 3, 1, '1/6/2010 11:11 AM')
insert into table1 values(1, 3, 3, 1, '1/7/2010 12:41 AM')
-- COMPLETE DATA
SELECT DISTINCT
table1.datestamp, table1.id, table2.name, table3.name
FROM table1
LEFT OUTER JOIN table2 ON table2.id = table1.table2id
LEFT OUTER JOIN table3 ON table3.id = table1.table3id
WHERE table1.userid = 1
ORDER BY table1.id, table2.name, table3.name
-- FILTERED VIEW - TOP 5 ONLY
SELECT
table1.id, table2.name, table3.name, MAX(table1.datestamp)
FROM table1
LEFT OUTER JOIN table2 ON table2.id = table1.table2id
LEFT OUTER JOIN table3 ON table3.id = table1.table3id
WHERE table1.userid = 1
GROUP BY table1.id, table2.name, table3.name
ORDER BY MAX(table1.datestamp)
SELECT TOP 5
table1.id, table2.name, table3.name, MAX(table1.datestamp)
FROM table1
LEFT OUTER JOIN table2 ON table2.id = table1.table2id
LEFT OUTER JOIN table3 ON table3.id = table1.table3id
WHERE table1.userid = 1
GROUP BY table1.id, table2.name, table3.name
ORDER BY MAX(table1.datestamp)
SELECT AgencyID
FROM (
SELECT AgencyID, ROW_NUMBER() over ( PARTITION by AgencyID ORDER BY DateTimeStamp DESC) rnum
FROM tblRecent
WHERE AdminUserID = @AdminUserID) temp
WHERE rnum <=5
ORDER BY AgencyID
SELECT AgencyID
FROM (
SELECT AgencyID, DateTimeStamp, ROW_NUMBER() over ( PARTITION by AgencyID ORDER BY DateTimeStamp DESC) rnum
FROM tblRecent
WHERE AdminUserID = @AdminUserID) temp
WHERE rnum <=5
ORDER BY DateTimeStamp desc
-- For your modified table run the one below
SELECT ID
FROM (
SELECT ID, datestamp, ROW_NUMBER() over ( PARTITION by ID ORDER BY datestamp DESC) rnum
FROM testtable ) temp
WHERE rnum <=5
ORDER BY datestamp desc
SELECT ID, DATESTAMP FROM
(SELECT ID, MAX(datestamp) AS DATESTAMP
FROM testtable
GROUP BY ID) AS A
ORDER BY DATESTAMP DESC
Remove the Distinct Use Group by and Try ...