related value to min in group-by

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

General SQL Server Forums

New to SQL Server Programming

related value to min in group-by

Author

Topic

loxt
Starting Member

4 Posts

Posted - 2012-07-24 : 01:44:10

Hi,

I have a problem relating to a similiar table like this:

table_locations
userID timestamp locationID
A 1 c
A 3 a
A 2 d
A 4 b
B 3 a
B 2 c
B 1 b
C 4 b
C 1 a
C 3 d
C 2 c

I need a list with the earliest locationIDs for every user.
I need:
userID locationID
A c
B b
C a
So I thought about:

select locationID
from table_locations
where userID and timestamp IN
(select userID, min(timestamp)
from table_locations
group by userID)

But the IN-operator can just handle a list of the same values...

Thanks a lot.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-24 : 02:14:48

CREATE TABLE #table_locations (userID CHAR(5), timestampp INT, locationID CHAR(5))

INSERT INTO #table_locations
SELECT 'A', 1, 'c' UNION ALL
SELECT 'A', 3, 'a' UNION ALL
SELECT 'A', 2, 'd' UNION ALL
SELECT 'A', 4,' b' UNION ALL
SELECT 'B', 3 ,'a' UNION ALL
SELECT 'B', 2, 'c' UNION ALL
SELECT 'B', 1, 'b' UNION ALL
SELECT 'C', 4, 'b' UNION ALL
SELECT 'C', 1, 'a' UNION ALL
SELECT 'C', 3, 'd' UNION ALL
SELECT 'C' ,2, 'c'

;WITH CTE
AS
(SELECT userID,locationID, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY timestampp) RowNum
FROM #table_locations)

SELECT userID,locationID FROM CTE
WHERE RowNum = 1

DROP TABLE #table_locations

--------------------------
http://connectsql.blogspot.com/

loxt
Starting Member

4 Posts

Posted - 2012-07-24 : 02:45:50

Thank you, that was exactly what I was looking for!

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-24 : 02:52:45

My pleasure

--------------------------
http://connectsql.blogspot.com/

loxt
Starting Member

4 Posts

Posted - 2012-07-24 : 03:23:31

Well, how could I get the locationID for every userID at the highest timestamp?

So I need:
userID locationID
A b
B a
C b

unfortunatelly for every userID there are different numbers of entries...

Thanks!

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-24 : 03:26:20

;WITH CTE
AS
(SELECT userID,locationID, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY timestampp DESC ) RowNum
FROM #table_locations)

SELECT userID,locationID FROM CTE
WHERE RowNum = 1

--------------------------
http://connectsql.blogspot.com/

loxt
Starting Member

4 Posts

Posted - 2012-07-24 : 03:33:37

Right, great!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources