Simple SQL? - SQL Server Forums

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

SQL Server Development (2000)

Simple SQL?

Author

Topic

Balmark
Starting Member

3 Posts

Posted - 2004-08-27 : 00:55:47

Hello ..

um I'm kindoff in a bind and need a bit of help

I'm trying to get write sql to return rows in a table, 1 row per unique column value in col1

ie.


Col1  |  Col2  |  Col3  | Col4
-------------------------------
10001	OWNER	45666	qwerty
10001	SHARE	58456	asdfgh
10021	OWNER	106780	zxcvbn
10025	OWNER	23471	uioppp
10027	OWNER	109678	hjklll
10028	OWNER	108432	vbnmmm
10090	OWNER	100456	dfhghh
10090	OWNER	107342	fghjhr
10090	SHARE	456734	ertyyy

I need to return rows where I get 1 row for each unique value in col1
(for duplicates in col1, it doesnt matter which one I return as long as I get a full row)


10001	OWNER	45666	qwerty
10021	OWNER	106780	zxcvbn
10025	OWNER	23471	uioppp
10027	OWNER	109678	hjklll
10028	OWNER	108432	vbnmmm
10090	OWNER	100456	dfhghh

Group By doesnt help because I need at least 1 value for the other columns..

help please?

/Balmark

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-27 : 01:01:29

There's probably a better way to do this, but it's midnight here.


DECLARE @MessyMessy TABLE(
	messy1 INT,
	messy2 VARCHAR(55),
	messy3 INT,
	messy4 VARCHAR(55))

DECLARE @MessyMessier TABLE(
	ident INT IDENTITY(1,1) PRIMARY KEY,
	messy1 INT,
	messy2 VARCHAR(55),
	messy3 INT,
	messy4 VARCHAR(55))

INSERT @MessyMessy(messy1,messy2,messy3,messy4)
	SELECT 10001,'OWNER',45666,'qwerty' UNION ALL
	SELECT 10001,'SHARE',58456,'asdfgh' UNION ALL
	SELECT 10021,'OWNER',106780,'zxcvbn' UNION ALL
	SELECT 10025,'OWNER',23471,'uioppp' UNION ALL
	SELECT 10027,'OWNER',109678,'hjklll' UNION ALL
	SELECT 10028,'OWNER',108432,'vbnmmm' UNION ALL
	SELECT 10090,'OWNER',100456,'dfhghh' UNION ALL
	SELECT 10090,'OWNER',07342,'fghjhr' UNION ALL
	SELECT 10090,'SHARE',456734,'ertyyy'

INSERT @MessyMessier(messy1,messy2,messy3,messy4)
	SELECT messy1,messy2,messy3,messy4 FROM @MessyMessy

SELECT
	mm1.messy1,
	mm1.messy2,
	mm1.messy3,	
	mm1.messy4
FROM 
	@MessyMessier mm1
	INNER JOIN (
		SELECT MAX(ident) AS ident, messy1
		FROM @MessyMessier
		GROUP BY messy1) mm2 ON mm1.ident = mm2.ident

messy1      messy2                                                  messy3      messy4                                                  
----------- ------------------------------------------------------- ----------- ------------------------------------------------------- 
10001       SHARE                                                   58456       asdfgh
10021       OWNER                                                   106780      zxcvbn
10025       OWNER                                                   23471       uioppp
10027       OWNER                                                   109678      hjklll
10028       OWNER                                                   108432      vbnmmm
10090       SHARE                                                   456734      ertyyy

(6 row(s) affected)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

01010111011010000111100100100000011000010111001001 10010100100000011101000110100001100101011100110110 01010010000001110000011001010110111101110000011011 00011001010010000001110011011011110010000001110011 011101000111010101110000011010010110010000111111

Balmark
Starting Member

3 Posts

Posted - 2004-08-29 : 19:22:55

Thank you v much Derrick =)

I'd never thought to dump it into an indexed file!

/Balmark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources