Simple query help - 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

General SQL Server Forums

New to SQL Server Programming

Simple query help

Author

Topic

MALON
Starting Member

5 Posts

Posted - 2008-09-15 : 19:58:22

So I am making a racing game, and the scores will be kept in a database. My goal is to make a scoreboard. So far, my test table is set up like this:

tablename = kz

i, name, fintime, mapname, steamid, cps, gcs, wpn

1, MALON, 30, bkz_goldbhop, STEAM:123, 0, 0, 0
2, Veritas, 8, bkz_goldbhop, STEAM:456, 0, 0, 0
3, MALON, 51, bkz_goldbhop, STEAM:123, 3, 2, 1
4, Veritas, 38, bkz_goldbhop, STEAM:456, 2, 3, 1

For this instance, the query should return:

name, fintime, cps, gcs, wpn
Veritas, 8, 0, 0, 0
MALON, 51, 3, 0, 0

Basically, it needs to return the fastest finish times, with no repeating names, ordered by finish time, limited at 15. The unique identifier is the mapname.

The closest I can get is:
SELECT name, min(fintime), cps, gcs, wpn FROM kz WHERE mapname = "bkz_goldbhop" GROUP BY name ORDER BY fintime LIMIT 15

But that returns:

name, fintime, cps, gcs, wpn
Veritas, 8, 2, 3, 1
MALON, 30, 3, 2, 1

Which is close, but the cps, gcs, and wpn are not in the corresponding row of fintime. I'm sure I either have to use subselects or joins, but I'm failing at figuring out this out. I've been trying for 8 hours now, reading on how to do it, and it's eluding me.

Thanks anyone!

--MALON

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-15 : 22:20:49

this is a Microsoft SQL Server forum. Please post your MySQL question over at forums.mysql.com or dbforums.com

KH
[spoiler]Time is always against us[/spoiler]

james_wells
Yak Posting Veteran

55 Posts

Posted - 2008-09-16 : 15:06:39

This code will work on your data however what do you want to do if two identical minimum times exist for the same kw.name.

This code will report all records for each kw.name that match the lowest time for the kw.name.

use databasename
go
select O.KW_NAME ,
O.KW_NAME,
O.KW_FINTIME,
O.KW_MAPNAME,
O.KW_STEAMID,
O.KW_CPS,
O.KW_GCS,
O.WPN_WPN
from KW AS O
where O.KW_FINTIME = (select min(I.KW_FINTIME)
from KW I
where I.KW_NAME = O.KW_NAME)

james_wells
Yak Posting Veteran

55 Posts

Posted - 2008-09-17 : 15:58:52

Never noticed the "LIMIT 15" (grey momement)
and now understand the mysql comment above
however it does not hurt to give the SQL server
eqivelant as well

-------------
revised code
-------------

use databasename

select TOP (15)
O.KW_NAME,
O.KW_FINTIME,
O.KW_MAPNAME,
O.KW_STEAMID,
O.KW_CPS,
O.KW_GCS,
O.WPN_WPN
from KW O
where O.KW_FINTIME = (select min(I.KW_FINTIME)
from KW I
where I.KW_NAME = O.KW_NAME)
ORDER BY O.KW_FINTIME

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources