•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 423,848 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,828 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 707 | Replies: 6
 |
•
•
Join Date: Dec 2004
Location: Hiawassee, Georgia
Posts: 129
Reputation: 
Rep Power: 4
Solved Threads: 1
Junior Poster
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
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
If in doubt, reach into the trash can and remove the user guide.
•
•
Join Date: Jan 2008
Posts: 35
Reputation:
Rep Power: 1
Solved Threads: 5
Light Poster
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:
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
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
•
•
Join Date: Dec 2004
Location: Hiawassee, Georgia
Posts: 129
Reputation:
Rep Power: 4
Solved Threads: 1
Junior Poster
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.
If in doubt, reach into the trash can and remove the user guide.
•
•
Join Date: Jan 2008
Posts: 35
Reputation:
Rep Power: 1
Solved Threads: 5
Light Poster
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:
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
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
•
•
Join Date: Dec 2004
Location: Hiawassee, Georgia
Posts: 129
Reputation:
Rep Power: 4
Solved Threads: 1
Junior Poster
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
Thanks mellamokb
If in doubt, reach into the trash can and remove the user guide.
|
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
•
•
•
•
ajax asp data database decimal seperator thousand seperator when using sql server deleting records from ms sql table where columns have duplicate values dell developer development drive flash hacker hard hard drive hitachi laptop management studio 2005 microsoft msdn news office sandisk security software sql sql cache dependency with polling-based invalidation storage survey terabyte vista
- Basic SQL made Hard (MS SQL)
- Query Tool (MS SQL)
- conditional database query (ColdFusion)
- Cheapish Graphics Card Query (Monitors, Displays and Video Cards)
- MySql multiple table query problem.... (MySQL)
- Javascript array from sql query (JSP)
- Pretty urgent but not too hard (i think)... (ASP)
Other Threads in the MS SQL Forum
- Previous Thread: TOP seven data and grouping
- Next Thread: sql amature
