Getting Top in historic data - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Getting Top in historic data

Author

Topic

TrondBjerkan
Starting Member

6 Posts

Posted - 2002-07-16 : 10:28:07

Hi,

Env: SQLserver 2000,

Problem: I want the last 3 unique points for a subscriber

Data:
The following sql should produce a temp table with data to illustrate my problem

/*sql start*/

CREATE TABLE #TMLocations (
TMLocation_ID int IDENTITY (1, 1) NOT NULL ,
XMSubscriber int NOT NULL ,
Time datetime NOT NULL ,
MPoint int NULL
)

INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-15 15:56:21.460', 998)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-11 14:27:45.287', 982)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-15 15:01:36.817', 996)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-15 14:24:03.600', 996)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-15 14:21:28.647', 996)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-15 14:13:12.240', 996)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-11 16:26:07.427', 988)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-11 14:26:02.367', 960)

DROP TABLE #TMlocations

/*sql end*/

The tmlocation table stores time (time) and location (mpoint) for users (xmsubscriber).
The fields xmsubscriber and mpoint are foreign keys.

Browsing the data, I can see that the last three unique mpoints for subscriber 454 are 988, 996 and 988
(that is the first row, third row and the sixth row). But the sql is a bit more tricky to me.

I have tried the following sql but no ...
select distinct top 3 mpoint from #tmLocations order by time desc

Looking forward for your input

Regards, Trond Bjerkan

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-16 : 10:40:44

Instead of ordering by time, don't you just want to order by mpoint?

select distinct top 3 mpoint from #tmLocations order by mpoint desc

this will give you the results of
998
996
988

If you remove the word distinct you'll get
998
998
996

-----------------------
Take my advice, I dare ya

Edited by - M.E. on 07/16/2002 10:55:30

TrondBjerkan
Starting Member

6 Posts

Posted - 2002-07-16 : 10:48:19

M.E.,

Ordering by mpoint would not give me the last 3 mpoints that is ordering by time is needed

Furthermore I need to exclude duplicate points, that is the 3 mpoints can not be the same

Thanks anyway...

- Trond

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-16 : 10:57:36

I editted my previous post...

That seems to give the desired results

-----------------------
Take my advice, I dare ya

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-16 : 11:03:24

Very interesting query!!! bravo...

Give this a try, I think it will do it for you...


select distinct
    xmsubscriber,
    mpoint
from
   #tmlocations t
where
   exists (
        select 1
        from
            #tmlocations
        where
            xmsubscriber = t.xmsubscriber
        group by
            xmsubscriber
        having (
            select count(distinct mpoint)
            from
                #tmlocations
            where
                xmsubscriber = t.xmsubscriber and
                time < > t.time ) < 3 )
go

EDIT: greater than / less than error in having clause sorry...

<O>

Edited by - Page47 on 07/16/2002 11:09:09

TrondBjerkan
Starting Member

6 Posts

Posted - 2002-07-16 : 11:09:10

M.E.,

I have change the sample data to the following:

CREATE TABLE #TMLocations (
TMLocation_ID int IDENTITY (1, 1) NOT NULL ,
XMSubscriber int NOT NULL ,
Time datetime NOT NULL ,
MPoint int NULL
)

INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-15 15:56:21.460', 98)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-11 14:27:45.287', 982)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-15 15:01:36.817', 96)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-15 14:24:03.600', 996)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-15 14:21:28.647', 996)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-15 14:13:12.240', 996)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-11 16:26:07.427', 88)
INSERT INTO #TMlocations (xmsubscriber, time, mpoint) values (454, '2002-07-11 14:26:02.367', 960)

select distinct top 3 mpoint from #tmLocations order by mpoint desc

DROP TABLE #TMlocations

I have changed three mpoint (removed the first digit), and looking at the time column the last tree mpoint must be 98, 96 and 88, but your solution do not find these three. What to do then ?

- Trond

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-16 : 11:12:09

quote:

I have changed three mpoint (removed the first digit), and looking at the time column the last tree mpoint must be 98, 96 and 88, but your solution do not find these three.

wait, wait, wait . . . shouldn't it be 98, 96 and 996, from the '14:24:03.600' record?

quote:
What to do then ?

I'd suggest using my query

<O>

Edited by - Page47 on 07/16/2002 11:13:42

TrondBjerkan
Starting Member

6 Posts

Posted - 2002-07-16 : 11:26:48

Page47,

You are quite right. I was too fast in changing the data. I have tried your solution and it seems to work. Super!!

Thanks, Trond

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources