Given this table, named tblCUST:

txtNAME intSCORE
====== ========
CARL 70
ALEX 50
BILL 55
ALEX 55
BILL 50
ALEX 70
ALEX 18
BILL 26
CARL 27
CARL 50
ALEX 25
CARL 55
BILL 28
CARL 38

What SQL statement will display
the three (3) highest score values
for each customer name – sorted
ascending by name and then sorted
descending by score?

The result should look like this:

ALEX 70 (highest)
ALEX 55 (second highest)
ALEX 50 (third highest)

BILL 55 (highest)
BILL 50 (second highest)
BILL 28 (third highest)

CARL 70 (highest)
CARL 55 (second highest)
CARL 50 (third highest)

THANK YOU !!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I believe you'd have to create a temp table and use a VBA loop to store
the top 3 scores for each individual into the temp table & then query
the temp table.

VBA example (put in your own error handlers) (watch for line wrap):

const SQL_TEMP_TABLE = "CREATE TABLE SortedScores (txtName Text(50),
intScore Integer)"

const SQL_NAMES = "SELECT txtName FROM tblCust GROUP ON txtName"

const SQL_SCORES = "INSERT INTO SortedScores " & _
"SELECT TOP 3 txtName, intScore " & _
"FROM tblCust " & _
"WHERE txtName = "

const SQL_SCORES_TAIL = "ORDER BY intScore DESC"

dim db as dao.database
dim rs as dao.recordset
dim strSQL as string

set db = currentdb

' create temp table
db.execute SQL_TEMP_TABLE

' Get the names in tblCust
set rs = db.OpenRecordset(SQL_NAMES)

' Loop thru the names & put their 3 highest scores into the temp table:
do while not rs.EOF
strSQL = SQL_SCORES & "'" & rs!txtName & "' " & SQL_SCORES_TAIL
db.execute strSQL, dbFailOnError
rs.MoveNext
loop

' You'll have to have a prepared query that looks like this:
' SELECT * FROM SortedScores ORDER BY txtName, intScore DESC
' named "qryShowSortedScores" & then run this command
docmd.openquery "qryShowSortedScores"

=====

In T-SQL (SQL Server language) you'd have to use a cursor loop in a
stored procedure:

- -- First create the temp table
CREATE TABLE #SortedScores (
txtName VARCHAR(50),
intScore INT
)

- -- If using SQL server 7 or lower version use: SET ROWCOUNT 3

DECLARE names CURSOR FOR
SELECT txtName FROM tblCust GROUP ON txtName

DECLARE @this_name AS VARCHAR(50)

FETCH names INTO @this_name

WHILE @@FETCH_STATUS = 0
BEGIN

- -- If using SQL Server 2000 use TOP 3 in the SELECT statement

INSERT #SortedScores
SELECT TOP 3 txtName, intScore -- Assumes SQL Server 2000
FROM tblCust
WHERE txtName = @this_name
ORDER BY intScore DESC

FETCH names INTO @this_name

END

CLOSE names
DEALLOCATE names

- -- Display result from temp table
SELECT * FROM #SortedScores ORDER BY txtName, intScores DESC

- -- Get rid of temp table
DROP TABLE #SortedScores


MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBPyenKYechKqOuFEgEQKgSwCfSdpgrE9E+xvJJsjl/s42DMvwWloAoOcw
itCKh/1SxSo/vXR2T05WhOMj
=MEy+
-----END PGP SIGNATURE-----


POBOXNYC wrote:

hi!!

think it can be done using co-related sub queries and the function top

thanx and hope it helps

regards
bala

hi!!!

the exact query for the problem is

select a.txtName, a.intScore from tblCUST a
where a.intScore in(select top 3 b.intScore from tblCUST b where
b.txtname = a.txtname order by b.txtname asc, b.intscore desc)
group by a.txtname,a.intScore order by a.txtname asc, a.intScore desc

thanx. take care and hope this helps, have a great day

regards
bala

poboxnyc (AT) aol (DOT) com (POBOXNYC) wrote in message news:<b15cca5d.0307281350.49de5f3b (AT) posting (DOT) google.com>...

hi!!!!

the following query works except for the description part (like
highest etc..,)

select a.txtName, a.intScore from tblCUST a
where a.intScore in(select top 3 b.intScore from tblCUST b where
b.txtname = a.txtname order by b.txtname asc, b.intscore desc)
group by a.txtname,a.intScore order by a.txtname asc, a.intScore desc

built the query based on the table, columns given in the example

regards
bala