Selecting row data as column in resultset 1

[snitin78]

Hi Guys,

Due to time constraints I did very minimal search on the forums here, so I am sorry if this has been answered previously..I am just throwing it out there!

Here is what I need help with:

I have a users table and then a join table between users and some attributes (lets call this table user_attributes). Now I would like to select data from the users table with all columns from the users table along with the first attribute associated to the user as an additional column in the result.

Basically, if users X & Y have the following attributes:

User Attribute
X A
X B
X C
Y A1
Y B1
Y C1

then when I do select * from users where user= X what I would like to get is:

user_name user_something ..... attribute
X some abc ..... A
Y some abc ..... A1

That chosen attribute has to be the top 1 of the attributes associated with the user. What should I add in the select statement to get this result?

Any help on how to achieve this is greatly appreciated. Thanks in advance.

 

[snitin78]

Just for information, the environment is MS SQL Server 2008 in compatibility mode.

 

[snitin78]

Argh!!! This time crunch is taking the toll on me...

Please read my select statement as

select * from users where user in (X, Y)

 

[markros]

select * from (Select U.*, row_number() over (order by Attribute) rn from Users U) Ordered

See also

http://social.msdn.microsoft.com/Fo...l/thread/578bad2b-62d0-487e-9d79-4f25c6004377

 

[markros]

Oops, I meant

select * from (Select U.*, row_number() over (order by Attribute) as rn from Users U) Ordered where rn = 1

 

[snitin78]

HI Markos,

Thanks for the reply. The link looks promising .

For your solution, I did not understand the over "(order by Attribute)" part. User and Attribute are 2 separate tables...can I still do what you have proposed.

 

[markros]

Yes, you can still do the same, just join both tables as you do normally.

E.g.

select * from (select U.*, A.*, row_number() over (Partition by UserID order by Attributes) as rn from Users U inner join Attributes A on U.UserID = A.UserID) D where D.rn = 1

 

[RyanEK]

Hi,

How about...

select u.*,
(select top 1 att from [user_attribute] a where a.id = u.id) as attribute
from [user] u

Ryan

 

[snitin78]

Cool! I achieved what I needed using CTEs and row_number as mentioned by Markos.

Thanks a ton for your responses guys.