Hard to Query

Hey everyone,

This problem I've been working with has got me stumped. Let me show you the table layout first.

ID_NUM int,
SEQ_NUM int,
ATTRIBUTE_CODE char(2),
ATTRIBUTE_DEF char(50),
JOB_TIME datetime

This table contains a list of activities individuals like and there is no way of knowing which one they like the most. Also one individual can have 3 records while another can have 50. My goal is to pull out the first three entered into the table and place those into three different columns for a data mining report.

Report Layout: leaving out non-relavent columns

ID_NUM int,
...
ATTRIBUTE_CODE1 char(2),
ATTRIBUTE_CODE2 char(2),
ATTRIBUTE_CODE3 char(2),
...

I first thought a pivot table would work since I wanted to turn the rows into columns, but I'm not aggregating anything. If anyone has any ideas I'm all ears.

Thanks,
Daniel

[mellamokb]

Hi Daniel,

Here is the best I could come up with. I think it does what you wanted, but it's made up of a lot of subqueries, so it wouldn't be very efficient. Some improvements might include using temporary tables or views, or using indices:

select
	ID_NUM,
	(select ATTRIBUTE_CODE from myTable m1 where m1.SEQ_NUM = SEQ_NUM1 and m1.ID_NUM = m2.ID_NUM) as ATTRIBUTE_CODE1,
	(select ATTRIBUTE_CODE from myTable m1 where m1.SEQ_NUM = SEQ_NUM2 and m1.ID_NUM = m2.ID_NUM) as ATTRIBUTE_CODE2,
	(select ATTRIBUTE_CODE from myTable m1 where m1.SEQ_NUM = SEQ_NUM3 and m1.ID_NUM = m2.ID_NUM) as ATTRIBUTE_CODE3
from
	(select
		ID_NUM,
		(select MAX(SEQ_NUM) from (select top 1 SEQ_NUM from myTable t2 WHERE t2.ID_NUM = t1.ID_NUM order by SEQ_NUM) t1) AS SEQ_NUM1,
		(select MAX(SEQ_NUM) from (select top 2 SEQ_NUM from myTable t2 WHERE t2.ID_NUM = t1.ID_NUM order by SEQ_NUM) t1) AS SEQ_NUM2,
		(select MAX(SEQ_NUM) from (select top 3 SEQ_NUM from myTable t2 WHERE t2.ID_NUM = t1.ID_NUM order by SEQ_NUM) t1) AS SEQ_NUM3
	from
		(select distinct ID_NUM from myTable) t1) m2

Basically, the main query selects from a subquery that determines the first three SEQ_NUM per ID_NUM, and then the main query retrieves the ATTRIBUTE_CODE that corresponds to each of those three SEQ_NUM and ID_NUM combinations.

~ mellamokb

Thanks mellamokb for the reply and the code. I ran what you created and there is only one error that shows up. It doesn't like the t1.ID_NUM on the where statement t2.ID_NUM = t1.ID_NUM. It tells me that is an invalid column. I'm guessing that subquery isn't getting the alias name t1 assigned to it before it reaches the where clause.

[mellamokb]

Actually, the query was a little confusing, because those t1's at the end of the inner subqueries are actually irrelevant. The t1 comes from the name of the subquery at the very end. Here is the same query, with those t1's renamed in case those are the cause of the problem:

select
	ID_NUM,
	(select ATTRIBUTE_CODE from myTable m1 where m1.SEQ_NUM = SEQ_NUM1 and m1.ID_NUM = m2.ID_NUM) as ATTRIBUTE_CODE1,
	(select ATTRIBUTE_CODE from myTable m1 where m1.SEQ_NUM = SEQ_NUM2 and m1.ID_NUM = m2.ID_NUM) as ATTRIBUTE_CODE2,
	(select ATTRIBUTE_CODE from myTable m1 where m1.SEQ_NUM = SEQ_NUM3 and m1.ID_NUM = m2.ID_NUM) as ATTRIBUTE_CODE3
from
	(select
		ID_NUM,
		(select MAX(SEQ_NUM) from (select top 1 SEQ_NUM from myTable t2 WHERE t2.ID_NUM = t1.ID_NUM order by SEQ_NUM) s1) AS SEQ_NUM1,
		(select MAX(SEQ_NUM) from (select top 2 SEQ_NUM from myTable t2 WHERE t2.ID_NUM = t1.ID_NUM order by SEQ_NUM) s2) AS SEQ_NUM2,
		(select MAX(SEQ_NUM) from (select top 3 SEQ_NUM from myTable t2 WHERE t2.ID_NUM = t1.ID_NUM order by SEQ_NUM) s3) AS SEQ_NUM3
	from
		(select distinct ID_NUM from myTable) t1) m2

What version of MS SQL are you running? I tested this code on MS SQL 2005 and it works perfectly, so if you are using an older version, say MS SQL 2000, there may be a subtle difference between the two that is causing the problem you mentioned.

~ mellamokb

I emailed the list server for our vendor and I was informed that the table I'm working with used to not start at 1 for every individual's sequence number. So they updated it a while back. I was using the older ID numbers prior to the update for my testing and that's what was throwing me off. Sorry for making you go to the trouble of finding a workaround for their mistake.

Thanks mellamokb

Just to add on what version I'm running is that it is SQL 2000.

[mellamokb]

No problem.