problem with select query - 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 2005 Forums

Transact-SQL (2005)

problem with select query

Author

Topic

steffen
Starting Member

3 Posts

Posted - 2008-02-01 : 07:41:31

hello.

i'm new to queries exceeding simple joined selects. but the time has come to get deeper into this. :)

i got a table here which looks like this.

market_id visit_date
1 2007/05/30
1 2007/03/25
1 2007/12/24
1 2007/11/20
3 2007/04/20
3 2007/02/21
3 2007/06/18
3 2007/11/05
3 2007/08/03

i would need the following result:

market_id first_last_visit second_last_visit third_last_visit
1 2007/12/24 2007/11/20 2007/05/30
3 2007/11/05 2007/08/03 2007/06/18

it would also be enough if i could get market_id and visit_date, order the visit_date descending but only get 3 rows per market_id, not all available.

can anyone help me with this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-01 : 07:47:57

[code]DECLARE @sample TABLE
(
market_id int,
visit_date datetime
)
INSERT INTO @sample
SELECT 1, '2007/05/30' UNION ALL
SELECT 1, '2007/03/25' UNION ALL
SELECT 1, '2007/12/24' UNION ALL
SELECT 1, '2007/11/20' UNION ALL
SELECT 3, '2007/04/20' UNION ALL
SELECT 3, '2007/02/21' UNION ALL
SELECT 3, '2007/06/18' UNION ALL
SELECT 3, '2007/11/05' UNION ALL
SELECT 3, '2007/08/03'

SELECT market_id,
first_last_visit = [1],
second_last_visit = [2],
third_last_visit = [3]
FROM
(
SELECT market_id, visit_date, row_no = row_number() OVER (PARTITION BY market_id ORDER BY visit_date DESC)
FROM @sample
) d
pivot
(
MAX(visit_date)
FOR row_no IN ([1], [2], [3])
)p

/*
market_id first_last_visit second_last_visit third_last_visit
----------- ----------------- ----------------- ----------------
1 2007-12-24 2007-11-20 2007-05-30
3 2007-11-05 2007-08-03 2007-06-18

(2 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]