select group/ max items  
Author Message
Staffing





PostPosted: Mon Apr 17 15:18:26 CDT 2006 Top

SQL Server Developer >> select group/ max items

Hi,

Imagine I have a table with the following rows:

Type Number Date
A 1 2006-01-01
A 1 2006-01-31
B 1 2006-01-15
C 2 2006-01-02
A 2 2006-01-01

What I would like is to get a resultset of one row per Number, whereby the
most recent date is used of each number in the above table, such that the
looks like this:

Type Number Date
C 2 2006-01-02
A 1 2006-01-31

How do I do this?

Lisa

SQL Server143  
 
 
SQL





PostPosted: Mon Apr 17 15:18:26 CDT 2006 Top

SQL Server Developer >> select group/ max items
Like this

create table table1 (Type char(1) , Number int, Date datetime)
insert into table1 values ('A',1,'2006-01-01')
insert into table1 values ('A',1,'2006-01-31')
insert into table1 values ('B',1,'2006-01-15')
insert into table1 values ('C',2,'2006-01-02')
insert into table1 values ('A',2,'2006-01-01')



select a.* from table1 a join
(select number,max(date) as MaxDate
from Table1
group by number ) z on a.date = z.maxdate
and a.number =z.number


Denis the SQL Menace
http://sqlservercode.blogspot.com/

 
 
David





PostPosted: Mon Apr 17 15:21:32 CDT 2006 Top

SQL Server Developer >> select group/ max items
> Hi,
>
> Imagine I have a table with the following rows:
>
> Type Number Date
> A 1 2006-01-01
> A 1 2006-01-31
> B 1 2006-01-15
> C 2 2006-01-02
> A 2 2006-01-01
>
> What I would like is to get a resultset of one row per Number, whereby the
> most recent date is used of each number in the above table, such that the
> looks like this:
>
> Type Number Date
> C 2 2006-01-02
> A 1 2006-01-31
>
> How do I do this?
>
> Lisa

I'll assume that (number, date) is unique and that "most recent" means
the maximum date (i.e. there are no future dates to consider.

SELECT type, number, date
FROM your_table AS T
WHERE date =
(SELECT MAX(date)
FROM your_table
WHERE number = T.number) ;

If (number, date) isn't the key then you'll have to explain what
criteria should be used to select a single row if there is more than
one most recent row.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 
 
Omnibuzz





PostPosted: Tue Apr 18 02:58:53 CDT 2006 Top

SQL Server Developer >> select group/ max items An alternate solution... Just for fun ;)

select number, right(max(cast(date as varchar) + type),1) as type,
max(date) as date
from table1 group by number

P.S: Do not use this solution