return one row with many values same column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

return one row with many values same column

Author

Topic

dmaxj
Posting Yak Master

174 Posts

Posted - 2011-08-31 : 13:20:57

Hello -

My table data example


Code        Description
--------------------------
1111        Television42
1111        Television44
1111        Television60
2222        Radio100
2222        Radio200
3333        Stove99
3333        Stove45

How can get the following output -


Code        Description
--------------------------
1111        Television42
2222        Radio100
3333        Stove99

Regards

Kristen
Test

22859 Posts

Posted - 2011-08-31 : 13:23:23

You can't.

Either you will have some sort of additional Sequence column (date created, IDENTITY, or something else), that you can use to create an ordering effect.

Or you will have to use MIN(Description) to get the lowest value.

Or you can just take one description, at random, for each distinct Code value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 13:24:32

[code]select Code,description
from
(
select Code,Description,ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Description) AS Seq
FROM table
)t
where Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-08-31 : 13:34:33

"PARTITION BY ... ORDER BY Description"

Unfortunately that will give you Stove45 for Code3333, rather than Stove99 as per the requirement (which is the physically first row, rather than the order-ably first row).

@dmaxj : RDBMS Database has no concept of "the order the records were created" - unless you have a column with CreateDate or ID number or somesuch to ORDER BY

dmaxj
Posting Yak Master

174 Posts

Posted - 2011-08-31 : 14:30:27

I am just looking for any Description with a single Code, so I think that this code will suffice -

Thanks to you all!

Kristen
Test

22859 Posts

Posted - 2011-08-31 : 16:56:10

"I am just looking for any Description with a single Code"

In that case I think:


SELECT Code,
       [Description] = MIN(Description)
FROM MyTable
GROUP BY Code
ORDER BY Code

will be more efficient

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources