Most occuring/2nd most occuring

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

Most occuring/2nd most occuring

Author

Topic

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-15 : 23:51:42

I am trying to retrieve the most popular STATION used. If there is a tie, choose the most recent DESCRIPTION (Table 2) and use the other for second most popular.
The tables are joined by TUB

Most popular as in most occuring most rows.
So ID 10001 has a Station 4 once and Station 3 once.
So draw...pick the most Recent TUB is LP12 (by date)

TABLE 1

ID	TUB	STATION
100000	1002	3
100000	1002	3
100000	1002	3
100001	1201	4
100001	1202	3
100039	1003	1
100039	1003	5
100039	1003	2
100039	1002	2
100039	1002	1

TABLE 2

YEAR	TUB	DESCRIPTION	DATE
2012	1201	LP1 12	    18-Jan-12
2012	1202	LP2 12	    18-Mar-12
2012	1203	LP3 12	    18-Jun-12
2012	1204	LP4 12	    18-Sep-12
2010	1003	LP3 10	    18-Jun-10
2010	1002	LP2 10	    19-Mar-10
2010	1001	LP1 10	    20-Jan-10
2009	904	LP4 09	    15-Sep-09

ID	STATION	Most_recent	2nd_Most_recent
100000	3	LP2 10	            NULL
100001	3	LP2 12	            LP1 12
100039	2	LP3 10	            LP2 10

Any help appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-16 : 05:35:54

how about 100039 ? there are 2 recs for STATION 1 and 2. Why is 2 selected ?

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

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-16 : 08:37:43

+1....i have the same question

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-16 : 19:38:49

quote:
Originally posted by khtan

how about 100039 ? there are 2 recs for STATION 1 and 2. Why is 2 selected ?

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

Hey khtan, thanks for trying to help.
You are correct,
I just picked any (like just highest value 2 is bigger then 1(not as in occurance))

So could either pick any station (since there is a draw) and then the most_recent and 2nd_most_recent

ID STATION Most_recent 2nd_Most_recent
100000 3 LP2 10 NULL
100001 3 LP2 12 LP1 12
100039 2 LP3 10 LP2 10

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-16 : 21:52:48

[code]
select ID, STATION, [1], [2]
from
(
select t1.ID, t1.STATION, t2.[DESCRIPTION], cn = row_number() over (partition by t1.ID, t1.STATION order by t2.[DATE] desc)
from
(
SELECT distinct d.ID, d.STATION, t1.TUB
from
(
select t1.ID, t1.STATION,
rn = row_number() over ( partition by t1.ID order by count(*) desc)
from table1 t1
group by t1.ID, t1.STATION
) as d
inner join table1 t1 on d.ID = t1.ID and d.STATION = t1.STATION
where rn = 1
) t1
inner join table2 t2 on t1.TUB = t2.TUB
) d
pivot
(
max([DESCRIPTION])
for cn in ([1] , [2] )
) p
[/code]

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

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-17 : 00:21:49

quote:

Originally posted by khtan


select	ID, STATION, [1], [2]
from
(
	select	t1.ID, t1.STATION, t2.[DESCRIPTION], cn = row_number() over (partition by t1.ID, t1.STATION order by t2.[DATE] desc)
	from	
	(
		SELECT	distinct d.ID, d.STATION, t1.TUB
		from
		(
			select	t1.ID, t1.STATION,
				rn = row_number() over ( partition by t1.ID order by count(*) desc)
			from	table1 t1
			group by t1.ID, t1.STATION
		) as d 
		inner join table1 t1	on	d.ID	= t1.ID	and	d.STATION	= t1.STATION
		where	rn	= 1
	) t1
	inner join table2 t2	on	t1.TUB	= t2.TUB
) d
pivot
(
	max([DESCRIPTION])
	for cn in ([1] , [2] )
) p

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

I am getting
Error: ORA-00923: FROM keyword not found where expected

On Line:

select	t1.ID, t1.STATION, t2.[DESCRIPTION], cn = row_number() over (partition by t1.ID, t1.STATION order by t2.[DATE] desc)

Cant figure it out.

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-17 : 00:28:38

Possibly make it easier to test:

I am trying to retrieve the most used channel (from table 1). If there is a tie, I want to choose the most recent SHORT_DESCR (from table 2) and use the other for second most popular.
(most used meaning most occuring/repeating)

Table 1 and Table 2 are joined by TERM

TABLE 1

CREATE TABLE DAN_T
(ID     VARCHAR2(4),
TERM    CHAR(8),
CHANNEL VARCHAR2(8)) 


INSERT INTO DAN_T (ID, TERM,CHANNEL) VALUES (1, 1201, 1);
INSERT INTO DAN_T (ID, TERM,CHANNEL) VALUES (1, 1202, 1);
INSERT INTO DAN_T (ID, TERM,CHANNEL) VALUES (1, 1202, 3);
INSERT INTO DAN_T (ID, TERM,CHANNEL) VALUES (2, 1203, 3);
INSERT INTO DAN_T (ID, TERM,CHANNEL) VALUES (2, 1202, 3);
INSERT INTO DAN_T (ID, TERM,CHANNEL) VALUES (2, 1101, 3);
INSERT INTO DAN_T (ID, TERM,CHANNEL) VALUES (3, 1001, 5);
INSERT INTO DAN_T (ID, TERM,CHANNEL) VALUES (3, 1201, 6);
INSERT INTO DAN_T (ID, TERM,CHANNEL) VALUES (4, 1201,2);
INSERT INTO DAN_T (ID, TERM,CHANNEL) VALUES (4, 1202,2);
INSERT INTO DAN_T (ID, TERM,CHANNEL) VALUES (4, 1203,1);
INSERT INTO DAN_T (ID, TERM,CHANNEL) VALUES (4, 1204,1);

TABLE 2


CREATE TABLE DAN_T2
(TERM    VARCHAR2(4),
SHORT_DESCR    CHAR(8),
START_DTE DATE) 


insert into DAN_T2 ( TERM, SHORT_DESCR, START_DTE)
                  values 
                  ( '1001','UP 10', TO_DATE( '13-DEC-2010', 'DD-MON-YYYY' ) );

insert into DAN_T2 ( TERM, SHORT_DESCR, START_DTE)
                  values 
                  ( '1101','UP1 11', TO_DATE( '13-JUN-2011', 'DD-MON-YYYY' ) );

insert into DAN_T2 ( TERM, SHORT_DESCR, START_DTE)
                  values 
                  ( '1201','UP1 12', TO_DATE( '13-MAR-2012', 'DD-MON-YYYY' ) );

insert into DAN_T2 ( TERM, SHORT_DESCR, START_DTE)
                  values 
                  ( '1202','UP2 12', TO_DATE( '13-JUN-2012', 'DD-MON-YYYY' ) );

insert into DAN_T2 ( TERM, SHORT_DESCR, START_DTE)
                  values 
                  ( '1203','UP3 12', TO_DATE( '13-SEP-2012', 'DD-MON-YYYY' ) );

insert into DAN_T2 ( TERM, SHORT_DESCR, START_DTE)
                  values 
                  ( '1204','UP4 12', TO_DATE( '13-DEC-2012', 'DD-MON-YYYY' ) );

WANT:

CREATE TABLE DAN_T3
(ID    VARCHAR2(4),
CHANNEL    VARCHAR2(8),
MOST_RECENT CHAR(8),
MOST_RECENT2 CHAR(8)) 

INSERT INTO DAN_T3 (ID, CHANNEL,MOST_RECENT,MOST_RECENT2) VALUES (1,1,NULL,NULL);

INSERT INTO DAN_T3 (ID, CHANNEL,MOST_RECENT,MOST_RECENT2) VALUES (2,3,NULL,NULL);

INSERT INTO DAN_T3 (ID, CHANNEL,MOST_RECENT,MOST_RECENT2) VALUES (3,'DRAW','UP1 12','UP 10');

INSERT INTO DAN_T3 (ID, CHANNEL,MOST_RECENT,MOST_RECENT2) VALUES (4,'DRAW','UP4 12','UP3 12');

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-17 : 00:29:35

me too.

That's why i suggested you to post your question in an Oracle forum. We can only provide SQL Server solution here.

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

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-17 : 00:35:34

quote:
Originally posted by khtan

me too.

That's why i suggested you to post your question in an Oracle forum. We can only provide SQL Server solution here.

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

Lol
I am using both.
Am asking the same question in an oracle forum.
Thought i would double my chances by asking here to.
Plus a helpful guy like you is hard to find elsewhere.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-17 : 01:10:57

Does oracle also have the PIVOT operator ?

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

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-17 : 02:28:16

Nope.
You would have to use case.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-17 : 02:35:51

try


select	ID, STATION, 
	[1]	= max(case when cn = 1 then [DESCRIPTION] end),
	[2]	= max(case when cn = 2 then [DESCRIPTION] end)
from
(
	select	t1.ID, t1.STATION, t2.[DESCRIPTION], cn = row_number() over (partition by t1.ID, t1.STATION order by t2.[DATE] desc)
	from	
	(
		SELECT	distinct d.ID, d.STATION, t1.TUB
		from
		(
			select	t1.ID, t1.STATION,
				rn = row_number() over ( partition by t1.ID order by count(*) desc)
			from	table1 t1
			group by t1.ID, t1.STATION
		) as d 
		inner join table1 t1	on	d.ID	= t1.ID	and	d.STATION	= t1.STATION
		where	rn	= 1
	) t1
	inner join table2 t2	on	t1.TUB	= t2.TUB
) d
group by ID, STATION

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

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-17 : 02:50:18

Nope I think it does not like your

	[1]	= max(case when cn = 1 then [DESCRIPTION] end),
	[2]	= max(case when cn = 2 then [DESCRIPTION] end)

and

cn = row_number() over (partition by t1.ID, t1.STATION order by t2.[DATE] desc)

Same Error
Error: ORA-00923: FROM keyword not found where expected

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-17 : 04:15:31

Post your question at www.orafaq.com. The alias syntax will differ there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources