Another Question- - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Another Question-

Author

Topic

azmark30
Starting Member

11 Posts

Posted - 2008-03-05 : 14:35:50

I need to get a list for the last 10 recent dates for each person .
right now its giving me all the dates in a random order.. i just need the last 10 recent dates with info..

thanks

ID NAME TIMEPLAYED POINTS DATE
800 ESQUIPULA 3221 13 2007-08-20 00:00:00.000
800 ESQUIPULA 8683 82 2007-08-21 00:00:00.000
800 ESQUIPULA 1979 6 2007-08-22 00:00:00.000
812 JANN 3075 410 2006-10-27 00:00:00.000
812 JANN 26368 1001 2007-01-02 00:00:00.000
812 JANN 6858 732 2007-01-03 00:00:00.000
812 JANN 5 0 2007-01-04 00:00:00.000
812 JANN 1687 211 2007-06-15 00:00:00.000
812 JANN 11415 1325 2007-09-22 00:00:00.000
828 DENNIS 600 0 2007-12-09 00:00:00.000
828 DENNIS 3600 0 2007-12-10 00:00:00.000
828 DENNIS 6803 11 2007-12-10 00:00:00.000
828 DENNIS 4269 2 2007-12-26 00:00:00.000
828 DENNIS 7628 9 2007-12-27 00:00:00.000
828 DENNIS 3600 0 2007-12-26 00:00:00.000
828 DENNIS 9000 0 2007-12-27 00:00:00.000
828 DENNIS 4582 8 2007-12-28 00:00:00.000
828 DENNIS 3900 0 2007-12-28 00:00:00.000
828 DENNIS 2830 2 2007-12-29 00:00:00.000
828 DENNIS 4200 0 2007-12-29 00:00:00.000
828 DENNIS 1800 0 2008-01-12 00:00:00.000
838 CONCHA 2100 0 2006-12-05 00:00:00.000
838 CONCHA 227 2 2007-02-16 00:00:00.000
838 CONCHA 2178 25 2007-08-08 00:00:00.000
846 JACK 513 2 2007-03-08 00:00:00.000
846 JACK 9600 0 2007-03-08 00:00:00.000
846 JACK 7200 0 2007-03-09 00:00:00.000
846 JACK 88800 0 2007-03-10 00:00:00.000
846 JACK 962 6 2007-03-11 00:00:00.000
846 JACK 3600 0 2007-03-11 00:00:00.000
846 JACK 776 3 2007-03-12 00:00:00.000
846 JACK 6300 0 2007-03-12 00:00:00.000
867 PAT 1965 41 2006-11-02 00:00:00.000
867 PAT 3998 54 2007-05-10 00:00:00.000
923 LINDA 2215 21 2006-10-31 00:00:00.000
923 LINDA 3356 23 2007-01-05 00:00:00.000
923 LINDA 2033 28 2007-02-10 00:00:00.000
923 LINDA 2932 19 2007-02-12 00:00:00.000
923 LINDA 778 5 2007-02-20 00:00:00.000
923 LINDA 1827 8 2007-05-04 00:00:00.000
923 LINDA 2587 13 2007-07-04 00:00:00.000
923 LINDA 2089 16 2008-01-18 00:00:00.000
927 NATASHA 4759 6 2006-10-28 00:00:00.000
927 NATASHA 4940 7 2007-01-27 00:00:00.000
940 JEANETTE 6991 17 2007-03-08 00:00:00.000
940 JEANETTE 3514 6 2007-03-09 00:00:00.000
940 JEANETTE 781 1 2007-03-10 00:00:00.000
940 JEANETTE 4218 5 2007-03-11 00:00:00.000
940 JEANETTE 6030 19 2007-03-12 00:00:00.000
946 BEATRICE 3073 5 2006-10-01 00:00:00.000
946 BEATRICE 6583 12 2006-10-02 00:00:00.000
946 BEATRICE 6796 20 2007-06-21 00:00:00.000
946 BEATRICE 4103 15 2007-06-22 00:00:00.000
946 BEATRICE 5703 15 2007-06-23 00:00:00.000
948 JOYCE 1130 1 2007-03-08 00:00:00.000
948 JOYCE 2854 1 2008-03-04 00:00:00.000
952 MONICA 1102 2 2007-06-30 00:00:00.000
954 BERNICE 12040 31 2007-01-17 00:00:00.000
967 ROSE 17601 73 2007-12-06 00:00:00.000
996 KARL 4678 24 2006-11-07 00:00:00.000
1009 MARY ANNE 30154 795 2006-12-29 00:00:00.000
1009 MARY ANNE 48693 1135 2006-12-30 00:00:00.000
1009 MARY ANNE 32140 638 2006-12-31 00:00:00.000
1009 MARY ANNE 8569 219 2007-01-01 00:00:00.000
1009 MARY ANNE 73891 987 2007-05-11 00:00:00.000
1009 MARY ANNE 69110 859 2007-05-12 00:00:00.000
1009 MARY ANNE 48277 1157 2007-05-13 00:00:00.000
1009 MARY ANNE 6763 100 2007-05-14 00:00:00.000
1009 MARY ANNE 41136 1236 2007-12-30 00:00:00.000
1009 MARY ANNE 41564 935 2007-12-31 00:00:00.000
1009 MARY ANNE 17719 633 2008-01-01 00:00:00.000
1012 JEFFREY 10492 45 2006-10-26 00:00:00.000
1012 JEFFREY 50011 272 2006-10-27 00:00:00.000
1012 JEFFREY 31917 145 2006-10-28 00:00:00.000
1012 JEFFREY 824 7 2006-10-29 00:00:00.000
1012 JEFFREY 11901 64 2007-05-17 00:00:00.000
1012 JEFFREY 11408 64 2007-05-18 00:00:00.000
1012 JEFFREY 16318 63 2007-05-19 00:00:00.000
1012 JEFFREY 262 2 2007-05-20 00:00:00.000
1012 JEFFREY 5778 21 2007-06-11 00:00:00.000
1012 JEFFREY 29945 155 2007-06-12 00:00:00.000
1012 JEFFREY 29701 153 2007-06-13 00:00:00.000
1012 JEFFREY 16601 81 2007-06-14 00:00:00.000
1012 JEFFREY 27540 127 2007-10-25 00:00:00.000
1012 JEFFREY 23753 120 2007-10-26 00:00:00.000
1012 JEFFREY 11965 55 2007-10-27 00:00:00.000
1012 JEFFREY 1307 7 2007-10-28 00:00:00.000
1037 MARYJANE 17518 160 2007-03-22 00:00:00.000
1037 MARYJANE 15579 135 2007-03-23 00:00:00.000
1037 MARYJANE 25874 224 2007-05-08 00:00:00.000
1037 MARYJANE 3206 25 2007-05-09 00:00:00.000
1037 MARYJANE 20936 176 2007-10-05 00:00:00.000
1037 MARYJANE 2271 22 2007-10-06 00:00:00.000
1037 MARYJANE 23833 213 2008-02-02 00:00:00.000
1037 MARYJANE 6 0 2008-02-03 00:00:00.000
1050 WALTER 3348 8 2006-11-08 00:00:00.000
1050 WALTER 3568 12 2006-12-13 00:00:00.000
1050 WALTER 4 0 2006-12-16 00:00:00.000
1050 WALTER 2819 12 2007-11-18 00:00:00.000
1052 GEORGE 1832 1 2006-12-12 00:00:00.000
1052 GEORGE 6011 10 2007-07-24 00:00:00.000
1052 GEORGE 2077 2 2007-08-16 00:00:00.000
1052 GEORGE 2795 4 2007-12-06 00:00:00.000
1060 DON 11696 23 2006-12-10 00:00:00.000
1060 DON 500 1 2007-05-24 00:00:00.000
1060 DON 8129 15 2007-05-25 00:00:00.000
1060 DON 12487 19 2007-07-28 00:00:00.000
1060 DON 5 0 2007-07-29 00:00:00.000
1060 DON 22680 32 2007-12-31 00:00:00.000
1065 BETTY 2883 8 2008-02-18 00:00:00.000
1065 BETTY 3096 12 2008-02-19 00:00:00.000
1073 RENEE 4915 43 2006-11-12 00:00:00.000
1073 RENEE 4929 18 2007-02-05 00:00:00.000
1073 RENEE 2338 45 2007-04-22 00:00:00.000
1073 RENEE 12638 116 2007-07-27 00:00:00.000
1073 RENEE 45346 181 2007-07-28 00:00:00.000
1073 RENEE 7310 114 2007-07-29 00:00:00.000

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-03-05 : 15:30:03

