Rank em SQL

[BlueDragon]

Boas a todos,

Gostaria de saber se há por aqui alguem com skills em sql.

Preciso criar uma consulta (unicamente sql) para numa tabela com ID, Local e score, me mostrar os 3 melhores scores de cada Local, agrupados por local e ordenados por score dentro desse agrupamento.

Passei algumas horas em tentativas e pesquisa mas n cheguei a nenhum resultado.

Por isso se houver alguem com uma ideia, agradecia.

Cumprimentos

blue

[bruno1234]

Se puseres o script para criar as tabelas e inserir dados de teste em sql server posso ajudar-te com a query.

[BlueDragon]

CREATE TABLE [dbo].[aspnet_Games](

[GameId] [int] NOT NULL,

[userId] [uniqueidentifier] NOT NULL,

[MapId] [int] NOT NULL,

[score] [int] NOT NULL,

[Date] [date] NOT NULL,

END

GO

INSERT [dbo].[aspnet_Games] ([GameId], [userId], [MapId], [score], [Date]) VALUES (1, N'7e312ec1-3089-432f-808e-ffbabfcdbcdc', 3, 3000, CAST(0x2C320B00 AS Date))

INSERT [dbo].[aspnet_Games] ([GameId], [userId], [MapId], [score], [Date]) VALUES (2, N'7e312ec1-3089-432f-808e-ffbabfcdbcdc', 2, 1000, CAST(0xFB2F0B00 AS Date))

INSERT [dbo].[aspnet_Games] ([GameId], [userId], [MapId], [score], [Date]) VALUES (3, N'7e312ec1-3089-432f-808e-ffbabfcdbcdc', 1, 9999, CAST(0x72130B00 AS Date))

INSERT [dbo].[aspnet_Games] ([GameId], [userId], [MapId], [score], [Date]) VALUES (4, N'7e312ec1-3089-432f-808e-ffbabfcdbcdc', 9, 777, CAST(0x07130B00 AS Date))

INSERT [dbo].[aspnet_Games] ([GameId], [userId], [MapId], [score], [Date]) VALUES (5, N'0dc8af34-5d11-4dc2-9932-06c28abf4aef', 1, 1111, CAST(0x55180B00 AS Date))

INSERT [dbo].[aspnet_Games] ([GameId], [userId], [MapId], [score], [Date]) VALUES (6, N'0dc8af34-5d11-4dc2-9932-06c28abf4aef', 2, 1874, CAST(0x56180B00 AS Date))

INSERT [dbo].[aspnet_Games] ([GameId], [userId], [MapId], [score], [Date]) VALUES (7, N'f7a3c51e-32b2-45e3-8623-29ef2ecd0fe9', 3, 99999, CAST(0x55180B00 AS Date))

INSERT [dbo].[aspnet_Games] ([GameId], [userId], [MapId], [score], [Date]) VALUES (8, N'f7a3c51e-32b2-45e3-8623-29ef2ecd0fe9', 3, 1, CAST(0x55180B00 AS Date))

INSERT [dbo].[aspnet_Games] ([GameId], [userId], [MapId], [score], [Date]) VALUES (9, N'f7a3c51e-32b2-45e3-8623-29ef2ecd0fe9', 2, 2, CAST(0xD6CD0600 AS Date))

INSERT [dbo].[aspnet_Games] ([GameId], [userId], [MapId], [score], [Date]) VALUES (10, N'f7a3c51e-32b2-45e3-8623-29ef2ecd0fe9', 3, 3, CAST(0xAAB40A00 AS Date))

INSERT [dbo].[aspnet_Games] ([GameId], [userId], [MapId], [score], [Date]) VALUES (11, N'f7a3c51e-32b2-45e3-8623-29ef2ecd0fe9', 1, 4, CAST(0xA9B40A00 AS Date))

/****** Object:  StoredProcedure [dbo].[aspnet_Roles_CreateRole]    Script Date: 12/12/2009 16:42:52 ******/

aqui fica

[bruno1234]

Experimenta assim:

SELECT t.MapId, t.Score
FROM aspnet_Games AS t
WHERE (Select count(*) from aspnet_Games
	WHERE t.MapId = MapId and t.Score < Score) < 3
ORDER BY t.MapId, t.Score DESC

[BlueDragon]

perfeito 😉

obrigado

ps.: só para terminar, o userid dessa tabela, se eu quiser ir buscar o username correspondente, à tabela aspnet_Users, onde tb tem userid, como seria?

Tentei com o join, mas deixa de mostrar apenas 3 de cada Map

[BlueDragon]

Resolvido:

SELECT        t.MapId, t.Score, t.UserId, U.UserName

FROM            aspnet_Games AS t INNER JOIN

                        aspnet_Users AS U ON t.UserId = U.UserId

WHERE        ((SELECT        COUNT(*) AS Expr1

                            FROM            aspnet_Games

                            WHERE        (t.MapId = MapId) AND (t.Score < Score)) < 3)

ORDER BY t.MapId, t.Score DESC