April 29, 2004 at 4:26 pm
Hi all, here's a challenging problem. Say, I have a 2-column table: the first column is the user ID, and the second column is some text data.
In this table, for example, I have a thousand records - 100 text data values for each of 10 user ID values.
Without using temp tables or cursors, how do I select 10 records for each userID value?
(The number if userID values can change, i may, the next day, have 6 userID values, with 123 text values each).
I'm basically trying to summarize the data for each userID.
Any ideas?
Thanks a ton,
Sara
April 30, 2004 at 12:31 am
In which form do you want to summarize the text values ? Comma-separated, for example ? Give some more information about the form of the result set you want to obtain.
Regards,
Goce Smilevski.
April 30, 2004 at 2:40 am
does this help ? Assumed that for a userid , there will be distinct values for textdata.
create table testdata
( userid int , textdata varchar(10) )
go
insert testdata
select id , name + 'a' from sysobjects
go
insert testdata
select id , name + 'b' from sysobjects
go
insert testdata
select id , name + 'c' from sysobjects
go
insert testdata
select id , name + 'd' from sysobjects
go
insert testdata
select id , name + 'e' from sysobjects
go
insert testdata
select id , name + 'f' from sysobjects
go
insert testdata
select id , name + 'g' from sysobjects
go
insert testdata
select id , name + 'h' from sysobjects
go
insert testdata
select id , name + 'i' from sysobjects
go
insert testdata
select id , name + 'j' from sysobjects
go
insert testdata
select id , name + 'k' from sysobjects
go
insert testdata
select id , name + 'l' from sysobjects
go
select * from testdata b
where b.textdata in
(
select top 10 a.textdata
from testdata a
where a.userid = b.userid
order by a.textdata
)
order by userid , b.textdata
April 30, 2004 at 6:23 am
Amit,
For future reference this is called a CORRELATED SUBQUERY. I find them to be a rather abstract thought process at first, but the only way that I know of to complete this request without using ... a cursor. If I am wrong, somebody please let me know because I need to do these regularly.
April 30, 2004 at 6:29 am
I am trying to answer a quesion "Without using temp tables or cursors, how do I select 10 records for each userID value?"
I do not want to answer a new person joined in this dorum by saying "go and use CORRELATED SUBQUERY" .
thats why gave him the test data used by me as well.
I am very well aware of the TSQL terms like this.
April 30, 2004 at 7:02 am
Hi Sara,
I guess it would be easier to help if you explain a bit more closely what do you want to achieve... What does the "Without using temp tables or cursors, how do I select 10 records for each userID value?" precisely mean? Do you want to select 10 records for each userID, or should we understand it as "10 records, i.e. 1 record for each userID"? If it is the first, will this number change, too?
As I understood the question, you want to have as many records in the resultset as there are userIDs, and for each userID you want to see all text values in that one row... is that right?
Vladan
April 30, 2004 at 7:50 am
Amit,
I apologize for insulting your intelligence and questioning your knowledge of sql terms. However, based on you test data and the original question that you asked, it appears that you already answered your own question. I was just offering the terminology because I thought maybe you were looking for how to research the subject. How I read your question was how do I get N records for each ID (where actual recordcount is N+, which the select query you offered after the insert statements in your test data does. You then said, I'm basically trying to summarize the data for each user, what do you mean by that?
Tim
April 30, 2004 at 8:32 am
Amit, your solution is beautiful, thank you so much for the education. I knew there had to be a way to do it using subqueries, but didn't know where to go from there.
I'll provide some more detail: I am working on a program that uploads car descriptions from flat files into sql server.
If there is something wrong an uploaded record, it gets inserted into the 'bad vins' table. Then, an email is generated that says, basically, these bad rows were uploaded:
156 bad rows were found for Group 1
rowdata here
rowdata here
rowdata here
.. and so on
9 bad rows were found for Group 2
rowdata here
rowdata here
rowdata here
... and so on.
The problem was that sometimes, very large numbers of bad rows are found, and the email gets to be pretty honkin' huge.
This solution allows me to generate an email that reads:
156 bad rows were found for group 1. Here are the first ten.
rowdata
rowdata
rowdata.
Thanks again!
Sara
April 30, 2004 at 8:39 am
Thanks for the explanation, Sara - now I can see that I understood the question wrong and that Amit's method should give correct results.
And now it is time to have some fun... see you all next week again!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply