Member Avatar for Member #25180

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

  • 2 Contributors
  • 6 Replies
  • 103 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by mellamokb

Dani AI

Generated

Quick alternative that works in SQL Server 2000 (no ROW_NUMBER): compute a per-ID rank using a correlated COUNT of earlier rows, then pivot those ranks into columns with aggregate + CASE. This avoids deep nested TOP/MAX subqueries and gives a deterministic way to break ties (use JOB_TIME and SEQ_NUM as a tie breaker).

Below is a compact pattern to generate the first three attributes per ID. It assigns a rank per row then collapses ranks 1..3 into three columns.

SELECT
  t.ID_NUM,
  MAX(CASE WHEN t.rnk = 1 THEN t.ATTRIBUTE_CODE END) AS ATTRIBUTE_CODE1,
  MAX(CASE WHEN t.rnk = 2 THEN t.ATTRIBUTE_CODE END) AS ATTRIBUTE_CODE2,
  MAX(CASE WHEN t.rnk = 3 THEN t.ATTRIBUTE_CODE END) AS ATTRIBUTE_CODE3
FROM
  (
    SELECT
      a.ID_NUM,
      a.ATTRIBUTE_CODE,
      1 + (
        SELECT COUNT(*)
        FROM myTable b
        WHERE b.ID_NUM = a.ID_NUM
          AND (b.JOB_TIME < a.JOB_TIME
               OR (b.JOB_TIME = a.JOB_TIME AND b.SEQ_NUM < a.SEQ_NUM))
      ) AS rnk
    FROM myTable a
  ) t
WHERE t.rnk <= 3
GROUP BY t.ID_NUM;

Notes and cautions:

  • Use an explicit tie-breaker (SEQ_NUM or a unique key) so ordering is deterministic when JOB_TIME values repeat.
  • Performance: the correlated COUNT is O(N^2) in the worst case. Add an index on (ID_NUM, JOB_TIME, SEQ_NUM) and restrict the input set (WHERE clauses) when possible. For very large tables, populate a filtered temp table first and then rank that.
  • If SEQ_NUM is guaranteed to start at 1 per ID and reflects insertion order, you can simplify by pivoting on SEQ_NUM directly. Given the vendor update mentioned earlier, verify SEQ_NUM consistency before relying on it.
  • If upgrading to SQL Server 2005+, replace the COUNT trick with ROW_NUMBER() for much better performance and simpler code (see Microsoft docs for ROW_NUMBER).

Recommended Answers

All 6 Replies

Member Avatar for 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

Member Avatar for Member #25180

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.

Member Avatar for 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

Member Avatar for Member #25180

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

Member Avatar for Member #25180

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

Member Avatar for mellamokb

No problem.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.