SELECT DISTINCT and more from a table

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 2005 Forums

Transact-SQL (2005)

SELECT DISTINCT and more from a table

Author

Topic

smcallister
Starting Member

15 Posts

Posted - 2009-05-29 : 12:18:23

I have a table that has the following columns:

ID (key)
tag
value (distinct)

I want to be able to select no more than 1 from each type of tag. Should I go through and SELECT DISTINCT tag and then loop through each tag or is there a faster way.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-29 : 12:29:28

Sorry it is not clear to me.
Can you give sample data and wanted output?

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-05-29 : 12:49:39

TOP 1 Tag, Max tag, Min tag...does it matter?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

smcallister
Starting Member

15 Posts

Posted - 2009-05-29 : 13:26:56

For example

ID: 1
tag: red
value: balloon

ID: 2
tag: blue
value: water

ID: 3
tag: red
value: bird

From the above data, it should only pull TWO records.. one record with a BLUE tag and one record with a RED tag. If possible, I'd like it to select randomly which RED tag it chooses.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-29 : 13:56:12

select tag, max([value]) as [value]
from table
group by tag

No, you're never too old to Yak'n'Roll if you're too young to die.

smcallister
Starting Member

15 Posts

Posted - 2009-05-29 : 14:19:18

select tag, max([value]) as [value]
from table
group by tag

^---- this does not work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-29 : 14:36:24

why is it not working? atleast show by means of sample data what you're looking for and why you think the provided suggestion is not working?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-29 : 15:21:10

[code]DECLARE @Table TABLE (ID INT, Tag VARCHAR(50), Value VARCHAR(50))

INSERT @Table
SELECT 1, 'red', 'ballon'
UNION ALL SELECT 2, 'blue', 'water'
UNION ALL SELECT 3, 'red', 'bird'

SELECT
*
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Tag ORDER BY NEWID()) AS RowNum
FROM
@Table
) AS T
WHERE
RowNum = 1[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources