Link to home
Start Free TrialLog in
Avatar of markej
markejFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Grouping SQL Data

I have a problem I can't get my head round I have a datatable containing the following fields Region, id, loaddescription , dateadded and I need to create a results set that gives me the latest added item for each region, the region order is not important but if it can be added great..

Typical data
id Region  dateadded            Description
1    2           02/02/11 12:00      xyz
2    1           02/02/11 11:00      abc
3    3           10/02/11 10:00      xyz2
4    3           10/02/11 11:00      grt
5    4           11/02/11  05:00      xyz1
6    2           11/02/11  06:00      xyzabc
7    1           11/02/11  09:00      qwerty
8    1           11/02/11 11:00


So what I'm after is a resultsset that based on the above data looks like this (I've ommitted the description  column for clarity.)

Region   id  dateadded description
1            8   11/02/11 11:00
2            6   11/02/11 06:00
3            4   10/02/11 11:00
4            5   11/02/11 05:00

There are in total 17 regions and potentiallly thousands of records

HELP!

Mark
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

select *
from (
select region, id, dateadded ,
row_number() over ( partition by region order by dateadded desc ) rr
from your_table )
where rr = 1;
SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of markej

ASKER

I tried  using the following
select *
from (
select region, id, dateadded , description,
row_number() over ( partition by region order by dateadded desc ) rr
from your_table )
where rr = 1;

BUT I get an error SQL Server doesn't recognise the "Over" command.

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of markej

ASKER

I thought I had deselected Oracle, missclick rather than intentional., as for OVER I thought my ISP's SQL Server was running 2005, how do I check?

That works as well thanks.

Avatar of markej

ASKER

Partial hicup over the OVER cmd and Selecting ORACLE but apart from that 3 answers that were good and presented quickly
select @@version

even if they are running sql server 2005 , your database may be ruynning at at lower compatability level

You can check the compability of a database simply by running the below sql command.

sp_dbcmptlevel 'DatabaseName'

60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
100= sql server 2008