How can i get max Point and Min Date using SQL [modified]

mangrovecm

Dear all, I have a 'Games' table in testxyz db following: create database testxyz go use testxyz go if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Games]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Games] GO CREATE TABLE [dbo].[Games] ( [UserID] [int] IDENTITY (1, 1) NOT NULL , [UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Point] [int] NULL , [Dates] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Games] ADD CONSTRAINT [DF_Users_Dates] DEFAULT (getdate()) FOR [Dates], CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [UserID] ) ON [PRIMARY] GO --Insert data: DELETE FROM [Games] GO SET IDENTITY_INSERT[Games] ON GO INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates) VALUES(1,'C.John',10,'5/19/2008 7:20:59 PM') GO INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates) VALUES(2,'C.John',12,'5/18/2008 7:10:59 PM') GO INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates) VALUES(3,'C.John',6,'5/17/2008 7:22:59 PM') GO INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates) VALUES(4,'C.John',20,'5/19/2008 7:45:59 PM') GO INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates) VALUES(5,'A.Jerry',4,'5/19/2008 7:28:59 PM') GO INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates) VALUES(6,'A.Jerry',8,'5/19/2008 7:19:59 PM') GO INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates) VALUES(7,'A.Jerry',10,'4/1/2008 7:19:59 PM') GO INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates) VALUES(8,'A.Jerry',67,'4/1/2008 7:19:59 PM') GO INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates) VALUES(9,'Nancy',50,'5/11/2008 7:19:59 PM') GO INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates) VALUES(10,'Nancy',10,'5/21/2008 7:19:59 PM') GO INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates) VALUES(11,'Nancy',78,'5/25/2008 7:19:59 PM') GO INSERT INTO [dbo].[Games](UserID,UserName,Point,Dates) VALUES(12,'A.Jerry',78,'5/26/2008 7:19:59 PM') SET IDENTITY_INSERT[Games] OFF GO Now, I want to get rows which have maximum 'Point' and minimum 'Dates'. If Point of user is the same. it gets a row with minimum 'Dates' condition. Note that result will sort by Point 'DESC'. UserName do not repeat in the result. In data above. Expected result is: UserID | UserName | Point | Dates 11 | Nancy |78 | 5/25/2008 7:19:59 PM 12 | A.Jerry |78 | 5/26/2008 7:19:59 PM 4 | C.John |20 | 5/19/2008 7:45:59 PM Please help me! Thanks a lot <

WoutL

Their are multiple solutions for that. You could try:

select g.*
from games g
inner join (
select point=max(point), date=min(dates)
from games
) d on g.point = d.point or g.dates = d.date
order by g.point desc

or:

select *
from games
where point = (select max(point) from games)
or dates = (select min(dates) from games)
order by point desc

Both give the same results.

Wout Louwers

mangrovecm

mangrovecm (-,-)am from VietNamese.

WoutL

Without the UserID it would be simple:

select UserName, points=max(point), date=min(dates)
from games
Group by UserName

But the problem is thath a singel user has more than one userid. So which userid should be returned? I think it is better to redesign the database and split the games table in a user and games table.

Wout Louwers