[code]DECLARE @a TABLE ( i INT IDENTITY, ID INT, NAME CHAR(35), TIMEPLAYED INT, POINTS INT, DATE DATETIME )
INSERT @a ( ID, NAME, TIMEPLAYED, POINTS, DATE )
SELECT ID, NAME, TIMEPLAYED, POINTS, DATE
FROM YourTable
ORDER BY ID, DATE

SELECT a1.ID, a1.NAME, a1.TIMEPLAYED, a1.POINTS, a1.DATE
FROM @a a1
JOIN (
SELECT COUNT(*) AS rn,
a1.i
FROM @a a1
JOIN @a a2 ON a1.i >= a2.i
AND a1.id = a2.id
GROUP BY a1.i ) a2 ON a1.i = a2.i
WHERE a2.rn <= 10[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-06 : 02:04:31

[code]SELECT s.ID,
s.Name,
s.TimePlayed,
s.Points,
s.Date
FROM YourTable AS s
WHERE s.Date IN (SELECT TOP 3 x.Date FROM YourTable AS x WHERE x.Name = s.Name ORDER BY x.Date DESC)[/code]

And here is the sample data to test with[code]CREATE TABLE YourTable
(
ID INT,
Name VARCHAR(200),
TimePlayed INT,
Points INT,
Date DATETIME
)

INSERT YourTable
SELECT 800, 'ESQUIPULA', 3221, 13, '2007-08-20' UNION ALL
SELECT 800, 'ESQUIPULA', 8683, 82, '2007-08-21' UNION ALL
SELECT 800, 'ESQUIPULA', 1979, 6, '2007-08-22' UNION ALL
SELECT 812, 'JANN', 3075, 410, '2006-10-27' UNION ALL
SELECT 812, 'JANN', 26368, 1001, '2007-01-02' UNION ALL
SELECT 812, 'JANN', 6858, 732, '2007-01-03' UNION ALL
SELECT 812, 'JANN', 5, 0, '2007-01-04' UNION ALL
SELECT 812, 'JANN', 1687, 211, '2007-06-15' UNION ALL
SELECT 812, 'JANN', 11415, 1325, '2007-09-22' UNION ALL
SELECT 828, 'DENNIS', 600, 0, '2007-12-09' UNION ALL
SELECT 828, 'DENNIS', 3600, 0, '2007-12-10' UNION ALL
SELECT 828, 'DENNIS', 6803, 11, '2007-12-10' UNION ALL
SELECT 828, 'DENNIS', 4269, 2, '2007-12-26' UNION ALL
SELECT 828, 'DENNIS', 7628, 9, '2007-12-27' UNION ALL
SELECT 828, 'DENNIS', 3600, 0, '2007-12-26' UNION ALL
SELECT 828, 'DENNIS', 9000, 0, '2007-12-27' UNION ALL
SELECT 828, 'DENNIS', 4582, 8, '2007-12-28' UNION ALL
SELECT 828, 'DENNIS', 3900, 0, '2007-12-28' UNION ALL
SELECT 828, 'DENNIS', 2830, 2, '2007-12-29' UNION ALL
SELECT 828, 'DENNIS', 4200, 0, '2007-12-29' UNION ALL
SELECT 828, 'DENNIS', 1800, 0, '2008-01-12' UNION ALL
SELECT 838, 'CONCHA', 2100, 0, '2006-12-05' UNION ALL
SELECT 838, 'CONCHA', 227, 2, '2007-02-16' UNION ALL
SELECT 838, 'CONCHA', 2178, 25, '2007-08-08' UNION ALL
SELECT 846, 'JACK', 513, 2, '2007-03-08' UNION ALL
SELECT 846, 'JACK', 9600, 0, '2007-03-08' UNION ALL
SELECT 846, 'JACK', 7200, 0, '2007-03-09' UNION ALL
SELECT 846, 'JACK', 88800, 0, '2007-03-10' UNION ALL
SELECT 846, 'JACK', 962, 6, '2007-03-11' UNION ALL
SELECT 846, 'JACK', 3600, 0, '2007-03-11' UNION ALL
SELECT 846, 'JACK', 776, 3, '2007-03-12' UNION ALL
SELECT 846, 'JACK', 6300, 0, '2007-03-12' UNION ALL
SELECT 867, 'PAT', 1965, 41, '2006-11-02' UNION ALL
SELECT 867, 'PAT', 3998, 54, '2007-05-10' UNION ALL
SELECT 923, 'LINDA', 2215, 21, '2006-10-31' UNION ALL
SELECT 923, 'LINDA', 3356, 23, '2007-01-05' UNION ALL
SELECT 923, 'LINDA', 2033, 28, '2007-02-10' UNION ALL
SELECT 923, 'LINDA', 2932, 19, '2007-02-12' UNION ALL
SELECT 923, 'LINDA', 778, 5, '2007-02-20' UNION ALL
SELECT 923, 'LINDA', 1827, 8, '2007-05-04' UNION ALL
SELECT 923, 'LINDA', 2587, 13, '2007-07-04' UNION ALL
SELECT 923, 'LINDA', 2089, 16, '2008-01-18' UNION ALL
SELECT 927, 'NATASHA', 4759, 6, '2006-10-28' UNION ALL
SELECT 927, 'NATASHA', 4940, 7, '2007-01-27' UNION ALL
SELECT 940, 'JEANETTE', 6991, 17, '2007-03-08' UNION ALL
SELECT 940, 'JEANETTE', 3514, 6, '2007-03-09' UNION ALL
SELECT 940, 'JEANETTE', 781, 1, '2007-03-10' UNION ALL
SELECT 940, 'JEANETTE', 4218, 5, '2007-03-11' UNION ALL
SELECT 940, 'JEANETTE', 6030, 19, '2007-03-12' UNION ALL
SELECT 946, 'BEATRICE', 3073, 5, '2006-10-01' UNION ALL
SELECT 946, 'BEATRICE', 6583, 12, '2006-10-02' UNION ALL
SELECT 946, 'BEATRICE', 6796, 20, '2007-06-21' UNION ALL
SELECT 946, 'BEATRICE', 4103, 15, '2007-06-22' UNION ALL
SELECT 946, 'BEATRICE', 5703, 15, '2007-06-23' UNION ALL
SELECT 948, 'JOYCE', 1130, 1, '2007-03-08' UNION ALL
SELECT 948, 'JOYCE', 2854, 1, '2008-03-04' UNION ALL
SELECT 952, 'MONICA', 1102, 2, '2007-06-30' UNION ALL
SELECT 954, 'BERNICE', 12040, 31, '2007-01-17' UNION ALL
SELECT 967, 'ROSE', 17601, 73, '2007-12-06' UNION ALL
SELECT 996, 'KARL', 4678, 24, '2006-11-07' UNION ALL
SELECT 1009, 'MARY ANNE', 30154, 795, '2006-12-29' UNION ALL
SELECT 1009, 'MARY ANNE', 48693, 1135, '2006-12-30' UNION ALL
SELECT 1009, 'MARY ANNE', 32140, 638, '2006-12-31' UNION ALL
SELECT 1009, 'MARY ANNE', 8569, 219, '2007-01-01' UNION ALL
SELECT 1009, 'MARY ANNE', 73891, 987, '2007-05-11' UNION ALL
SELECT 1009, 'MARY ANNE', 69110, 859, '2007-05-12' UNION ALL
SELECT 1009, 'MARY ANNE', 48277, 1157, '2007-05-13' UNION ALL
SELECT 1009, 'MARY ANNE', 6763, 100, '2007-05-14' UNION ALL
SELECT 1009, 'MARY ANNE', 41136, 1236, '2007-12-30' UNION ALL
SELECT 1009, 'MARY ANNE', 41564, 935, '2007-12-31' UNION ALL
SELECT 1009, 'MARY ANNE', 17719, 633, '2008-01-01' UNION ALL
SELECT 1012, 'JEFFREY', 10492, 45, '2006-10-26' UNION ALL
SELECT 1012, 'JEFFREY', 50011, 272, '2006-10-27' UNION ALL
SELECT 1012, 'JEFFREY', 31917, 145, '2006-10-28' UNION ALL
SELECT 1012, 'JEFFREY', 824, 7, '2006-10-29' UNION ALL
SELECT 1012, 'JEFFREY', 11901, 64, '2007-05-17' UNION ALL
SELECT 1012, 'JEFFREY', 11408, 64, '2007-05-18' UNION ALL
SELECT 1012, 'JEFFREY', 16318, 63, '2007-05-19' UNION ALL
SELECT 1012, 'JEFFREY', 262, 2, '2007-05-20' UNION ALL
SELECT 1012, 'JEFFREY', 5778, 21, '2007-06-11' UNION ALL
SELECT 1012, 'JEFFREY', 29945, 155, '2007-06-12' UNION ALL
SELECT 1012, 'JEFFREY', 29701, 153, '2007-06-13' UNION ALL
SELECT 1012, 'JEFFREY', 16601, 81, '2007-06-14' UNION ALL
SELECT 1012, 'JEFFREY', 27540, 127, '2007-10-25' UNION ALL
SELECT 1012, 'JEFFREY', 23753, 120, '2007-10-26' UNION ALL
SELECT 1012, 'JEFFREY', 11965, 55, '2007-10-27' UNION ALL
SELECT 1012, 'JEFFREY', 1307, 7, '2007-10-28' UNION ALL
SELECT 1037, 'MARYJANE', 17518, 160, '2007-03-22' UNION ALL
SELECT 1037, 'MARYJANE', 15579, 135, '2007-03-23' UNION ALL
SELECT 1037, 'MARYJANE', 25874, 224, '2007-05-08' UNION ALL
SELECT 1037, 'MARYJANE', 3206, 25, '2007-05-09' UNION ALL
SELECT 1037, 'MARYJANE', 20936, 176, '2007-10-05' UNION ALL
SELECT 1037, 'MARYJANE', 2271, 22, '2007-10-06' UNION ALL
SELECT 1037, 'MARYJANE', 23833, 213, '2008-02-02' UNION ALL
SELECT 1037, 'MARYJANE', 6, 0, '2008-02-03' UNION ALL
SELECT 1050, 'WALTER', 3348, 8, '2006-11-08' UNION ALL
SELECT 1050, 'WALTER', 3568, 12, '2006-12-13' UNION ALL
SELECT 1050, 'WALTER', 4, 0, '2006-12-16' UNION ALL
SELECT 1050, 'WALTER', 2819, 12, '2007-11-18' UNION ALL
SELECT 1052, 'GEORGE', 1832, 1, '2006-12-12' UNION ALL
SELECT 1052, 'GEORGE', 6011, 10, '2007-07-24' UNION ALL
SELECT 1052, 'GEORGE', 2077, 2, '2007-08-16' UNION ALL
SELECT 1052, 'GEORGE', 2795, 4, '2007-12-06' UNION ALL
SELECT 1060, 'DON', 11696, 23, '2006-12-10' UNION ALL
SELECT 1060, 'DON', 500, 1, '2007-05-24' UNION ALL
SELECT 1060, 'DON', 8129, 15, '2007-05-25' UNION ALL
SELECT 1060, 'DON', 12487, 19, '2007-07-28' UNION ALL
SELECT 1060, 'DON', 5, 0, '2007-07-29' UNION ALL
SELECT 1060, 'DON', 22680, 32, '2007-12-31' UNION ALL
SELECT 1065, 'BETTY', 2883, 8, '2008-02-18' UNION ALL
SELECT 1065, 'BETTY', 3096, 12, '2008-02-19' UNION ALL
SELECT 1073, 'RENEE', 4915, 43, '2006-11-12' UNION ALL
SELECT 1073, 'RENEE', 4929, 18, '2007-02-05' UNION ALL
SELECT 1073, 'RENEE', 2338, 45, '2007-04-22' UNION ALL
SELECT 1073, 'RENEE', 12638, 116, '2007-07-27' UNION ALL
SELECT 1073, 'RENEE', 45346, 181, '2007-07-28' UNION ALL
SELECT 1073, 'RENEE', 7310, 114, '2007-07-29'[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources